Only pull 1st record with specific value in a specific slot

[Challseus]

Hi all.

Let say you have a database that has values such as first name, last name, address, phone number etc. Now, lets say that there might be 2 records in this database where the 2 people live at the same address. Now, lets say there are many records like that.

How would you run a query that would pull all records, but if it comes across a duplicate value (i.e. address), skip it.

The reason for this because I need to send out letters to a lot of address, but I don't want to send multiple letters to the same house.

Thanks in advance for any help.

 

[SydneyGeek]

Re: Only pull 1st record with specific value in a specific s

You can do that by building a unique set of addresses in a query.
Try this:
1. Build a query with all of the address fields, but NOT the names.
2. Still in Design view, switch to SQL view. You'll see something like
SELECT .....
FROM ....;

Change this to
SELECT DISTINCT ....
FROM ....;

Save the query and check it out -- you should now have no duplicates in your addresses. Use this query for your mailout.

Denis

 

[Challseus]

Re: Only pull 1st record with specific value in a specific s

Hmm, that didn't seem to do anything, although I understand what you are trying to get at. Maybe I should list everything I did.

1) Create a new query using the wizard.
2) Add every field, except for the last name field.
3) Open up query in Design View.
4) Open up query in SQL mode.
5) add "DISTINCT" after "SELECT"
6) Save query, and open up again.
7) Observe that there are 56954 records.
8) Open up main table and observe that there are 56954.
9) Realize that I screwed up somewhere

 

[SydneyGeek]

Re: Only pull 1st record with specific value in a specific s

Leave out the First Name field and try again. You don't want ANY names in the query.

Denis