Finding duplicates

[Maxi]

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

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
You could use a UNION ALL query as the source for the "find duplicates"
query. E.g.:

JET SQL:

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

UNION ALL

SELECT [name]
FROM table2

UNION ALL

SELECT [name]
FROM table3

UNION ALL

[Maxi]

Thanx Foster

I tried 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.

But when I try to find duplicates with the help of your SQL statement,
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 TEMP=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/Analyze database to crunch the size but it does
not work.

Any ideas???

BTW what is "JET SQL:"? You started your SQL statement with that. Is
that a syntax?

Maxi

[Maxi]

Somebody told me to make .mde file so that the size reduces and you
will be able to perform the above task.

When I tried to do that, "Make MDE" option is grayed out???
I am using ACCESS 2002

Please help

Maxi

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

JET SQL means the SQL statement that followed was in the SQL syntax that
the Access database engine follows, close to SQL-92 (JET = Joint Engine
Technology).

The UNION query creates a temporary table of all the data in each of the
UNION statements, therefore, you will have a table of approx. 21,515,676
rows. If you multiply that times the size of your column you get the
approximate number of bytes of space you're union statement will need to
just hold that data. The main query's GROUP BY also builds a temporary
table of approximately the same size, but slightly smaller. So you've
got a need for A LOT of disk space, plus A LOT of RAM.

You could probably run a sequential process on the tables. You could
run a merge sort on the data (instead of data in tables, data would be
in text files), then sequentially scan the final sorted file "looking"
for duplicates - they'd be items w/ the same spelling, in sequential
order. Once that process was complete you could create a table for all
the data, put a Primary Key (PK) on the column(s) that define a row's
uniqueness & put the data back in that table. The PK would prevent any
future duplicates.

I used to have some code that did merge sorts, but I think it was in the
FORTH language & I'm not sure where that code is now-a-days. You can
probably find some on the Web. Search for '"merge sort" VB' on Google
or Clusty, or your favorite search-engine site (remove the single quotes
when typing the search string into the search text box).

=====

Making an .mde file has nothing to do w/ your problem, which is lack of
RAM and disk space.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQfFxbIechKq OuFEgEQKAAgCcDp 2+XZjs+lspApQ/NtqW/JdbeCwAn0Dv
eSN/7oOh25ZMLOm/i8HLdTvV
=62ox
-----END PGP SIGNATURE-----
Maxi wrote:

Thanx Foster

I tried 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.

But when I try to find duplicates with the help of your SQL statement,
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 TEMP=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/Analyze database to crunch the size but it does
not work.

Any ideas???

BTW what is "JET SQL:"? You started your SQL statement with that. Is
that a syntax?

Maxi

[Darryl Kerkeslager]

An Access mdb needs to be in Access 2002 format to make it an MDE file in
Access 2002.

Perhaps you originally saved it in Access 2000 format?

Don't forget to save a backup before you make it an MDE.
Darryl Kerkeslager

"Maxi" <ma********@hot mail.com> wrote

Somebody told me to make .mde file so that the size reduces and you
will be able to perform the above task.

When I tried to do that, "Make MDE" option is grayed out???
I am using ACCESS 2002

Please help

Maxi

[Salad]

Maxi wrote:

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

I multiplied the following:
? 352716.0 * 40.0 * 61.0
860,627,040

Since a record may exist in table 1 and 2, and another in table 3 and 4
but not in 1, you'd need to scan all records.

You can see that if the name field is 40 chars, you have 850 meg+ in
bytes. The max size of a database mdb in A97 is 1 gig, so you are
slightly under it.

You might want to try creating a new MDB with a table in it that would
hold the names. You might even want to have an integer field to hold
the table name number...1 to 61. Then link that table to your
database. Then run a routine to append all names into that new table.
Air code:

Sub BuildTable
'assumes the tablenames as Table1 to Table61
Dim intFor As Integer
Dim strSQL As String
Dim strTable As String
For intFor = 1 to 61
strTable = "Table" & intFor
strSQL = "INSERT INTO HoldingTable " & _
"( NameField, TableNum ) " & _
"SELECT NameField, " & _
intFor & " As TNum FROM " & strTable & ";"
Currentdb.Execu te strSQL
Next
msgbox "Done"
End Sub

This will append all of the records from all 61 tables

I doubt you can create an index on the name field in Holding table since
having one that may then exceed the mdb size limit.

Once all of the names are appended to table HoldingTable you could try
to run a query.
Select NameField, Count(NameField ) As NameCount _
From HoldingTable _
Group By NameField _
Having Count(NameField ) > 1

It may take awhile to execute since you don't have an index on the
NameField but you should get the answers. By having the table number
also, you can later determine which table the duplicates came from.

To do that, you may want to create a table that has the table names and
the numbers 1...61. You could use this table for determining the table
number, in the above For/Next loop. Because if you had a duplicate on
the name Smith, you'd certainly like to know which tables Smith exists in.