Creating Link criteria with different dates

[marksham]

This title probably doesn't describe my problem very well but it's the best I can come up with.

My fiscal year Starts a little before Jan1 each year, but it's an arbitrary start date, there's no definable logic to it. So I made a maintenance table to store the Start Date of the Fiscal Year, and the numerical value of that year. e.g.
12/27/2005 2006
12/23/2004 2005

In any given fiscal year each product I produce is supposed to meet a manufacturing standard, so I have another table that looks like:
ProductID; Year; StandardValue
1, 2006, 15000
1, 2005, 14000
2, 2006, 70
2, 2005, 85

So for each production run I am supposed to compare it to the current applicable standard to see how it matches up. On a record by record basis this is handy enough. Using a Select Max(Year) where StartDate > MyDate

My problem is I have a report that returns values over a 6 month period, so if my fiscal start date is inside the period (which it is 50% of the time), I have to compare that particular record to the previous years standard, because that's when it was manufactured. A simple example:
ProductID, DateofProductio n, Quantity
1, 12/26/2006, 14500
1, 12/30/2006, 15500

Using all of the above examples, what I want to get is:
ProductID, Year, Quantity, Standard
1, 2005, 14500, 14000
1, 2006, 15500, 15000

I hope this makes sense, logically it's easy enough to figure out, but I'm too dumb to make the leap in Access.

Any help would be greatly appreciated.

[PEB]

Hi,

The first tip that it comes to my mind is to put Start and and date of your Financial Year...

It will be easier to create your query without complications!

:)

Have a nice day!