Access Query comparing 2 sets of data with 2 sets of dates

[lynng]

I have a database that calculates the yield of product produced. We query it to find the yield based on gross and net weight for different products each month (or week). It is a select query that calculates the Yield in the query. We need to design a new query/report that compares this years data for each month per product to last years data for the same month (accounting months, 4-4-5). I used a make table query to store last years data results, but now the problem is averaging the yields by product and comparing to this years. Also, if a product was not produced last year in the month in question, they want it to fall back to whatever previous month it was produced and use that value. Don't know where to go from here as I ususally calculate the average yield in the report.
EXAMPLE. product 5720 for Jan 2001 had a 87% avg yield (from 3 data values), the same product for Jan 2002 had a 92% avg yield (from 7 data values), calculating that average first then comparing the two will be +5% this year. Or 5720 for Jan 2001 was 0 so fall back to Dec 2000 values and compare to this years Jan values. Any suggestions where to start?

 

[AnalystDBA]

Lynn,

I do this sort of stuff often. I find it easier to run reports like this using VBA so if you would like some help drop me a line at alc_technology@optusnet.com.au
It might be worth emailing me your .mdb file. That way I can see it in more detail.

Cheers
Andrew.