Unique Count in Sigma Query

L

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.
 
K

KARL DEWEY

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

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.
 
K

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;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top