query to return date field corresponding to Max(Quantity)

[aero]

I'm having trouble writing a query that will give me the RPT_PERIOD field corresponding to the result of the MAX Quantity. Whenever I put it in the same query it ends up giving me a max for each Report Period. Anyone know what to do? Do I need to do a subquery and if so what would that be?

Query to determine Average:


SELECT table.PROD_CODE, table.PT_SP_TYPE, MAX(table.QUANTITY) AS MAXOfQUANTITY
FROM table
WHERE (((table.RESP_ID)=[Forms]![Selection Form]![RID]) AND ((table.RPT_PERIOD)>'140100'))
GROUP BY table.PROD_CODE, table.PT_SP_TYPE;

[JoeM]

Yes, you can do this in a series of two queries (or use a SubQuery).

The first query should just return the Max Quantity and the other key fields (that you are Grouping By).
Then, create a new query with joins the 1st query to your original data table based on your key fields and Quantity, and then you can return the fields that you want from the data table.

[aero]

I need the second to join the info from the first Query "Respondent ID", Product Code", "Product Type (3 per code)", and max("Quantity") for each combination of the fields that came before. The additional field I need this query to add on is the reportreport period for which the selected ID has a maximum quantity of each product code and product type. I'm new to SQL and MS Access. Can anyone provide the sql statement or tell me what needs to go in query design in access?

[JoeM]

You already pretty much most of the first query already written. So, something like this:

Query1

SELECT table.PROD_CODE, table.PT_SP_TYPE, MAX(table.QUANTITY) AS MAXOfQUANTITY
FROM table
WHERE table.RPT_PERIOD>'140100'
GROUP BY table.PROD_CODE, table.PT_SP_TYPE;

Query 2

SELECT table.PROD_CODE, table.PT_SP_TYPE, table.RPT_PERIOD, Query1.MAXOfQUANTITY
FROM table
INNER JOIN Query1
ON (table.PROD_CODE=Query1.PROD_CODE) AND (table.PT_SP_TYPE=Query1.PT_SP_TYPE) AND (table.QUANTITY=Query1.MAXOfQUANTITY)
WHERE table.RESP_ID=[Forms]![Selection Form]![RID];