DINSTINCT query question

J

Joe Williams

I have an inventory table that has the following fields: PartNumber,
Location, Qty, SerialNumber

What I would like is a query that tells me the number of UNIQUE
serialnumbers for each partnumber.

For instance, if the following were a set of data from the table:

Part, Location, Qty, SerialNumber
PARTABC, SHELF 5, 45, XYZ
PART123, ROW 1, 103, 456
PARTABC, TABLE5, 106, 1342F
PART123, ROW8, 140, 456

The desired query result would be:

PartNumber, NumUniqueSerialNumbers
PARTABC, 2
PART123, 1

Thanks

Joe
 
B

Brian Camire

You need two queries -- one (say, named "Query1") that finds the distinct
combinations of part number and serial number whose SQL might look something
like this:

SELECT DISTINCT
[Your Table].[PartNumber],
[Your Table].[SerialNumber]
FROM
[Your Table]

and another based on the first that counts the number of distinct serial
numbers for each part number, and whose SQL might look something like this:

SELECT
[Query1].[PartNumber],
Count([Query1].[SerialNumber]) AS [NumUniqueSerialNumbers]
FROM
[Query1]
GROUP BY
[Query1].[PartNumber]

This assumes your table is named "Your Table".
 
G

geeksdoitbetter

building on this: i have a query with two fields [docID] and [category]

i'd like the query to return all unique categories

when i use the SQL below, i get *all* records - because each docID is unique
*grin*

i need to see the docID so i can double check the record using the form -
the form contains several memo fields that i'll use to (visually) evaluate
whether the catagory should be unique
 
Top