Forum Discussion
Removing duplicates from Access query
I have an Access database to catalog a collection. In the table "Sets", there is a column that "Material Type" that links to another table (Materials.ID). Material Type allows multi-selection to su...
Use the keyword DISTINCT in query SQL to remove duplicates.
This results from the use of a 'multi-valued field', which is something most experienced Access developers counsel against. Using the DISTINCT predicate will make a query non-updatable, so this might not be suitable for a form's RecordSource. I would advise that, rather than using a multi-valued field, you model the many-to-many relationship type between Sets and MaterialTypes by the time-honoured method for modelling the relationship type by a table which resolves the relationship type into two one-to-many relationship types.
You might like to take a look at MVFCorrector.zip in my public databases folder at:
https://1drv.ms/f/c/44cc60d7fea42912/EhIppP7XYMwggESpAAAAAAABaDKZCllSuweYBPJ5zKa3cg
This little demo file illustrates how the database structure can be corrected by means of code in a form's module
You might like to take a look at MVFCorrector.zip in my public databases folder at:
https://1drv.ms/f/c/44cc60d7fea42912/EhIppP7XYMwggESpAAAAAAABaDKZCllSuweYBPJ5zKa3cg
This little demo file illustrates how the database structure can be corrected by means of code in a form's module
