Is my duplicate query duplicating results?

[rebfein]

I created a duplicate query to check the entries of several tables that have from 1800 entries (the two smallest sized tables) to 7800 entries (the three largest sized tables). All the columns in most all the tables are defined as text, except for date columns (in 3 tables) and hyperlink columns (in 1 table).



The query is to find duplicate entries by ECO number and model number (2 columns) in one particular table. There are a total of 10 columns in the table.

I automatically delete query information entries (not the record) if the information is the same, but the Access IDs are different. For example, if one entry for XXXX has an ID of1042 and another entry for XXXX with an ID of 7201 and all the other information in the other columns is the same, I delete the information and use the record for another entry.

I'm very careful with the data I'm entering for the tables. I enter the information on an Excel spreadsheet; then I organize it by model number using Excel's Data Sort function. Then copy & paste the entries into the tables.

However I've found some entries that have somewhat consecutive ID numbers:


Any idea of what is going on and how I can fix it?

Thank you

[June7]

I don't see how a query to find duplicates can save data to a table. Provide sample data as well as the query statements.

How do you 'automatically delete'?

Why are you using Excel?

Is data in that fourth field supposed to be unique?

[John_G]

That's not much to go on. Can you post the structure of your table(s), and the SQL of the query? What is the Access ID (first column in your illustration) = is it an autonumber?

From your description, I doubt the problem is the query, because the ID's are different; it is more likely that duplicate information is being entered somewhere

I delete the information and use the record for another entry.

If the ID's are being used in any relationships or to link tables in queries, I don't think that is a good approach; you would have to be very careful about what happens to data in other tables that are related using the ID.

[rebfein]

John & June7,

I'm answering both your comments below:

FYI, none of the tables are what you may call finished. I only have 5 queries set up, 4 for checking for duplicates and an appendage-to-table query for voided items.

John,
Can you post the structure of your table(s)...
Here you go...



And the SQL of the query?
Please see below, I copied it to June7's comment.

What is the Access ID (first column in your illustration) = is it an autonumber?
Yes, the Access ID is an autonumber.

From your description, I doubt the problem is the query, because the ID's are different; it is more likely that duplicate information is being entered somewhere
I doubt it, since, despite numerous requests for help, I seem to be the only one entering in the data.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
June7,

I don't see how a query to find duplicates can save data to a table. Provide sample data as well as the query statements.
Sorry, can't provide any more data than that what you see listed in the table.

Here's the SQL script for a duplicates search in the diagrams table.

SELECT [Diagrams Affected].ECO, [Diagrams Affected].Model, [Diagrams Affected].Parts, [Diagrams Affected].Misc
FROM [Diagrams Affected]
WHERE ((([Diagrams Affected].ECO) In (SELECT [ECO] FROM [Diagrams Affected] As Tmp GROUP BY [ECO] HAVING Count(*)>1 )))
ORDER BY [Diagrams Affected].ECO;

How do you 'automatically delete'
Actually, its manual highlight and click the delete key

Why are you using Excel?
From above: "I'm very careful with the data I'm entering for the tables. I enter the information on an Excel spreadsheet; then I organize it by model number using Excel's Data Sort function. Then copy & paste the entries into the Access tables." Its easier to do it this way when I have lots of information for the same item used in different units.

Is data in that fourth field supposed to be unique?
No

[June7]

I still don't understand why Excel is involved and not just entering and manipulating data within Access. You say Excel makes process easier but I am not convinced.

You organize data in Excel then copy/paste rows to Access table? As I said, need raw data as in the unorganized spreadsheet.

The dataset you posted is from the table, not query result?

If you want to provide files, follow instructions at bottom of my post.

[John_G]

Going back to the screenshot in your orginal post - is that from a query or from a table?
How many fields are in that diagram? To me it looks like 6, but if it is fewer than that, what are they? What does data in the "ECO" field look like?

What does the result of the query (SQL above) look like, and how do you interpret it?

Here is SQL for another variation of your query you could try:

SELECT [Diagrams Affected].ECO, [Diagrams Affected].Model, Count([Diagrams Affected].ECO)
FROM [Diagrams Affected]
GROUP BY [Diagrams Affected].ECO, [Diagrams Affected].Model
HAVING Count([Diagrams Affected].ECO)>1 )))
ORDER BY [Diagrams Affected].ECO;

Notice I left out Parts and Misc - you don't want them in there because they are not the issue.
When you run that, you will get a list of all ECO+Model combinations that occur more than once in [Diagrams Affected].

Since you are only selecting from one table, if you get any counts > 1, I guarantee that the duplication is in the table, and is not a problem with the query.