I have a query that is attempting to do various calculations to later be use to append another table.
I want to allocate period numbers. The original records have a start_date and I have another table GLPERIODS that has start_date and End_date and period number.
If the start_date of the original record falls within the start_date and End_Date of the GLPeriods table I want to allocate that period number. I can't use a join (as far as I know).
Currently the query returns too many queries. I need help.
I have the following query:
SELECT IbmfcstUpload.Item, IbmfcstUpload.Descr, IbmfcstUpload.Brand, IbmfcstUpload.PGCode, IbmfcstUpload.PromoGroup, IbmfcstUpload.Packsize, IbmfcstUpload.State, Sum(IbmfcstUpload.Cases) AS SumOfCases, "IBM" AS LD, GLPERIODS.PERIOD_YEAR, GLPERIODS.PERIOD_NAME, GLPERIODS.PERIOD_NUM, IbmfcstUpload.Account, "GROCERY" AS Channel, Sum([IbmfcstUpload]![Cases]*[Pricing]![GROCERY]) AS AMT
FROM GLPERIODS, IbmfcstUpload INNER JOIN Pricing ON IbmfcstUpload.PGCode = Pricing.PGCODE
WHERE (((IbmfcstUpload.Account) Not Like "Fal*" And (IbmfcstUpload.Account) Not Like "Frank*" And (IbmfcstUpload.Account) Not Like "David*" And (IbmfcstUpload.Account)<>"other-G" And (IbmfcstUpload.Account) Not Like "independen*" And (IbmfcstUpload.Account) Not Like "Impul*" And (IbmfcstUpload.Account) Not Like "Conv*"
AND ((IbmfcstUpload.Start_Date) Between [GLPERIODS]![START_DATE] And [GLPERIODS]![END_DATE]))
GROUP BY IbmfcstUpload.Item, IbmfcstUpload.Descr, IbmfcstUpload.Brand, IbmfcstUpload.PGCode, IbmfcstUpload.PromoGroup, IbmfcstUpload.Packsize, IbmfcstUpload.State, "IBM", GLPERIODS.PERIOD_YEAR, GLPERIODS.PERIOD_NAME, GLPERIODS.PERIOD_NUM, IbmfcstUpload.Account, "GROCERY";
I want to allocate period numbers. The original records have a start_date and I have another table GLPERIODS that has start_date and End_date and period number.
If the start_date of the original record falls within the start_date and End_Date of the GLPeriods table I want to allocate that period number. I can't use a join (as far as I know).
Currently the query returns too many queries. I need help.
I have the following query:
SELECT IbmfcstUpload.Item, IbmfcstUpload.Descr, IbmfcstUpload.Brand, IbmfcstUpload.PGCode, IbmfcstUpload.PromoGroup, IbmfcstUpload.Packsize, IbmfcstUpload.State, Sum(IbmfcstUpload.Cases) AS SumOfCases, "IBM" AS LD, GLPERIODS.PERIOD_YEAR, GLPERIODS.PERIOD_NAME, GLPERIODS.PERIOD_NUM, IbmfcstUpload.Account, "GROCERY" AS Channel, Sum([IbmfcstUpload]![Cases]*[Pricing]![GROCERY]) AS AMT
FROM GLPERIODS, IbmfcstUpload INNER JOIN Pricing ON IbmfcstUpload.PGCode = Pricing.PGCODE
WHERE (((IbmfcstUpload.Account) Not Like "Fal*" And (IbmfcstUpload.Account) Not Like "Frank*" And (IbmfcstUpload.Account) Not Like "David*" And (IbmfcstUpload.Account)<>"other-G" And (IbmfcstUpload.Account) Not Like "independen*" And (IbmfcstUpload.Account) Not Like "Impul*" And (IbmfcstUpload.Account) Not Like "Conv*"
AND ((IbmfcstUpload.Start_Date) Between [GLPERIODS]![START_DATE] And [GLPERIODS]![END_DATE]))GROUP BY IbmfcstUpload.Item, IbmfcstUpload.Descr, IbmfcstUpload.Brand, IbmfcstUpload.PGCode, IbmfcstUpload.PromoGroup, IbmfcstUpload.Packsize, IbmfcstUpload.State, "IBM", GLPERIODS.PERIOD_YEAR, GLPERIODS.PERIOD_NAME, GLPERIODS.PERIOD_NUM, IbmfcstUpload.Account, "GROCERY";