Using 'Group By' to find a specific record...

[rede96]

Hi,
I have a table with various customer items, a current price and the date the price changed.

EXAMPLE DATA

CustID/ItemCode/DateChanged/Value
AAA 21 01/01/2004 £3.00
AAA 21 01/06/2005 £5.00
AAA 21 01/03/2005 £4.00
AAA 22 01/01/2005 £6.00
AAA 22 01/06/2005 £7.00
BBB 25 01/01/2005 £8.00
BBB 25 03/07/2005 £9.00
BBB 26 01/06/2005 £1.00
BBB 26 01/09/2005 £2.00

Note: Dates are in "dd/mm/yyyy" format. Also, not all data is in order in the table. It’s not how I would have designed it but that’s what I need to work with!

I would like to run a query that groups by custID & itemcode and also the most recent date for any price change. I would then like the query to display the relating price for the record it pulls out. (EG for custID 'AAA' and item 21, it would display the price of £5 as that is the value for the most recent price as of 01/06/2005)
I then want to link the custID & itemcode to another table that has sales in, to check that we are using the most recent price.

Is there a way I can do this via a query or do I need to put some code together?

Cheers
Red

 

[neileg]

I would use two queries. The first would hold the PK, the CustID, ItemCode, DateChanged. Make this a totals query and set the aggregate function to Max on the date. Then create another query, linking this query via the PK back to the same table and return the value.

To retrieve the matching records from the sales table, use this second query linked to the sales table on CustID and ItemCode.

 

[rede96]

I think I see what you mean. Create a query to pull out a unique ID from CustID, itemcode & date then link it back to the original table.

Thanks neileg.

(just wish Access had an easier way!