Conditional Counts for Queries?

[Kozbot]

Hello

I have a query that is returning material used for production runs. Originally I wanted the query to only return unique lot numbers for raw material used by setting the count criteria=1.

However this isn't working anymore because sometimes the same lot will sometimes be entered multiple times because of different blends. Meaning that the query does not return all the lots used because they are counted multiple times.

How can I get that count criteria to ignore the same lot number with other blend numbers? I can't just add a specific criteria for the blend number because I want any unique lots used in that blend to appear.

Or if anyone has a better way of returning unique values for a field but with the condition described above
Thank you!

 

[Cronk]

It's all to do with the groupings. You might need to use a second query. I could perhaps be more specific if you advised the field structure on which you are running the query.

 

[plog]

You lost me in your terms. Can you provide sample data from your tables and then what data should be returned for your query based on that sample data>

 

[WayneRyan]

Kozbot,

Query1:

Select LotNumber, Count(*)
From   YourTable
Group By LotNumber
Having Count(*) > 1

Query2:

Select a.BlendNumber, a.LotNumber
From   YourTable As a Left Join Query1 As b on
          a.LotNumber = b.LotNumber
Where  b.LotNumber Is Null
Order By a.BlendNumber, a.LotNumber

hth,
Wayne