Duplicate records - query

[John M]

Hi,

I have two incomplete lists of staff which combined create a full list of
staff with duplicates. I wish to create a full list with no duplicates.
Each member of staff has and obviously a name. I wish to select the list of
staff using the code, but here lies the problem. The naming is not entirely
consistent

Table 1
Code Name
SMJ Mr. J. Smith

Table 2
Code Name
SMJ Mr J. Smith

In a query using SELECT DISTINCT - I end up with these being two records
because of the "." after the Mr! If I only select on the basis of code,
then fine, bu I want to return both the Code and the name.

Thanks in advance

[LoopyNZ]

Hi John,

Do a Totals query on the data like this (where Table_Joined is the
combined data from Table 1 and Table 2):

SELECT Code, First(Name) AS UserName
FROM Table_Joined
GROUP BY Code;

This query ensures you will only get one name for each code. (Of course
you'll have a different problem if you have multiple _actual_ users for
any one code. In that case a different approach would be required.)

By the way, "Name" is a reserved word in Access (like "Date", "Value",
etc.), so shouldn't be used as a field name.

------------
LoopyNZ
------------
------------------------------
Original Message:

Duplicate records - query
From: John M
Date Posted: 9/23/2004 11:23:00 PM

Hi,

I have two incomplete lists of staff which combined create a full list
of
staff with duplicates. I wish to create a full list with no duplicates.
Each member of staff has and obviously a name. I wish to select the list
of
staff using the code, but here lies the problem. The naming is not
entirely
consistent

Table 1
Code Name
SMJ Mr. J. Smith

Table 2
Code Name
SMJ Mr J. Smith

In a query using SELECT DISTINCT - I end up with these being two records
because of the "." after the Mr! If I only select on the basis of code,
then fine, bu I want to return both the Code and the name.

Thanks in advance

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!