I have a table containing item numbers, serial numbers and quantities (the quantity represents a linear piece of material) shipped. I have created a query that groups the items by their description and quantity and then counts the number of shipments for any given quantity (size of material shipped). So far so good...
What I am missing is how to only show the record for each product with the most number of shipments by size. Very confusing I'm sure. Please see the attached image. I only want the highlited record from this group and the similar record for all other distinct items (descriptions).
Here is my current SQL:
SELECT tblInreg.description, tblInreg.qty, Count(tblInreg.qty) AS CountOfqty
FROM [inreg12(new)] AS tblInreg
WHERE (((tblInreg.date)>#7/1/2004#))
GROUP BY tblInreg.description, tblInreg.qty
HAVING (((tblInreg.qty) Between 0 And 33));
Thanks!
What I am missing is how to only show the record for each product with the most number of shipments by size. Very confusing I'm sure. Please see the attached image. I only want the highlited record from this group and the similar record for all other distinct items (descriptions).
Here is my current SQL:
SELECT tblInreg.description, tblInreg.qty, Count(tblInreg.qty) AS CountOfqty
FROM [inreg12(new)] AS tblInreg
WHERE (((tblInreg.date)>#7/1/2004#))
GROUP BY tblInreg.description, tblInreg.qty
HAVING (((tblInreg.qty) Between 0 And 33));
Thanks!
Attachments
Last edited: