DSUM and DMAX

  • Thread starter Thread starter nybaseball22
  • Start date Start date
N

nybaseball22

Hello. I have a query that lists items that we frequently purchase.
I am trying to get a subform to pull the top 2 items we purchase and
list them in text boxes. I have a query that set to SELECT TOP 2,
however I only get the results fo the MAX, or top 1.

Any help?

Thanks very much.
 
What is the SQL of the query? Select MAX(...) will return only one record,
unless you have a group by. I think what you need is a query that returns
the Count of item number, grouped by item number, then sorts on the count
descending, something like this:

SELECT TOP 2 [Duty Area], Count([Duty Area]) AS [CountOfDuty Area]
FROM [Task List]
GROUP BY [Duty Area]
ORDER BY Count([Duty Area]) DESC;

replacing ny names with yours, of course!

HTH

John
 
Just to add to what I said, a SUM function will work fine too. If the you
have a field which indicates the number of items purchaed in each record, you
coud have:

SELECT TOP 2 [ItemName], SUM([Quantity]) AS [SUMofQuantity]
FROM [Purchases]
GROUP BY [ItemName]
ORDER BY SUM([Quantity]) DESC;

which might be more like what you are trying to do.

John



J_Goddard said:
What is the SQL of the query? Select MAX(...) will return only one record,
unless you have a group by. I think what you need is a query that returns
the Count of item number, grouped by item number, then sorts on the count
descending, something like this:

SELECT TOP 2 [Duty Area], Count([Duty Area]) AS [CountOfDuty Area]
FROM [Task List]
GROUP BY [Duty Area]
ORDER BY Count([Duty Area]) DESC;

replacing ny names with yours, of course!

HTH

John
Hello. I have a query that lists items that we frequently purchase.
I am trying to get a subform to pull the top 2 items we purchase and
Click to expand...
[quoted text clipped - 4 lines]
Thanks very much.
Click to expand...
Click to expand...
 
Back
Top