When using Microsoft Access, have you ever wondered what the difference was between unique values and unique records in your queries? Here is how you can distinguish between the two:
Unique values - Relate to the DISTINCT statement. It checks for unique values ONLY in in the fields selected for output, and eliminates duplicate rows.
Example:
ID Name Deposit
1 Mouse, Mickey $10
2 Duck, Daisy $8
3 Mouse, Mickey $19
4 Duck, Daisy $15
If you chose Unique Values for your query for the name column the return would be:
Name
Duck, Daisy
Mouse, Mickey
Just the two unique names in the list.
The results are not updatable, since they are not necessarily corresponding to a unique record.
Unique records - Relate to the DISTINCTROW statement. It checks for unique values in ALL fields in the table that you are querying, not just the fields listed for output in the query.
Example:
ID Name Deposit
1 Mouse, Mickey $10
2 Duck, Daisy $8
3 Mouse, Mickey $19
4 Duck, Daisy $15
If you chose Unique Records for your query for the name column the return would be:
Name
Duck, Daisy
Duck, Daisy
Mouse, Mickey
Mouse, Mickey
The results show evidence of all four records.
If the table is keyed (has unique id) then the results are updatable, since they correspond with a single record.
This information can also be found at SharePoint > Software Users Groups > Access > Unique Values vs Unique Records.docx.