3 Tier Queries - Consolidate to One?

[bs0d]

I have what I can best describe as 3 tiers of queries for a project due to data existing in multiple tables:



Tier 1 = Queries that return individual fields
Tier 2 = A query of all Tier 1 queries to combine into one place. Sort of a pseudo-table.
Tier 3 = My objective query. A query to the Tier 2 query (yes, a query to a query that is made of queries).

Right now, I built all tier 1 queries and the tier 2 query directly in Access. The tier 1 queries require a parameter. In VBA, I create the Tier 3 query (to Tier 2) as if it's a table, pass along the parameter, and everything works great.

The problem is, the field that requires a parameter can changed based on prior user selections in my VBA program. So instead of hard-coding the Tier 1 & Tier 2 queries in Access, which could get messy fast, I need to learn how to handle everything on the VBA side.

I imagine this may be just a gigantic query? What I need someone's help on, is that I'm not well polished on structuring complex SQL statements. So how would a query look that had for example, 5 'field' Tier 1 queries, a Tier 2 query, and Tier 3 query -- all in one? I'm not trying to union multiple tables, but think of Tier 1 & Tier 2 as the structuring of a table itself. The database I'm dealing with stores things sort of everywhere, so the Tier 1 queries are to fetch what I need from all the different places, and Tier 2 brings them into a single appearance of a table that is easier to retrieve my data from.

Thoughts?

[June7]

Not really sure what you are looking for but nested queries can be built in the SQL View of query builder.

Post your 3 queries and will try to show as one nested SQL statement.

[bs0d]

Here goes:

Tier 1, Query 1 - SumofProd1, DailyProd2, DailyProd3 -- saved as _Group_Products:

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:

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:

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:

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:

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):

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,

[June7]

Yes, this would be one nasty, long nested query. Maybe even too long - there are limits. And I am not sure can be nested because Tier 2 query joins the Tier 1 queries to each other. _Group_Products is joined to each of the other 3 Tier 1 queries. That might mean repeating the _Group_Products SQL statement for each join. Way too complicated for me.

Examples of simple nested queries: http://allenbrowne.com/subquery-01.html

[bs0d]

Yes, this would be one nasty, long nested query. Maybe even too long - there are limits. And I am not sure can be nested because Tier 2 query joins the Tier 1 queries to each other. _Group_Products is joined to each of the other 3 Tier 1 queries. That might mean repeating the _Group_Products SQL statement for each join. Way too complicated for me.

Examples of simple nested queries: http://allenbrowne.com/subquery-01.html

Thanks for the link. I agree that nesting all into one huge query would be quite complicated. Maybe there's a better way. at the end of the day, I just need the Tier 1 queries to be flexible in the way of a parameter. As I write this I'm wondering, in Access when limiting by a parameter, maybe I can tie each of them to a named query -instead of a fixed value? Maybe then I could change the contents of the named query in VBA before executing the Tier 3 query? This way all Tier 1's are limited as needed before querying the Tier 2 table. Again, adding a parameter to all Tier 1's really makes what complicated mess I have up to this point usable. To be clear though, it works great- and fast. So any creative ideas on dynamic parameters on the Access side?

[accedeholdings]

I admire you threadstarter for attempting nested queries it is hard and really confusing for me.

[June7]

If you want filter criteria to be dynamic, use a form for input of parameter and query can reference controls on form.

I avoid and seldom have situation that requires dynamic parameters in query. I use VBA to construct filter criteria and pass to form or report when opening.

[bs0d]

If you want filter criteria to be dynamic, use a form for input of parameter and query can reference controls on form.

I avoid and seldom have situation that requires dynamic parameters in query. I use VBA to construct filter criteria and pass to form or report when opening.

I'm trying to figure out if even a dynamic parameter setup would restrict or complicate multi-user requests, each possibly with their own criteria. One thing I've done as a workaround up to this point is to run queries to pull the Tier 1 data, and write it to a local table (effectively Tier 2). So then querying the local DB becomes just as fast as Tier 1's w/ parameter through Tier 2 & 3. The problem with this is, I have to run queries to look if prior daily data has changed, if so- delete it, then add all the new data from that point forward. So it's not as simple as adding the new day's worth of readings. But maybe there's a way to automate that process at the start of the program, then always deal with the copy of the data, vs. the nightmare gauntlet of queries. Thoughts here? Do you get the idea of the problem I have, and might you have any ideas?


Another question down the avenue of the monster query, can I create simple Tier 1 queries in access as named queries, then append SQL to them as needed in VBA (the criteria)? This may cut down on the confusion/complexity of the query?

For example, take _Group_Products as shown (no parameters) and store it in the DB:

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;

...maybe something like this:

[_Group_Products] = [_Group_Products] & "Inner Join Properties on x xxxx x WHERE [field] = [value] ORDER BY xx xx x "

Or maybe theres a way to just have the Tier2 & Tier3 query as one in VBA, instead of the thought of bringing everything into VBA -- and instead of applying parameters to each Tier 1, require a parameter at Tier 2?

[June7]

Why look if prior daily data has changed, just purge the local table of all records and populate with the new set of current data.

Can refer to SELECT query objects in VBA just as can tables to open a recordset.

The fields used to filter Tier 1 queries are not referenced in Tier 2 so how could filter be done at Tier 2?

Perhaps Tiers 2 and 3 could be nested query - again, too complicated for me.

[bs0d]

With hundreds of thousands of rows, I figured it would be faster to just do the 1 to 3 or 5 days worth.

No stab at combining the Tier 2 & Tier 3 example?

[June7]

How I create nested queries described in https://www.accessforums.net/queries...ery-50472.html

I will let you attempt with yours.

[bs0d]

Here's first crack:

SELECT 
       Q1.TimeDay, 
   Sum(Q1.SalesProd1) AS SumOfSalesProd1, 
   Sum(Q1.DailyProd2) AS SumOfDailyProd2, 
   Sum(Q1.DailyProd3) AS SumOfDailyProd3, 
 Count(Q1.Item_KEY)   AS CountOfItem_KEY
FROM 
        (SELECT 
         Q2.Item_KEY, 
         Q2.ReadingDate, 
         Q2.SalesProd1,
         Q2.DailyProd2, 
         Q2.DailyProd3,
               (SELECT 
               COUNT(Q2A.ReadingDate) 
               FROM [Q2] AS Q2A  
               WHERE [Q2A].[ReadingDate]<=[Q2].[ReadingDate] AND [Q2A].[Item_KEY]=[Q2].[Item_KEY]) AS TimeDay
                                 (SELECT
                                  fd_sysSite.SiteID AS Item_KEY,
                                  [_Group_Products].Date AS ReadingDate, 
                                  [_Group_Products].SumOfProd1 AS SalesProd1,
                                  [_Group_Products].DailyProd2, 
                                  [_Group_Products].DailyProd3
                                  FROM fd_sysSite
                                  INNER JOIN [_Group_Products] ON fd_sysSite.SiteID = [_Group_Products].SiteID) AS Q2
         FROM tblProperties INNER JOIN Q2 ON tblProperties.WH_IDX = Q2.Item_KEY 
         WHERE (((tblProperties.PROP_GROUP)="MY_GROUP_SELECTION") AND ((tblProperties.PROP_AREA)="MY_AREA")) ORDER BY Q2.ReadingDate) AS Q1
GROUP BY Q1.TimeDay;

The error I get is very vague, so it's hard to know exactly which part is tripping it up.
Error:

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

[June7]

Date is a reserved word. However, the field reference is prefixed with query name so that should be okay. But enclose in [] anyway just to see what happens:

[_Group_Products].[Date]

Should avoid reserved words as names.

Probably should give Count(Q2A.ReadingDate) an alias name.

I see 4 SELECT statements but only 2 alias query names.

Making my brain curl into fetal position!!

[bs0d]

Since I'm not much of a query guy, this could be completely wrong. But as I understand it, it would be:

Four Select statements
• Primary selection (no alias required)
• Q1, which acquires it's data from Q2
• Q2, which acquires it's data from [_Group_Products]
• TimeDay, which is a field within Q2 that acquires it's data from the Q2.ReadingDate field

I added [ and ] around .Date -- That source field I'm not in control of, and is from a table in a database from a software we use. But I still get the same error.

[Rawb]

Ahh yes, Subqueries (or as I call them, FrankenQueries)!

I think your situation is complex enough that without seeing everything in action, without knowing your exact Table design, and without knowing more about what, specifically, you're trying to do, we won't be able to help you very much.

However, I did go through your last post (the one with the Subqueries nested together), and can tell you that things start going wrong at the line ending "AS TimeDay". At the very least, there should be a comma there as the next line launches into a new Subquery.

Also keep in mind that, unless used in the FROM clause, a Subquery can only return a SINGLE value.

If you want to continue working towards a single query solution, I'd recommend reading up on them from allenbrowne.com (here and here). However, since you're working with so many Queries, you may want to look into using VBA to modify your "Tier 1" and "Tier 2" Queries using the QueryDefs Collection.