There are many different types and ways to run queries that your Microsoft Access database application provides and one useful ms access query is where you can create a distinct or unique value list from duplicate or repeating values from other records.
Check out this 4 minute video tutorial which clearly explains two ways to create a unique value query report.
As the video shows, you can use either the property sheet to select unique value or the SQL statement approach using the keyword Distinct. In fact, an extension to this keyword is further explained in a previous post of mine which covers the distinct row and unique records property too.
Microsoft Access Database Tutorial – Selecting Unique And Distinct Values In A Query
Another approach is to consider changing a standard SELECT query into a Group/Total query which will have the same effect providing you have kept the number of output fields to either the one field being interrogated or just a couple of fields which can be naturally grouped together for the same result (if applicable).
Ideally, using the keyword DISTINCT or DISTINCTROW (or the properties from the property sheet) will be a better approach. Remember, you can only set one of the two properties from the property sheet at a time to the value ‘Yes’ which will switch the other to the value ‘No’. It’s also the same within SQL view statement area when specifying either of the two keywords!
The benefit to this type of Microsoft Access database query is the starting process to normalising data if it was say imported from a flat-file database file like an Excel spreadsheet and the next step to extracting and creating new tables using some of the ACTION queries available. No programming is needed here!
To learn more about MS Access queries and the more advanced types, you could consider my eBook on More Access database queries which has proved very useful to database developers and is also part of my eBook bundle offer of six eBooks – why not take a look?
Have a great Christmas, Happy Holiday and prosperous New Year to all (around the globe) 🙂
Tags: building distinct list in access, how to find unique values in a query, microsoft access queries, ms access queries, using distinct in your queries