Duplicates in output and sort not saving

[Dropout]

Hi All.

Thanks to Villaroy I was able to set up a couple of tables and write a query.

I have 2 issues:

For some reason when I set a sort it is not saving. It will work once but when I reopen the query to re-edit it, the sort is gone.

Also, for some reason, the data is listed twice, once with the due date and once without. This didn't seem to happen before I added the Due data.

Base attached.

[UnklDonald418]

The table "Inflow" has multiple entries for many of the part numbers, one with a date and another without a date.
Neither of your tables has a Primary Key field. A table without a Primary Key is read only.
Also, saving sorted queries requires saving an index but without a Primary Key that can't be done.

To remedy that, rename "Inflow" to "Inflow"Old.
Now you can use cut and paste to open the Copy Table dialog.
Change the name to "Inflow",
select Definition and data
check the Create primary key option
select Create to create an editable database table.
Right click on the newly created "Inflow" table to open the table design GUI.
Select the ID field and change Auto Value to Yes.
Save exit the table design GUI.
Now you should be able to save sorted fields in your queries of "Inflow" and edit the table data.

It would be a good idea to do the same with "Krug Audit List"

Any time you create a database table and you get a popup dialog asking if you want to create a Primary Key, it is almost always best to answer Yes.

Edit: A little further testing revealed that in fact the sorting IS being saved, even when using tables without a Primary Key. Create a sorted query and save and exit it. Right click on the query name and select Edit in the SQL View. The ORDER BY clause has been saved and the output is properly sorted. Now right click on the query and select Edit to open the query in the query design GUI. The sorting selections are gone, and switching to the SQL view the ORDER BY clause have been removed. I guess I've never noticed that behavior, probably because I usually edit my queries in the SQL view.

[UnklDonald418]

Once you have tables that can be edited you can remove the duplicates
select Tables in the main database window.
Select Tools>SQL and execute

Code: Select all

DELETE FROM "Inflow" WHERE "Inflow"."Due" IS NULL

Since those commands bypass the Base front end, you need to select View>Refresh Tables to tell Base about the changes.

[Dropout]

Thanks.

I must be doing something wrong.

I get the follow error when running the SQL command:

1: Table not found in statement [DELETE FROM "Inflow"]

What am I missing?

[UnklDonald418]

The command worked fine for me using the Test5.odb database you uploaded.
The error message is explaining that the database engine cannot find a table named "Inflow"..
In the Tables area of your database make sure you have a table named "Inflow".
Perhaps there is a space either before or after the table name in your database.

[Dropout]

Thanks for the reply.

I do have a table named Inflow - checked for spaces and uppercase/lowercase etc., still no luck.

I did find that filters will give me the result I wanted.

Now how to export the data into something useful...