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

stell

Registered User.
Local time
Today, 03:54
Joined
Jun 14, 2017
Messages
15
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

Super Moderator
Staff member
Local time
Today, 10:54
Joined
Feb 19, 2013
Messages
16,553
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

..forever waiting... waiting for jellybean!
Local time
Today, 18:54
Joined
May 7, 2009
Messages
19,175
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

Well-known member
Local time
Today, 06:54
Joined
Apr 9, 2015
Messages
4,339
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.
 

Users who are viewing this thread

Top Bottom