Hi,
I need a single SQL query string so that I can use it as a Graph's RowSource in my VBA code.
My SQL query combines two sub-Queries: 1) data 2) SQL-calculations performed on that data (Please don't judge me for doing it this way
).
Queries 1 and 2 work separately, if I refer to Query1 as an object inside Query2, but not when I use Query1 as SQL statement inside Query2.
Please see attached DB example (Query2 works, Query3 fails).
Query1
Query2 - Pulling Query1 as object
Query3 - using Query1 as SQL statement
What fails in Query3 is assigning Alias1 to the Query1 [QueryData AS Alias1] so that data and calculations (SUM, StDev etc) can be put out as output together from Query2.
As you can see in sample DB, assigning Alias1 [Query1 AS Alias1] in Query2 works out.
Anyone would have advice on how to accomplish the above through pure SQL statement? I'd rather avoid saving Queries in the database, as I'd want to extensively modify the SQL query in VBA.
Or, alternatively, anyone know of a way to execute the above queries sequentially in VBA (for example ending up with a Recordset object) and a way to apply the Recordset object to an AccessGraph Object?
Thank you.
I need a single SQL query string so that I can use it as a Graph's RowSource in my VBA code.
My SQL query combines two sub-Queries: 1) data 2) SQL-calculations performed on that data (Please don't judge me for doing it this way
).Queries 1 and 2 work separately, if I refer to Query1 as an object inside Query2, but not when I use Query1 as SQL statement inside Query2.
Please see attached DB example (Query2 works, Query3 fails).
Query1
Code:
SELECT
tbl_Data1.rec_ID,
tbl_Data1.rec_ItemNo,
tbl_Data1.rec_Value1,
tbl_Data1.rec_Value2
FROM tbl_Data1
WHERE
(tbl_Data1.rec_ID<
(SELECT tbl_Data1.rec_ID
FROM tbl_Data1
WHERE (((tbl_Data1.rec_ItemNo)="1045/16"))
ORDER BY tbl_Data1.rec_ID)
)
ORDER BY tbl_Data1.rec_ID;Query2 - Pulling Query1 as object
Code:
SELECT
Alias1.rec_ItemNo,
Alias1.rec_Value1,
Alias1.rec_Value2,
Sum(Query1.rec_Value1) AS Sum_rec_Value1,
StDev(Query1.rec_Value2) AS StDev_rec_Value2
FROM Query1,
Query1 AS Alias1
GROUP BY Alias1.rec_ID, Alias1.rec_ItemNo, Alias1.rec_Value1, Alias1.rec_Value2
ORDER BY Alias1.rec_ID;Query3 - using Query1 as SQL statement
Code:
SELECT
Alias1.rec_ItemNo,
Alias1.rec_Value1,
Alias1.rec_Value2,
Sum(QueryData.rec_Value1) AS Sum_rec_Value1,
StDev(QueryData.rec_Value2) AS StDev_rec_Value2
FROM
(SELECT tbl_Data1.rec_ID, tbl_Data1.rec_ItemNo, tbl_Data1.rec_Value1, tbl_Data1.rec_Value2
FROM tbl_Data1
WHERE (tbl_Data1.rec_ID<
(SELECT tbl_Data1.rec_ID
FROM tbl_Data1
WHERE (((tbl_Data1.rec_ItemNo)="1045/16"))
ORDER BY tbl_Data1.rec_ID)
)
ORDER BY tbl_Data1.rec_ID) AS QueryData,
QueryData AS Alias1
GROUP BY Alias1.rec_ID, Alias1.rec_ItemNo, Alias1.rec_Value1, Alias1.rec_Value2
ORDER BY Alias1.rec_ID;What fails in Query3 is assigning Alias1 to the Query1 [QueryData AS Alias1] so that data and calculations (SUM, StDev etc) can be put out as output together from Query2.
As you can see in sample DB, assigning Alias1 [Query1 AS Alias1] in Query2 works out.
Anyone would have advice on how to accomplish the above through pure SQL statement? I'd rather avoid saving Queries in the database, as I'd want to extensively modify the SQL query in VBA.
Or, alternatively, anyone know of a way to execute the above queries sequentially in VBA (for example ending up with a Recordset object) and a way to apply the Recordset object to an AccessGraph Object?
Thank you.
Last edited:
