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.
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.