Summing Mth-Yr Range From One Table & Linking To Another Table - Help!

[Dave Gibson]

Hi,

I wonder if anyone could please assist me with setting up a query to
perform a task I require. I have outlined the requirements below.

I have a table called SALES, in this table there are 3 fields,
[SPROCKETS], [MONTH-YEAR], [SOLD]

I have another table called SUPPLIERS, in this table there are 3
fields, [SPROCKETS], [MONTH-YEAR], [SupplierCount]

What I wish to achieve is to take a [MONTH-YEAR] record from the SALES
table the obtain a summed value for a specific [MONTH-YEAR] range from
the SUPPLIERS table for the [SupplierCount] field.

I hope that is explained properly, it can be a bit confusing. I have
included an actual sample below.

Using [Sprockets][Oct-05][56] as an example from the SALES
table . I wish to get the summed value for [SupplierCount] for the
range of 15months to 18 months prior to [Oct-05] from the SUPPLIERS
table.

So, if [Jul-04] = 5, [[Jun-04] = 3, [[May-05] = 6 in the SUPPLIERS
table I would get:

[Sprockets], [Oct-05], [56], [14]

in the query result, the first 3 fields coming from the SALES table
and the 4th field with the value of[14] coming from the summed value
of the date range from the SUPPLIERS table.

I may also wish to use a greater range for the summed field other that
the 3 month range in the above example. However, the range to be
summed from the SUPPLIERS table will always be chronologically
historic if that makes any difference, ie, it is always before the
[Month-Year] value coming from the SALES table

I have tried making a "Key" on the lines of
Sprockets]&"-"&[Month-Year] to link the data etc but its not working.

I am not much good at SQL so I have been attempting it all via the
query grid and probably got so confused now that I cannot see things
clearly at all

Any solutions gratefully received and I hope the above is OK to
understand.

Regards
Dave

 

[Guest]

Two ways --
SELECT SALES1.SPROCKETS, SALES1.[MONTH-YEAR], SALES1.SOLD,
Sum(SUPPLIERS.SupplierCount) AS SumOfSupplierCount
FROM SALES1 LEFT JOIN SUPPLIERS ON SALES1.SPROCKETS = SUPPLIERS.SPROCKETS
WHERE (((SUPPLIERS.[MONTH-YEAR]) Between
DateAdd("m",-18,[SALES1].[MONTH-YEAR]) And
DateAdd("m",-15,[SALES1].[MONTH-YEAR])))
GROUP BY SALES1.SPROCKETS, SALES1.[MONTH-YEAR], SALES1.SOLD;

SELECT SALES1.SPROCKETS, SALES1.[MONTH-YEAR], SALES1.SOLD,
Sum(SUPPLIERS.SupplierCount) AS SumOfSupplierCount
FROM SALES1 LEFT JOIN SUPPLIERS ON SALES1.SPROCKETS = SUPPLIERS.SPROCKETS
WHERE (((SUPPLIERS.[MONTH-YEAR]) Between DateAdd("m",-[Enter # months
prior],[SALES1].[MONTH-YEAR]) And DateAdd("m",-([Enter # months prior]-[Enter
# months span]),[SALES1].[MONTH-YEAR])))
GROUP BY SALES1.SPROCKETS, SALES1.[MONTH-YEAR], SALES1.SOLD;