Question Access 2003 Create multi column filtered table

[sadist]

Hi

What I'm trying to achieve but am finding difficult and would appreciate guidance on is to create a datasheet from 2 odbc linked tables which I will be exporting to Excel. The resulting datasheet will have four columns, 'Purchase Order Number' 'Stock Code' 'Delivery Date' 'Unit Price'. The information I require in the datasheet is a unique stock code (one occurrence of each stock code) with the most recent unit price based on the last delivery date for each product. The linked tables contain many Purchase Orders for the same stock codes over a time period of a number of years where the unit price has varied.

I hope I've been able to put me question in an understandable way.

Many thanks

 

[Alansidman]

In a query, join the two tables on unique stock code. Add the fields you wish from each of the tables to your query grid. Here is a link to Queries if you need to be refreshed.

http://www.databasedev.co.uk/queries.html

 

[sadist]

Thanks for your reply Alan,

I need to clarify a little further. The two linked tables are 'Order Header' which contains order number, order date, delivery date, supplier details, po gross, nett, tax etc and the 'Line Detail' file contains order number, stock code, quantity ordered, unit of measure,unit price, etc. the only common field between the two tables is 'Order Number'. I have the two tables in the query joined by order number (the only common field between the two files) so my need is for a datasheet showing each stock code only once with its most recent unit price based on the last delivery date for that product code. All product codes have multiple entries over the last couple of years. I hope this clarifies a little better the results I'm trying to achieve.

Again many thanks

 

[sadist]

Update

I found this article which is the solution I was looking for so again thank you for your help

techonthenet.com/access/queries/max_date.php

Dermot