Eliminating Duplicate Fields

[FJ Questioner]

I have a database listing the names and addresses of people. There are often
several family members living at one address. I want to end up with a list of
addresses without duplicates. The addresses are in 3 columns. Unit#, street
# and Street name.

How can I eliminate the duplicate addresses?

Thanks.

 

[Marshall Barton]

I have a database listing the names and addresses of people. There are often
several family members living at one address. I want to end up with a list of
addresses without duplicates. The addresses are in 3 columns. Unit#, street
# and Street name.

How can I eliminate the duplicate addresses?

SELECT First(namefield), [Unit#], [street #], [Street name]
FROM sometable
GROUP BY [Unit#], [street #], [Street name]

Note that using First just picks a relatively random name
from the people at the same address. If you want to use a
specific name then your table will need a field to indicate
which records contain the name to use.

 

[FJ Questioner]

Thanks Marsh.

Following your model I created a select query as follows but I get the
message that my statement contains a reserved word of has a spelling error or
the punctuation is incorrect. I tried changing to square brackets around
Name and adding square brackets around the table name but I got the same
error message.
Can you see what I've done wrong ??

FJ


SELECT; First(NAME), [UNIT_NUMBER], [STREET_NUMBER], [STREET_NAME], [WARD]
FROM PSSupporters
GROUP BY [UNIT_NUMBER], [STREET_NUMBER], [STREET_NAME]

I have a database listing the names and addresses of people. There are often
several family members living at one address. I want to end up with a list of
addresses without duplicates. The addresses are in 3 columns. Unit#, street
# and Street name.

How can I eliminate the duplicate addresses?

SELECT First(namefield), [Unit#], [street #], [Street name]
FROM sometable
GROUP BY [Unit#], [street #], [Street name]

Note that using First just picks a relatively random name
from the people at the same address. If you want to use a
specific name then your table will need a field to indicate
which records contain the name to use.

 

[Marshall Barton]

You have an extraneous ; after SELECT. Get rid of it.

You do not need [ ] around names that start with a letter
and only contain letters, digits and underscore.

You should not use names that are reserved words (e.g. Name)
If you do, you MIGHT be able to avoid confusion in SOME
places by enclosing it in [ ] and in an SQL statement,
quoalifying it with its table name:

SELECT; First(PSSupporters.[NAME]), UNIT_NUMBER,
STREET_NUMBER, STREET_NAME, WARD
FROM PSSupporters
GROUP BY UNIT_NUMBER, STREET_NUMBER, STREET_NAME
--
Marsh
MVP [MS Access]

Following your model I created a select query as follows but I get the
message that my statement contains a reserved word of has a spelling error or
the punctuation is incorrect. I tried changing to square brackets around
Name and adding square brackets around the table name but I got the same
error message.
Can you see what I've done wrong ??


SELECT; First(NAME), [UNIT_NUMBER], [STREET_NUMBER], [STREET_NAME], [WARD]
FROM PSSupporters
GROUP BY [UNIT_NUMBER], [STREET_NUMBER], [STREET_NAME]

I have a database listing the names and addresses of people. There are often
several family members living at one address. I want to end up with a list of
addresses without duplicates. The addresses are in 3 columns. Unit#, street
# and Street name.

How can I eliminate the duplicate addresses?

SELECT First(namefield), [Unit#], [street #], [Street name]
FROM sometable
GROUP BY [Unit#], [street #], [Street name]

Note that using First just picks a relatively random name
from the people at the same address. If you want to use a
specific name then your table will need a field to indicate
which records contain the name to use.