Duplicate query 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.
--
Message posted via http://www.accessmonster.com

[Larry Linson]

The Left function works fine for me, in a Query.

I accomplished what you want with three simple Queries: a Totals Query,
qtotMatching, with the Initial, BirthDate, and a count of Employee ID, with
a criteria on the count of >1, which in SQL view is:

SELECT Count(Employees.EmployeeID) AS CountOfEmployeeID, Left([LastName],1)
AS Initial, Employees.BirthDate
FROM Employees
GROUP BY Left([LastName],1), Employees.BirthDate
HAVING (((Count(Employees.EmployeeID))>1));

another Query, qryEmpWInit that that included all Fields from the Employees
table plus the initial Field, which in SQL view is:

SELECT Employees.EmployeeID, Left([LastName],1) AS Initial,
Employees.LastName, Employees.FirstName, Employees.Title,
Employees.TitleOfCourtesy, Employees.BirthDate, Employees.HireDate,
Employees.Address, Employees.City, Employees.Region, Employees.PostalCode,
Employees.Country, Employees.HomePhone, Employees.Extension,
Employees.Photo, Employees.Notes, Employees.ReportsTo
FROM Employees;

then a third Query, qryShowFields, with those two queries as data sources,
drag and drop join lines from Initial in the qtotMatching to Initial in the
qryEmpWInit, and also from BirthDate in qtotMatching to BirthDate in
qryEmpWInit, displaying all the Fields from qryEmpWInit, except the Initial
Field, which in SQL view is:

SELECT qryEmpWInit.EmployeeID, qryEmpWInit.LastName, qryEmpWInit.FirstName,
qryEmpWInit.Title, qryEmpWInit.TitleOfCourtesy, qryEmpWInit.BirthDate,
qryEmpWInit.HireDate, qryEmpWInit.Address, qryEmpWInit.City,
qryEmpWInit.Region, qryEmpWInit.PostalCode, qryEmpWInit.Country,
qryEmpWInit.HomePhone, qryEmpWInit.Extension, qryEmpWInit.Photo,
qryEmpWInit.Notes, qryEmpWInit.ReportsTo
FROM qtotMatching INNER JOIN qryEmpWInit ON (qtotMatching.BirthDate =
qryEmpWInit.BirthDate) AND (qtotMatching.Initial = qryEmpWInit.Initial);

And, to get useful test data, I copied the BirthDate Field so that both
Employees whose name began with D had the same BirthDate.

If you create a test Databases, link the Employees Table of the sample
Northwind database that comes with Access, copy each of these Queries into
the SQL view of a new Query, save them with the names I used, and run them
to test, you will have a starting point -- use a similar approach with your
own Tables and Fields, of course.

Larry Linson
Microsoft Access MVP

"P B via AccessMonster.com" <fo***@AccessMonster.com> wrote in message
news:53***********@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.
--
Message posted via http://www.accessmonster.com

[DavidE]

In the past I have done this by using an SQL sub query to to the duplicate
matching.

Assuming your data is in a table "tblClient".

First create a query "qClient" to list all of the fields in tblClient:

SELECT tblClient.*
FROM tblClient;

Next build a query with a sub query to count matched records:

SELECT (SELECT Count(tblClient.dob) AS Count FROM tblClient where
(DOB=qclient.dob and left(Iname,1)=left(qclient.Iname,1)) ) AS Count,
qClient.*
FROM qClient;

You may then filter the query output to select duplicate records which
will have a Count>1:

SELECT (SELECT Count(tblClient.dob) AS Count FROM tblClient where
(DOB=qclient.dob and left(Iname,1)=left(qclient.Iname,1)) ) AS Count,
qClient.*
FROM qClient
WHERE ((((SELECT Count(tblClient.dob) AS Count FROM tblClient where
(DOB=qclient.dob and left(Iname,1)=left(qclient.Iname,1)) ))>1));

I hope that this helps.