Re: finding unique values

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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 wrote:
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!

.

Relevant Pages

  • Re: Establishing Precedence In ORDERBY Condition Causing Problems.
    ... this query is likely to return multiple rows. ... Since @sort is NULL at this point, ... since your original query had some 7-8 tables whereof several repeated ... by commenting out the following code, ...
    (comp.databases.ms-sqlserver)
  • Re: Establishing Precedence In ORDERBY Condition Causing Problems.
    ... this query is likely to return multiple rows. ... Since @sort is NULL at this point, ... since your original query had some 7-8 tables whereof several repeated ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Limit records by date difference
    ... I 've done what you outlined and this is what I get when I run the query. ... you can see below I am getting multiple rows. ... Same thing with John I want to ... Since you're applying a criterion to a field from the second table, ...
    (microsoft.public.access.queries)
  • Changing Row Data into Column Data
    ... I have a query that potentially results in multiple rows of data for the ... For example - original data comes out as: ... single row for each. ...
    (microsoft.public.access.queries)
  • Re: Parameter Box Pops up Twice
    ... It sounds like you applied the sort ... Then open your query in Design mode, ... set Sort there. ... > on the date column and now the project number parameter box pops up twice. ...
    (microsoft.public.access.queries)