Need a way to filter out records that are duplicate in all fields but one.

[stell]

My apologies if the title isn't clear, i'm having a hard time articulating what my issue is.

Basically, I'm running a query that brings back a list of part numbers and info that corresponds to each part number. The issue is, there is one field, "Special Stock", that is causing duplicate entries. There are certain part numbers that can fall into more than one special stock category. So when I run a report, the sums and averages are all off because of the duplicate records (duplicate in every field except Special Stock). I need the special stock number to be included in the report, but I can only have each part number appear once. I need the part numbers with multiple special stock numbers to just pick one, and disregard the duplicate entries. This doesn't seem like a very logical thing to do in access, but I'm hoping someone can maybe point me in the right direction.

Thank yo

 

[CJ_London]

i'm having a hard time articulating what my issue is.

rather than articulating, suggest provide some example data and the outcome required from that sample data

 

[arnelgp]

we can do what you want by including a Function on your query that will only pick one special part number. but we need more info of your table.

 

[Ranman256]

if your table has keys,
make Q2, using the table , to get TOP 1 of : part#, key
make Q3 using Q2 and the table outer joined on the part, and ask for all items in Table NOT in Q2.