Here goes:
Tier 1, Query 1 - SumofProd1, DailyProd2, DailyProd3 -- saved as _Group_Products:
Code:
SELECT
ptsProductionPt.SiteID,
sumDailyProduction.docDate AS [Date],
Sum(sumDailyProduction.Prod1) AS SumOfProd1,
Sum(sumDailyProduction.Prod2) AS DailyProd2,
Sum(sumDailyProduction.Prod3) AS DailyProd3
FROM ptsProductionPt INNER JOIN sumDailyProduction ON ptsProductionPt.ProductionPtID = sumDailyProduction.EntityID
GROUP BY
ptsProductionPt.SiteID,
sumDailyProduction.docDate;
Tier 1, Query 2 - Buy -- saved as _Group_Buy:
Code:
SELECT
ptsMeterPt.SiteID,
CDate(Int([docMeterReadings].[docDate])) AS [Date],
Sum(docMeterReadings.Volume) AS Buy
FROM docMeterReadings INNER JOIN ptsMeterPt ON docMeterReadings.MeterPtID = ptsMeterPt.MeterPtID
WHERE (((ptsMeterPt.Description)="Buy"))
GROUP BY
ptsMeterPt.SiteID,
CDate(Int([docMeterReadings].[docDate]));
Tier 1, Query 3 - ckMtr -- saved as _Group_CkMtr:
Code:
SELECT
ptsMeterPt.SiteID,
CDate(Int([docMeterReadings].[docDate])) AS [Date],
Sum(docMeterReadings.Volume) AS CkMtr
FROM docMeterReadings INNER JOIN ptsMeterPt ON docMeterReadings.MeterPtID = ptsMeterPt.MeterPtID
WHERE (((ptsMeterPt.Description)="Sales Meter"))
GROUP BY ptsMeterPt.SiteID,
CDate(Int([docMeterReadings].[docDate]));
Tier 1, Query 4 - P1, P2, Notes -- saved as _Group_P1_P2_Notes:
Code:
SELECT
ptsProductionPt.SiteID,
CDate(Int([docDate])) AS [Date],
Avg(Pressures.PRESS1) AS AvgOfPRESS1,
Avg(Pressures.PRESS2) AS AvgOfPRESS2,
Last(Pressures.Notes) AS LastOfNotes,
Last(Pressures.InputByID) AS LastOfInputByID
FROM ptsProductionPt INNER JOIN Pressures ON ptsProductionPt.ProductionPtID = Pressures.ProductionPtID
GROUP BY
ptsProductionPt.SiteID,
CDate(Int([docDate]))
ORDER BY CDate(Int([docDate]));
Tier 2: Query combining Tier 1's -- saved as tblDailyReadings:
Code:
SELECT
fd_sysSite.SiteID AS Item_Key,
[_Group_Products].Date AS ReadingDate,
[_Group_P1_P2_Notes].AvgOfPRESS1 AS PRESS1,
[_Group_P1_P2_Notes].AvgOfPRESS2 AS PRESS2,
[_Group_CkMtr].CkMtr, [_Group_Buy].Buy,
[_Group_Products].SumOfProd1 AS SalesProd1,
[_Group_Products].DailyProd2,
[_Group_Products].DailyProd3
FROM fd_sysSite INNER JOIN (((((((((_Group_Products
LEFT JOIN _Group_P1_P2_Notes ON ([_Group_Products].SiteID = [_Group_P1_P2_Notes].SiteID) AND ([_Group_Products].Date = [_Group_P1_P2_Notes].Date))
LEFT JOIN _Group_Buy ON ([_Group_Products].SiteID = [_Group_Buy].SiteID) AND ([_Group_Products].Date = [_Group_Buy].Date))
LEFT JOIN _Group_CkMtr ON ([_Group_Products].SiteID = [_Group_CkMtr].SiteID) AND ([_Group_Products].Date = [_Group_CkMtr].Date)) ON fd_sysSite.SiteID = [_Group_Products].SiteID;
Tier 3: Objective query, to Tier 2 (tblDailyReadings):
Code:
SELECT
[_Group_Norm_Query].[TimeDay],
Sum([_Group_Norm_Query].SalesProd1) AS SumOfSalesProd1,
Sum([_Group_Norm_Query].DailyProd2) AS SumOfDailyProd2,
Sum([_Group_Norm_Query].DailyProd3) AS SumOfDailyProd3,
Count([_Group_Norm_Query].Item_Key) AS CountOfItem_Key
FROM (
SELECT
tblDailyReadings.Item_Key,
tblDailyReadings.ReadingDate,
tblDailyReadings.SalesProd1,
tblDailyReadings.DailyProd2,
tblDailyReadings.DailyProd3,
(SELECT COUNT(Table1A.ReadingDate) FROM [tblDailyReadings] AS Table1A
WHERE [Table1A].[ReadingDate] <= [tblDailyReadings].[ReadingDate]
AND [Table1A].[Item_Key]=[tblDailyReadings].[Item_Key]) AS [TimeDay]
FROM tblProperties INNER JOIN tblDailyReadings ON tblProperties.WH_IDX = tblDailyReadings.Item_Key
WHERE (((tblProperties.PROP_GROUP) = 'My_Group_Selection'))
ORDER BY tblDailyReadings.ReadingDate) AS [_Group_Norm_Query]
GROUP BY [_Group_Norm_Query].[TimeDay];
In my example, the Tier 1's don't have parameters- but for my final query, I would do an inner JOIN with tblProperties for each of them and make PROP_GROUP require a value. This way, Tier 3 query on tblDailyReadings doesn't take forever to execute.
But since all Tier 1's and Tier 2's are in Access, I need to figure out how to make this one gigantic query (if that's the answer). You can see how it could get out of hand easily... I even trimmed this down for illustrative purposes. The specific query actually has 10 "Tier 1" queries, I provided 4.
Thanks,