Filtering field

[PolarBear]

I have a table that contains multiple instance records for members: For examle:

ID ED_CODE Year
123456 104 1977
123456 104 1972
123456 106 1987
123456 999 1992

Can I create an access query that would allow me to loop through all records in a table and return a query result that provides only a single instance of each record?

For example: want to search the first row and IF "104" exists, return the result "104" for this row

Then go to the next Unique ID > search for "104", if it does not exist search for "106" and if exists return "106" > If 106 does not exist search for "999" and if it exists return "999" then go to the next Unique ID and start the loop again

Regards,

Polar Bear

 

[dcx693]

If you just want a list of unique values in the CODE field, it's easy. Create a query that only returns the CODE field, then go to the query properties (go to the Query menu, choose Properties). On the line that says "Unique Values", choose Yes.

However, if you also want to show the other fields as well as just one entry for the CODE field, then you'll have to decide how Access decides which record to return. You'd create a totals query, group by the CODE field, then choose First, Last, Max, Min etc... for the other fields.