Smart brain needed: Show latest record monthly from time stamp onwards

[Acid1979]

Dear Community,


I created a table "Product" (simplified) with the following fields: "ProductName", "Amount", "lastUpdate".

I was able to build a query showing me always the latest Record for each Product (based on the field "lastUpdate").

Now I would like to create a query showing me the last valid record for each month / year. The rule should be: always take the latest record available up until end of the specific month. If there are newer records, which have an update date after end of the month, then don't use this record, but the previous record for this product.

Example:

The following table

ProductName.......Amount.......lastUpdate
P1..........................5...............20-Feb-2015
P2........................10...............10-Mar-2015
P1........................10...............01-May-2015

Should bring the following result :

Month........ProductName........Amount
January.......P1.........................0
February.....P1.........................5
March........P1..........................5
April...........P1..........................5
May...........P1........................10
June...........P1.........................10
... ... ...
January......P2..........................0
February....P2..........................0
March........P2........................10
April..........P2.........................10
...


I was thinking about creating a second table with called "Period" (simplified) with the fields "Month" and "LastDateOfMonth"

And then building a query, linking both this tables with a were condition.
In a not proper SQL syntax I am thinking about something like:
Select Max("LastUpdate") and "ProductName" and "Amount" from table "Product" where "lastUpdate" <= "Period"."LastDateOfMonth" for each record in table "Period".

Please excuse this really bad syntax, as I normal use the query build to build my queries.

Any ideas on how to build this query, or any other idea on how to solve this? Your help would be very much appreciated.

I am using Access 2007

 

[Acid1979]

Found a solution but maybe you still have some ideas on how to do it better or more sophisticated:

1. I created a Query "AllCombinations" based on the Table "Products" the table "Period" (multiplying out all combinations of both tables).
2. I created a Query "MaxAllCombinations" based on the Query "AllCombinations" grouping by "Product", "Month" and Max of "LastUpdate". Without "Amount"
3. I created a Query "Final" based on the previous two queries "AllCombinations" and "MaxAllCombinations" creating relations between "Product", "Month" and "Last Update", now also showing the field "Amount"