DSUM and DMAX

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

 

[J_Goddard via AccessMonster.com]

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

 

[J_Goddard via AccessMonster.com]

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

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

[quoted text clipped - 4 lines]

Thanks very much.