Query - group by question

[Adam]

Hi All,

i've posted another question which kinda relates to what i'm trying to
achieve here.

I want have a table which has duplicated files. I want to seperate
these duplicates out into another table, where i can do a delete query.

To do this i need to group by a field called AnswerID, the group needs
to be by MIN, and this needs to apply at each questionID.

When I do this, standard group by MIN on AnswerID and then sort
ascending by questionID, it groups the entire lot, only showing one
record, which is the MIN of AnswerID.

Does anyone know how to show the smallest number of AnswerID for each
QuestionID?

I'm doing something clearly wrong!

Many Thanks

Adam

 

[Jeff Boyce]

Adam

If you have "duplicates", it really doesn't matter which one you keep, does
it?

If this table's Primary Key is being referred to by another table's foreign
key, you need to ensure that you don't "orphan" any records in that
secondary table.

One approach would be to create a new (empty) table with the same structure
as your "duplicates" table. Open that new table in design mode and set a
unique index that spans all the fields you are using to determine there's a
"duplicate" ... I think you are limited to 10 fields (if you have more than
ten to index, post back with a description ... there may be other issues to
resolve).

Now create an append query from the current table to this new one. Since
the new table has a unique index, Access won't add "duplicates". You'll be
left with a single copy of each record in the new table. Again, if there
are any secondary/child tables pointing back to the first table, you'll need
to use other methods to preserve those connections.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Adam]

Hi Jeff,

That's brilliant, thank you for your help!


Adam