SQL Query Help :)

[Coolboy55]

Ok I think this is a tough one. I have two tables: an Inventory table and an Inventory History table. The Inventory table has the current Quantity of each Product. The Inventory History table has the Quantity of each Product whenever it changed, along with the Date when it changed.

I'm trying to create a query that takes a date from the user as a parameter and gives the Quantity of each Product on that day from the Inventory History table. If there is no record for that day, then it gives the most recent one before the inputted date.

The net effect is that the query gives the actual inventory count of each product on the day the user specifies.

Is this hard? :(

[nico5038]

No, just use a WHERE clause to check for the max datefield less or equal the entered value like:

WHERE DateField = Dmax("datefield","tblYours","Datefield <=#" & [Datefield] & "#")

Getting the idea ?

Nic;o)

[nico5038]

One additional remark, when you have multiple stocks on the same date, then multiple rows can be returned.
To overcome that add a SORT BY on a field that's relevant and use SELECT TOP 1 to get just one. It's an exception, but just in case.

Nic;o)