Unique Count in Sigma Query

[LarryP]

Access 2003, Windows XP Pro.
SITUATION: Database has lots, which have sublots, which have items. Sublot
key is a barcode number. I have a query that joins the three tables, and
since a sublot can have multiple items, the sublot key can occur more than
once. I want to run a query against this big query that just groups and
summarizes some things, one of which is the number of sublots in a given lot.
Using a straight Count fails because of the multiple occurrences of the
sublot key value. Here's an attempt to show this pictorially:

LOT SUBLOT ITEM
A A123 A123-1
A A123 A123-2
A A123 A123-3
A A124 A124-1
A A124 A124-2
B B123 B123-1
etc.

In this case, my summarizing query gives me a count of 3 for sublot A123,
where
I want to see a 1. Any suggestions on how to get what I want? So far the
lightbulb over my head hasn't gone on.

 

[KARL DEWEY]

Do you have another field with data unique to the Lot-sublot combination sich
as a date or autonumber field?

 

[LarryP]

No, I don't. At lot level the lot number (autonumber) is the key, at sublot
level it's the lot number/sublot number combination (and at item level it's
lot/sublot/item).

I went ahead yesterday and wrote a simple VBA function to solve this, but if
you or anyone can suggest how I could have done it within the query designer
I'd still be interested in that answer.

 

[KARL DEWEY]

Using the autonumber LotNumber it breaks any ties in the group like this --
SELECT Q.LOT, Q.SUBLOT, Q.SUBLOT & "-" & Str((SELECT COUNT(*) FROM
[YourTable] Q1 WHERE Q1.[LOT] = Q.[LOT] AND Q1.[SUBLOT] = Q.[SUBLOT] AND
Q1.[SUBLOT] & 1/Q1.[LotNumber] < Q.[SUBLOT] & 1/Q.[LotNumber])+1) AS ITEM
FROM YourTable AS Q
ORDER BY Q.LOT, Q.SUBLOT;