Writing query to find duplicates across tables

[vom]

Hi,

I am new to Microsoft Access and writing queries. I was wondering if anyone could offer some suggestions.



Employees submit information into a form which translates the information into a table. The table has been in use for years. By accident some employees were writing to an archived table while others were writing the the active table. This resulted in a field called "WorkID" being duplicated across the two tables.

Bottom line I am trying to write a query finding duplicates across tables but basing the duplicate only on certain columns.

Can anyone offer any assistance?

[June7]

Google: Access query find duplicates.
Does this help: http://www.ehow.com/how_13574_wizard-create-find.html

[orange]

How can some be writing to one table, and others be writing to another?
Seems there is/are some untested forms/queries being used in a production system.

This seems to be an issue with testing/maintenance and production activities, or a situation where multiple developers have independent access to database for the purpose of making changes.

Looks like some Standard Procedures are needed.

Finding the duplicates is a small piece of the puzzle. Understanding why duplicates or multiple forms are being used is another, preventing it in future is another and getting some "business practices in place" is yet another.

Good luck.

[vom]

How can some be writing to one table, and others be writing to another?
Seems there is/are some untested forms/queries being used in a production system.

This seems to be an issue with testing/maintenance and production activities, or a situation where multiple developers have independent access to database for the purpose of making changes.

Looks like some Standard Procedures are needed.

Finding the duplicates is a small piece of the puzzle. Understanding why duplicates or multiple forms are being used is another, preventing it in future is another and getting some "business practices in place" is yet another.

Good luck.

I am not responsible for creating web forms that link to the database. I was brought in temporarily to help with other objectives. This falls outside of my objective but I decided to help establish a solution because it is the right thing to do and wouldn't take that long. I agree there may be deficiencies, but not all situations have corporate level IT staffing for implementation the way they should be in a perfect world. I am not defending what I agree is an error, but saying that I understand why it occurred. My only concern in the matter in my current scope was helping to resolve the issue at hand.

I was able to assist in working through this issue in Access without writing any RAW SQL queries. Thank you for the replies.

[vom]

Google: Access query find duplicates.
Does this help: http://www.ehow.com/how_13574_wizard-create-find.html

Only relevant for data in a single table. There is no default query for comparing duplicate data across tables. You have to query the two tables together and then run the default duplicate query in Access from what I can see. Just FYI. But thank you for the link.

[June7]

Sorry, I should have looked more closely at that link.

What you want is the opposite of Find Unmatched. And yes that does require joining tables in query on common value, in your case the WorkID. An INNER JOIN will return only the records where WorkID is in both tables.