MS Access Query help

Corvus

Hi, I need a little help with an access query I'm trying to get working. I'm not a frequent Access user by any means.

What I have is a db with two tables, one with a list of names and ID numbers, and other with another list of names. What I want to do is run a query that will find the duplicate names between each table, and output that list with the ID #s from the first table.

I've tried using the "find duplicates" pre-made query, but it seems to be outputting duplicates from within the first table, which is not what I want. I only want names that are duplicates between both tables.

Halp?

DietarySupplement

Right idea, wrong query wizard. Use the "Find Unmatched Query Wizard." Make sure you select the table you want the results from in the first box, then your other table in the second. Find the field to match on, and viola.

Now, if you have records in table A that are not in table B, do you have records in table B that are not in table A? In other words, to find the true "delta" you might want to do it both ways.

uean

Corvus wrote: »

I've tried using the "find duplicates" pre-made query, but it seems to be outputting duplicates from within the first table, which is not what I want. I only want names that are duplicates between both tables.

Halp?

Easiest way:

Create new query.

Add table1 and table2

Join table1.name to table2.name

Return table1.name in the results. These are the names that are identical in the name field of table1 and table2. (You can also return table2.name, it doesn't matter as you are only going to be retrieving records with identical names)

.......... or, enter SQL view and just enter this (change to suit your table and field names obviously)

SELECT table1.ID, table1.name
FROM table1 INNER JOIN table2 ON table1.name = table2.name;

Presto This is based on my understanding that you want to know which names reside in both tables, and not which names are duplicates within each table. If you want to know which names are duplicates within each table, create a new query, add a table (ie table1), group the results and do a count on name, then return the count for everything over 1.

which would be

SELECT table1.ID, table1.name, Count(table1.name) AS countofname
FROM table1
GROUP BY table1.ID, table1.name
HAVING ((Count(Table1.name))>1);

Corvus

Thanks, I appreciate it, this was driving me nuts.

DietarySupplement

Sorry, I misread your post... but my solution is for when you don't have duplicates, but data in tables that might not be in the other. Still, a handy thing to know.