remove duplicates (1 Viewer)

[james7705]

I have a query which gets information from 2x tables where the I'd on one table is the reference number on the second table.
I would like to know how I can remove the duplicates on my reference number field?

 

[ypma]

Have you tried to index the Reference number and tick Yes (No Duplicates) box ,found in the Data type of the reference number .
To identify the existing duplicates you could create a query using the Find duplicates wizard which is located in the create query drop down .access 2010

Hope this is of help I am not a pro so there may well be other solutions

 

[james7705]

I'm actually tryng to remove the duplicates on the query. The two tables are joined on a one to many relationship where many records in table 2 equal one record in table 1.
What my query does is remove all the zero values in table 2 then generates a report based on table 1's ID
What I need is to remove all the duplicate values on the ID field so that I can see which reports do not contain zero values.
Basically what it is:
TABLE 1 contains:
ID field, adress, name, date
TABLE 2 contains:
Reference number (=ID field table1), items, quantity
I'm generating a report based on table 1's ID field for all items used from table 2. My query ignores all zero values in quantity fild of table 2, but as soon as there is more that one item allocated to a single place in gives me duplicates and I don't want that.
I have tried to add select distinct to the SQL, but that doesn't seem to work, unless I'm doing something wrong(highly possible )

Please help

 

[JHB]

Show you SQL-string and also a print screen of the result.