Finding duplicates

[mac_see]

I have 100 tabes in an Access database, every table has 1 filed with 100 names (records), no primary key assigned. I would like to find duplicates.

Here is the criteria:

The computer should pick up the first name of Table1 and check that name in that table (Table1) as well as the remaining 99 tables. Continue this till we reach the last name (record) of the 100th table.

Display the result in another table/query.

What is the best way to accomplish this task and how to do it.

Maxi

 

[Norie]

You could create append queries to add all the names to a new table.

And then create a find duplicates query.

Since there are so many tables this would probably be best to be done with VBA.

That is create code that creates and runs the append queries.

 

[mac_see]

I did that and it worked. But this was just an example that I gave you.

Actually, I have 61 tables and every every table has 352716 records. If I create a new table and put all 21515676 records, access would still create the table with such a lot of data.

But when I try to find duplicates, it gives an error half way "No space on temporary disk"

I have 3 partitions with more than 3 GB space available on every partition.
I have used the SET=C:\WINDOWS\TEMP in my autoexec.bat file.
I have played with the virutal memory settings as well.
Cleared all my temporary files and .tmp files.
Tried everything given in this URL http://support.microsoft.com/?kbid=161329
I have tried compacting but it does not work.


Any ideas???

 

[Norie]

Have you looked into creating UNION queries?

 

[mac_see]

Yes and unfortunately it gives the same error.

Herez what I tried. I got it from Foster from google groups.

SELECT [name]
FROM (
SELECT [name]
FROM table1

UNION ALL

SELECT [name]
FROM table2

UNION ALL

SELECT [name]
FROM table3

UNION ALL
.
.
.
SELECT [name]
FROM table100


) AS A
GROUP BY [name]
HAVING Count(*) > 1
ORDER BY [name]


It works on 100 tables with few records but not with 352716 records per table.

???

 

[Norie]

Why are you using ALL?

From Access help:

By default, no duplicate records are returned when you use a UNION operation; however, you can include the ALL predicate to ensure that all records are returned.

BTW are there duplicate records within each individual table?

 

[Andrew Fergus]

Hi, going back to Norie's original suggestion :

You could create append queries to add all the names to a new table.

If you first created the target table with the name field and set that field to the primary key, you can then append the 61 tables and any duplicates will not get appended because they will be in breach of the primary key (i.e. no duplicates).

HTH, Andrew,