Query to find Max Date Record and other fields (1 Viewer)

[amp]

I have the following tables:

Product: (parent table of products)
Product ID
Product Description
...

Location: (child table of locations where products have been moved and dates)
Product ID
Move Date
Location
...

The product ID field is a left outer join between both tables (show all Product ID in table Product and only those Product ID in table Location where the fields are equal).

I would like a single query to return, for every Product ID in table Product, the Max of Move Date in table Location and the Location field in that record.

I can get the max dates, but I can't get it to show the location unless it shows all the locations for that Product ID.

Any help would be appreciated.

 

[pbaldy]

Does this work for you?

http://www.baldyweb.com/LastValue.htm

 

[amp]

Does this work for you?

http://www.baldyweb.com/LastValue.htm

The problem is that not every move in the Location table has a move date. Previously, moves were recorded, but not dated. They also were not always entered in sequential order.

With this query, when all the moves for a Product ID have no Move Date, the query will return a blank Location (even when there are records with a Location).

I can't assign an arbitrary date to the missing Move Dates and none of this data is always entered sequentially (so I can't track it using the autonumber field).

Any thoughts?

 

[pbaldy]

I'm not sure there's any way of achieving your stated goal if some records don't contain a date or some other way of determining the most recent move. If you can't do it by eye, examining the data, then Access can't do it either.