Find Products Latest Date 1

[molly]

i have a simple qry need. I want to find the latest Date for Each FormulaID and the Qty and costs for that days formula record.

attached is a sample file. i read the site but i did not see what i am looking for.

thanks alot. Molly

 

[fneily]

For simplicity, two steps.
First create a query to pick off FormulaID and Invoice Date. In Design view, click the Totals button. This'll add a row called Total. Under FormulaID it should say Group By. Under Invoice Date, click on Group By and using the dropdown arrow, change it to MAX. Save the query.

In the second query, use the above query and the tbldata as the source. Manually connect FormulaID to FormulaID and MaxOfInvoiceData to Invoice Date. Put FormulaID, Invoice Date Qty Cost1 and Cost2 in the grid. Run it and there's your answer.

Also, your tblData is designed incorrectly. It violates the first normal form. It should just have:
FormulaID Date Qty Cost
no cost1, cost2, etc. No, no.

 

[molly]

thanks so much. it worked like you said to first get the date. molly