finding a single record from many

[John Thomson]

I have a table with the following fields

id ( primary key) name code

values
1 red wine A
2 wine red A
3 sweet white wine B
4 white wine sweet B
5 wine white sweet B
6 pink wine C

I would like to create a make table query whic returns
only one record per code (the particular record does not
matter)

I would expect the table to be

1 red wine A
3 sweet white wine B
6 pink wine C

I have tried everything but no joy

I hope someone can help

Thanks in advance

 

[Cheryl Fischer]

One way to get what you want ...

1. Open your current table in design view
2. From the menu, click File|Save As. Save the current table's design to a
new table name
3. Close current table and open your new table in design view.
4. Click on your Code field.
5. Look for the "Indexed" property in the lower left corner. Set it to:
"Yes, no duplicates"
6. Save and close this table.
7. Last, create an append query from your current table to your new table.
The Index on your Code field will allow only one record per code value to be
added to the new table.


hth,

 

[Guest]

Cheryl

Thanks a million, it works a treat, The table in fact has
56940 records, of which there may be up to 49 synonyms,
thanks to you its size is now 5302 records

Thank you again

John Thomson

 

[Cheryl Fischer]

You're welcome - thanks for posting back with your results.

Good luck with your project.