How to list only material that has 12 months data in any financial year

[Ken]

Hi,

This is what needs to be done. Need to list all material that has full 12 months data in any financial year. Financial year is from Mar to Apr (e.g. 1st Mar 2010 to 30th Apr 2011).
Any hints/idea will help

Tried using non equi join and not working gives error of aggregate function not work in where clause in Access 2007

e.g.
1) product: Material_1 purchased on June 10 2010.
2) Transaction: for Material_1 started from June 20 2010.
3) Criteria: To list Material_1 from 1st Mar 2011 to 30th Apr 2012 as it has 12 months data from 1st March 2011 to 30th Apr 2012


Table A: tblMaterial
Item_Number, ItemName, PurchDate
09, Material_1, Jun 10 2010


Table B: tblMaterial_Tran
Tran Date, Item_Number, Qty_Purch
Jun 20 2010, 09 , 20



select tblA.Item_Number, tblA.ItemName, tblB.trand_Date, sum(tblB.qty_Purch)
from tblMaterial tblA
inner join tblMaterial_Tran tblB
join on tblA.item_number = tblb.ItemNumber
where tblB.tran_date between (FinYR_From and FinYr_To)
and count(tblB.Tran_Date) >=12


Please help.

Thanks,

TechIT

 

[SkipVought]

Hi,

Do you mean that you want a list of material that has at least one transaction IN each month of the financial year?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Ken]

Hi SkipVought,

Material will be listed that has 12 months transaction in a Financial Year. Basically there will be a count for the months record. If record count is 11 then it won't be listed.

e.g. A material purchased in June 2010 will have 6 months transaction data for Financial Year Mar 2010 to Apr 2011. Thus, through query it will appear in next Financial Year of Mar 2011 to Apr 2012.

TechIT

 

[SkipVought]

So it is NOT one transaction IN each of the 12 months, but simply 12 transactions within the 12 month period?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BigRed1212]

So, for fiscal year April 2010 to March 2011 if (March 2011 - PurchDate) >= 12 then it should be in the report?

If so:

...
...
where (#3/31/2011# - PurchDate) >= 12