DINSTINCT query question

[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

 

[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".

 

[Guest]

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

 

[Brian Camire]

It sounds like you're trying to "clean" the data -- in other words, manually
determine and correct if the category has been incorrectly assigned.

I so, a simple approach might be to just sort the data, maybe using a query
whose SQL looks something like this:

SELECT
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[category],
[Your Table].[Your Memo Field 1],
[Your Table].[Your Memo Field 2],
..
..
..
[Your Table].[Your Memo Field N]

However, Access 97 and earlier do not allow ORDER BY on memo fields, and
although later versions may, they order by only the first 255 characters.

If you get a "Can't sort on Memo or OLE Object" error, you might try this
instead:

SELECT
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[category],
Left([Your Table].[Your Memo Field 1],255),
Left([Your Table].[Your Memo Field 2],255),
..
..
..
Left([Your Table].[Your Memo Field N],255)

If this is a one-time manual exercise, then ordering by only the first 255
characters may be acceptable.

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

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]