Duplicates Recordset?

[P B via AccessMonster.com]

I have a list of 160,000 records with these fields:

fname, lname, address, city, state, zip, dob

I need to generate a list with all fields where the first initial of lname
and the dob are equal.

How? I can't seem to get Left() to work in a query. Could I put this in a
VBscript and generate a recordset? What's the easiest way to generate this
list? This seems so simple.

Thanks for your help.

 

[Allen Browne]

1. In query design, depress the Total button on toolbar (upper sigma icon).
Access adds a Total row to the design grid.

2. Type this expression into the FieldRow:
Init: Left([lname],1)

3. Drag dob into the grid.
Accept Group By under thse fields.

4. Drag a non-blank field (such as your primary key) into the grid.
In the Total row under this field, choose Count.
In the Criteria row, enter:

1

The query now shows how many duplicates there are based in the first letter
of the lname and the dob.

If you wanted to see the full lname for these entries, you could drag lname
into the grid twice. In the Total row under the first, choose Min, and under
the second, Max. Where the Count is 3 or more there will be other names, but
this sometimes helps you sort out what you are dealing with.

Another completely different approach is to INNER JOIN 2 copies of the same
table. You will need to do this in SQL View. Something like this:

SELECT Table1.*
FROM Table1 INNER JOIN Table1 AS Dupe
ON Table1.dob = Dupe.dob AND Left(Table1.lname,1) = Left(Dupe.lname)
WHERE Table1.ID <> Dupe.ID;

 

[P B via AccessMonster.com]

SELECT 8_22_05list.*
FROM 8_22_05list INNER JOIN 8_22_05list AS Dupe
ON 8_22_05list.DOB = Dupe.DOB AND Left(8_22_05list.LName,1) = Left(Dupe.
LName)
WHERE 8_22_05list.ID <> Dupe.ID;

The above is the query. WHen I run it it produces this error:

Syntax error (missing operator) in query expression '8_22_05list.DOB = Dupe.
DOB'.

Not an expert so I can't easily identify what it's asking for.

1. In query design, depress the Total button on toolbar (upper sigma icon).
Access adds a Total row to the design grid.

2. Type this expression into the FieldRow:
Init: Left([lname],1)

3. Drag dob into the grid.
Accept Group By under thse fields.

4. Drag a non-blank field (such as your primary key) into the grid.
In the Total row under this field, choose Count.
In the Criteria row, enter:

1

The query now shows how many duplicates there are based in the first letter
of the lname and the dob.

If you wanted to see the full lname for these entries, you could drag lname
into the grid twice. In the Total row under the first, choose Min, and under
the second, Max. Where the Count is 3 or more there will be other names, but
this sometimes helps you sort out what you are dealing with.

Another completely different approach is to INNER JOIN 2 copies of the same
table. You will need to do this in SQL View. Something like this:

SELECT Table1.*
FROM Table1 INNER JOIN Table1 AS Dupe
ON Table1.dob = Dupe.dob AND Left(Table1.lname,1) = Left(Dupe.lname)

I have a list of 160,000 records with these fields:

[quoted text clipped - 8 lines]

this
list? This seems so simple.

 

[Allen Browne]

Put square brackets around the name that starts with a number:
SELECT [8_22_05list.]* FROM ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

SELECT 8_22_05list.*
FROM 8_22_05list INNER JOIN 8_22_05list AS Dupe
ON 8_22_05list.DOB = Dupe.DOB AND Left(8_22_05list.LName,1) = Left(Dupe.
LName)
WHERE 8_22_05list.ID <> Dupe.ID;

The above is the query. WHen I run it it produces this error:

Syntax error (missing operator) in query expression '8_22_05list.DOB =
Dupe.
DOB'.

Not an expert so I can't easily identify what it's asking for.

1. In query design, depress the Total button on toolbar (upper sigma
icon).
Access adds a Total row to the design grid.

2. Type this expression into the FieldRow:
Init: Left([lname],1)

3. Drag dob into the grid.
Accept Group By under thse fields.

4. Drag a non-blank field (such as your primary key) into the grid.
In the Total row under this field, choose Count.
In the Criteria row, enter:

1

The query now shows how many duplicates there are based in the first
letter
of the lname and the dob.

If you wanted to see the full lname for these entries, you could drag
lname
into the grid twice. In the Total row under the first, choose Min, and
under
the second, Max. Where the Count is 3 or more there will be other names,
but
this sometimes helps you sort out what you are dealing with.

Another completely different approach is to INNER JOIN 2 copies of the
same
table. You will need to do this in SQL View. Something like this:

SELECT Table1.*
FROM Table1 INNER JOIN Table1 AS Dupe
ON Table1.dob = Dupe.dob AND Left(Table1.lname,1) = Left(Dupe.lname)

I have a list of 160,000 records with these fields:

[quoted text clipped - 8 lines]

this
list? This seems so simple.

 

[John Spencer (MVP)]

Also, Add the number 1 to

.... AND Left([8_22_05list].LName,1) = Left(Dupe.LName,1)

Put square brackets around the name that starts with a number:
SELECT [8_22_05list.]* FROM ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

SELECT 8_22_05list.*
FROM 8_22_05list INNER JOIN 8_22_05list AS Dupe
ON 8_22_05list.DOB = Dupe.DOB AND Left(8_22_05list.LName,1) = Left(Dupe.
LName)
WHERE 8_22_05list.ID <> Dupe.ID;

The above is the query. WHen I run it it produces this error:

Syntax error (missing operator) in query expression '8_22_05list.DOB =
Dupe.
DOB'.

Not an expert so I can't easily identify what it's asking for.

1. In query design, depress the Total button on toolbar (upper sigma
icon).
Access adds a Total row to the design grid.

2. Type this expression into the FieldRow:
Init: Left([lname],1)

3. Drag dob into the grid.
Accept Group By under thse fields.

4. Drag a non-blank field (such as your primary key) into the grid.
In the Total row under this field, choose Count.
In the Criteria row, enter:
1

The query now shows how many duplicates there are based in the first
letter
of the lname and the dob.

If you wanted to see the full lname for these entries, you could drag
lname
into the grid twice. In the Total row under the first, choose Min, and
under
the second, Max. Where the Count is 3 or more there will be other names,
but
this sometimes helps you sort out what you are dealing with.

Another completely different approach is to INNER JOIN 2 copies of the
same
table. You will need to do this in SQL View. Something like this:

SELECT Table1.*
FROM Table1 INNER JOIN Table1 AS Dupe
ON Table1.dob = Dupe.dob AND Left(Table1.lname,1) = Left(Dupe.lname)
WHERE Table1.ID <> Dupe.ID;

I have a list of 160,000 records with these fields:

[quoted text clipped - 8 lines]
this
list? This seems so simple.