finding unique values

[buscher75]

Hello, I have a table that holds records of part numbers and also where the
part is used. So, part A123 could be listed in multiple rows if it is used
on multiple models. I want to run a query that will show any part that it
unique to a specific model.

The query should produce results for the specific model only and should
exclude any part that is used on another model even if is also used on the
specific model.

Hypothetically, I only want to see the second record, Unit A with part X456
because part Z123 is used elsewhere.

Model Part#
Unit A Z123
Unit A X456
Unit B Z123
Unit C Z123

I think I am close; however, it’s probably more like guessing than knowing.
Does anyone have a solution? Thanks in advance!

 

[Phil Smith]

Build a totals query with just the Part# field in it twice. Sort and
group by on the first, for the second, use count.

This is a list of all your part#s, and the number of times they are used.
Then build another query that links that query back to your table,
giving you the model numbers where the CountOfPart#=1.

 

[buscher75]

Hi Phil, I could not quite get the second query to work; however, I realize
the question I asked is not sufficient enough to get the results I want.

In my post, I made the assumption that the part number would only be listed
once; however it could potentially be listed multiple times within each
model. So, in thinking this through, I need help in creating a query that
identifies unique part numbers for a specific model, even if it is listed
more than once for that model. Hope this makes sense.

 

[Phil Smith]

If you right click on something, choose properties, then click on the
blank area where your tables are listed, chenage the line that says
"Unique Values" to yes.

This should give you the results you are looking for. If it doesn't,
paste the SQL of the query here.