Having access query list duplicates once?

[poodie]

Hi im brushing back up on my access/excel skills again, and i cant remember how to get duplicate items to only be listed once (assuming it is possible). I have to do the following:

"Create a query based on bad_product_IDs that will list each value once, then delete all records containing these product IDS from the invoice_detail table. Save your delete query as bad_products_delete_details."

I can delete it no problem, but I dont know what to put into access that will give me my desired result of having ID's listed once. I guess i could do a find duplicate query and get rid of those, but i thought there was another way.

any help is appreciated. i just stumbled onto this forum the other day, wasnt aware they had forums of this size for this stuff, really looks like a great place. thanks in advance.

 

[Norie]

To get duplicate items to list only once use Totals.

This will allow you to group any field.

For your example create a new query, add the table containing the bad_product_ID field, add that field to the query and then goto View>Totals.

There should now be a dropdown menu below the added field. It is set to Group By as default.

If you now run the query you will get unique ID's.

 

[poodie]

ah success! i didnt know you only wanted the one feild in there, thanks a lot ray:

edit: actually just kidding. I dont know how to delete them. i dont know how to bring the date i got from the query out of the invoice_detail table. im sure this is simple too but it i cant visualize it. when i like try to at the invoice_detail table to the query then it lists items more than once again so maybe i have to save that query and then run a query off that.

well i tried to make a new query and then put the old query results in it and run the delete but i have to specify the table so now im confused again. any help please?

 

[poodie]

ok ive been working on this thing for entirely too long. i cant visualize how to set up the query. i thought i found a way but when i try to run it i get this :

"Could not delete from specified tables. (Error 3086)
You tried to delete data from one or more tables, but the deletion could not be completed.

Possible causes:

You do not have permission to modify the table. To change your permissions assignments, see your system administrator or the table's creator.
The database was opened for read-only access. The database is read-only for one of these reasons:
You used the OpenDatabase method and opened the database for read-only access.
The database file is defined as read-only in the database server operating system or by your network.
In a network environment, you do not have write privileges for the database file.
In Microsoft® Visual Basic®, you used the Data control and set the ReadOnly property to True.
To delete the data, close the database, resolve the read-only condition, and then reopen the file for read/write access.:

now i havent done anything different when opening it, the "read-only" box isnt checked in the properties. i know this is fairly simple stuff, but my mind is blank. any help again would be appreciated.