dazed & confused

razorking

Registered User.
Local time
Today, 11:37
Joined
Aug 27, 2004
Messages
332
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!
 

Attachments

  • query.JPG
    query.JPG
    37 KB · Views: 143
Last edited:
Not Sure This Will Work!!

Create new query, go to sql and paste this in:

SELECT TOP 1 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))
ORDER BY CountOfqty DESC;
 
Last edited:
do a total query, and in the total row select "Max" so that it will display your greatest value.
 
Sorry but it's not working.

My explanation was probably confusing. I will try again:
I have a table with different item numbers. We sell the particular items by a certain length. The length varies by the requirements of the customer. I am trying to determine what the most popular size (length) of each item that we sell for a given date range. I have a record in the database corresponding to each individual item sold. So what I have done is to make a query with item number, size sold-grouped, size sold-counted. This way I can see, for example, that we sold 10 R5s at 5ft, 20 R5s at 6ft, 30 R5s at 7ft etcetera.


The problem is I only want to see the record corresponding to the item/size with the most sells (hihgest value in the counted total by size)

Please see the new image attached.

Thanks for looking!
 

Attachments

  • query.JPG
    query.JPG
    18.9 KB · Views: 138
Generate a simple "count" query - count sales for each size with group-by for your size indicator.

Now based on that query (not the original table), generate an order-by query using descending order of counts. Open it by hand once to verify that it is working.

Now open the second query in SQL view. Behind the word SELECT insert the word FIRST. Switch to query view. See if that does it.
 
The_Doc_Man said:
Generate a simple "count" query - count sales for each size with group-by for your size indicator.

Now based on that query (not the original table), generate an order-by query using descending order of counts. Open it by hand once to verify that it is working.

Now open the second query in SQL view. Behind the word SELECT insert the word FIRST. Switch to query view. See if that does it.


I have tried it with no luck.

I have created query1 as you desribed:
SELECT tblInreg.description, tblInreg.size, Count(tblInreg.size) AS CountOfsize
FROM tblInreg
GROUP BY tblInreg.description, tblInreg.size;

Then query2:
SELECT FIRST qryTest.description, qryTest.size, qryTest.CountOfsize
FROM qryTest
ORDER BY qryTest.CountOfsize DESC;

When I run query2 I get a - syntax error message (missing operator)

:confused:
 
I have attached a sample database. You can run Query3 to see if it's what you needed.

Note: When there is a tie in an item, more than one record for that item will be returned.
.
 

Attachments

Then query2:
SELECT FIRST qryTest.description, qryTest.size, qryTest.CountOfsize
FROM qryTest
ORDER BY qryTest.CountOfsize DESC;

When I run query2 I get a - syntax error message (missing operator)
First() is an aggregate function just like Count(). It's not a key word.
 

Users who are viewing this thread

Back
Top Bottom