Update multiple fields in a table using multiple select subqueries

[EricRex]

After a bunch of trial and error, I've found a combination of queries that work, and a mystery that doesn't, but seems like it should:

This query creates the Moving Averages table, RiskZ_SMAs:
INSERT INTO RiskZ_SMAs ( BarDate, SMA30, SMA10, SMA30_10 )
SELECT b.BarDate, Round((SELECT AVG(close) from VIX_DailyHist WHERE ID BETWEEN b.ID and b.ID-29),6) AS SMA30, Round((SELECT AVG(close) from VIX_DailyHist WHERE ID BETWEEN b.ID and b.ID-9),6) AS SMA10, Round(SMA30/SMA10,6) AS SMA30_10


FROM VIX_DailyHist AS b;

ID	BarDate	SMA30	SMA10	SMA30_10
2801	11/18/2022	24.182727	24.146	1.001521
2802	11/21/2022	24.030833	23.947	1.003501
2803	11/22/2022	23.82	23.522	1.012669
2804	11/23/2022	23.572143	22.948	1.027198
2805	11/24/2022	23.362	22.637	1.032027
2806	11/25/2022	23.183125	22.435	1.033346
2807	11/28/2022	23.125882	22.283	1.037826

This second query creates the table of all of the Moving Averages plus the calculated values from the Moving Averages table, called RiskZ_All:
SELECT b.ID, b.BarDate, b.SMA30, b.SMA10, b.SMA30_10, (SELECT Round(AVG(SMA30_10), 6) FROM RiskZ_SMAs WHERE ID BETWEEN b.ID AND b.ID-299) AS AVGSMA30_10, (SELECT Round(STDEVP(SMA30_10), 6) FROM RiskZ_SMAs WHERE ID BETWEEN b.ID AND b.ID-299) AS StDevMA30_10, Round((SMA30_10-AVGSMA30_10)/StDevMA30_10,6) AS RiskZ INTO RiskZ_All
FROM RiskZ_SMAs AS b;

ID	BarDate	SMA30	SMA10	SMA30_10	AVGSMA30_10	StDevMA30_10	RiskZ
2801	11/18/2022	24.182727	24.146	1.001521	1.000138	0.000437	3.16476
2802	11/21/2022	24.030833	23.947	1.003501	1.000419	0.001019	3.024534
2803	11/22/2022	23.82	23.522	1.012669	1.001361	0.003408	3.318075
2804	11/23/2022	23.572143	22.948	1.027198	1.003206	0.00742	3.233423
2805	11/24/2022	23.362	22.637	1.032027	1.005128	0.010153	2.649365
2806	11/25/2022	23.183125	22.435	1.033346	1.006891	0.01197	2.210109
2807	11/28/2022	23.125882	22.283	1.037826	1.008711	0.013706	2.124252

The mystery is when I use essentially the first query as the data source for the second query to try to collect all of the data in one place, the second set of calculations are messed up.
The third query, named qryRiskZ_SMA_v4 is:
SELECT b.ID, b.BarDate, Round((SELECT AVG(close) from VIX_DailyHist WHERE ID BETWEEN b.ID and b.ID-29), 6) AS SMA30, Round((SELECT AVG(close) from VIX_DailyHist WHERE ID BETWEEN b.ID and b.ID-9), 6) AS SMA10, Round(SMA30/SMA10, 6) AS SMA30_10
FROM VIX_DailyHist AS b;

Unsurprisingly, it generates the same data as the RiskZ_SMAs table above:

ID	BarDate	SMA30	SMA10	SMA30_10
11	11/18/2022	24.182727	24.146	1.001521
12	11/21/2022	24.030833	23.947	1.003501
13	11/22/2022	23.82	23.522	1.012669
14	11/23/2022	23.572143	22.948	1.027198
15	11/24/2022	23.362	22.637	1.032027
16	11/25/2022	23.183125	22.435	1.033346
17	11/28/2022	23.125882	22.283	1.037826

The fourth query that refers to the third:
SELECT b.ID, b.BarDate, b.SMA30, b.SMA10, b.SMA30_10, Round((SELECT AVG(SMA30_10) FROM qryRiskZ_SMA_v4 as c WHERE b.ID BETWEEN c.ID and c.ID-299), 6) AS AVGSMA30_10, Round((SELECT StDevP(SMA30_10) FROM qryRiskZ_SMA_v4 as d WHERE b.ID BETWEEN d.ID and d.ID-299), 6) AS StDevSMA30_10, Round((b.SMA30_10 - AVGSMA30_10)/StDevSMA30_10, 6) AS RiskZ
FROM qryRiskZ_SMA_v4 AS b;

Surprisingly, this query generates the same value in each row for the AVGSMA30_10 and StDevSMA30_10 fields:

ID	BarDate	SMA30	SMA10	SMA30_10	AVGSMA30_10	StDevSMA30_10	RiskZ
11	11/18/2022	24.182727	24.146	1.001521	1.013988	0.088022	-0.141635
12	11/21/2022	24.030833	23.947	1.003501	1.013988	0.088022	-0.119141
13	11/22/2022	23.82	23.522	1.012669	1.013988	0.088022	-0.014985
14	11/23/2022	23.572143	22.948	1.027198	1.013988	0.088022	0.150076
15	11/24/2022	23.362	22.637	1.032027	1.013988	0.088022	0.204937
16	11/25/2022	23.183125	22.435	1.033346	1.013988	0.088022	0.219922
17	11/28/2022	23.125882	22.283	1.037826	1.013988	0.088022	0.270819

I think the ID's in the where clauses are messed because it's acting as though they aren't even there and calculating the same average for all rows.

Cheers,
Eric

[June7]

Did you see post 16?

[CJ_London]

Not sure why you need to save this data to a table, using non standard joins is faster than subqueries so the final result can be displayed in a couple of seconds (on my machine anyway).

See attached. I have 2 years worth of FTSE data as example data and tried to imitate your sql

Note that as with your data, there are missing dates due to non trading days (at least in my data). However since you are working on ID, this should not matter providing ID's and dates are in the same order - you will be getting the last 10/30/300 trading days rather than calendar days. However I do exclude ID's <300 since your final calculation would be a nonsense for these records.

Note the queries can only be viewed in sql view due to the non standard joins. If you go to the QBE, these joins will be removed and will need to be reinstated.

query zSMA

SELECT MA.PK, MA.sDate, MA.Price, MA.MA10, MA.MA30, [MA30]/[MA10] AS MA30_10
FROM (SELECT tblFTSE.PK, tblFTSE.sDate, tblFTSE.Price, Avg(t10.Price) AS MA10, Avg(t30.Price) AS MA30 FROM (tblFTSE INNER JOIN tblFTSE AS t10 ON (t10.PK >tblFTSE.PK-10) AND (t10.PK <=tblFTSE.PK)) INNER JOIN tblFTSE AS t30 ON (t30.PK >tblFTSE.PK-30) AND (t30.PK <=tblFTSE.PK) GROUP BY tblFTSE.PK, tblFTSE.sDate, tblFTSE.Price)  AS MA
WHERE MA.PK>=300
GROUP BY MA.PK, MA.sDate, MA.Price, MA.MA10, MA.MA30, [MA30]/[MA10];

query zSMA2

SELECT zSMA.PK, zSMA.sDate, zSMA.Price, zSMA.MA10, zSMA.MA30, zSMA.MA30_10, Avg(A.MA30_10) AS avMA30_10, StDev(A.MA30_10) AS StDevOfMA30_10, ([zsma].[MA30_10]-Avg([a].[ma30_10]))/StDev([a].[MA30_10]) AS risk
FROM zSMA AS A INNER JOIN zSMA ON (A.PK <= zSMA.PK) AND (A.PK>zSMA.PK-300)
GROUP BY zSMA.PK, zSMA.sDate, zSMA.Price, zSMA.MA10, zSMA.MA30, zSMA.MA30_10;

[EricRex]

Not sure why you need to save this data to a table, using non standard joins is faster than subqueries so the final result can be displayed in a couple of seconds (on my machine anyway).

See attached. I have 2 years worth of FTSE data as example data and tried to imitate your sql

Note that as with your data, there are missing dates due to non trading days (at least in my data). However since you are working on ID, this should not matter providing ID's and dates are in the same order - you will be getting the last 10/30/300 trading days rather than calendar days. However I do exclude ID's <300 since your final calculation would be a nonsense for these records.

Note the queries can only be viewed in sql view due to the non standard joins. If you go to the QBE, these joins will be removed and will need to be reinstated.

query zSMA

SELECT MA.PK, MA.sDate, MA.Price, MA.MA10, MA.MA30, [MA30]/[MA10] AS MA30_10
FROM (SELECT tblFTSE.PK, tblFTSE.sDate, tblFTSE.Price, Avg(t10.Price) AS MA10, Avg(t30.Price) AS MA30 FROM (tblFTSE INNER JOIN tblFTSE AS t10 ON (t10.PK >tblFTSE.PK-10) AND (t10.PK <=tblFTSE.PK)) INNER JOIN tblFTSE AS t30 ON (t30.PK >tblFTSE.PK-30) AND (t30.PK <=tblFTSE.PK) GROUP BY tblFTSE.PK, tblFTSE.sDate, tblFTSE.Price)  AS MA
WHERE MA.PK>=300
GROUP BY MA.PK, MA.sDate, MA.Price, MA.MA10, MA.MA30, [MA30]/[MA10];

query zSMA2

SELECT zSMA.PK, zSMA.sDate, zSMA.Price, zSMA.MA10, zSMA.MA30, zSMA.MA30_10, Avg(A.MA30_10) AS avMA30_10, StDev(A.MA30_10) AS StDevOfMA30_10, ([zsma].[MA30_10]-Avg([a].[ma30_10]))/StDev([a].[MA30_10]) AS risk
FROM zSMA AS A INNER JOIN zSMA ON (A.PK <= zSMA.PK) AND (A.PK>zSMA.PK-300)
GROUP BY zSMA.PK, zSMA.sDate, zSMA.Price, zSMA.MA10, zSMA.MA30, zSMA.MA30_10;

Hey CJ_London,

The reason for saving the data in tables is that I don't trust my SQL skills to create the correct intermediate values so I'm checking to make sure everything makes sense before I generate the final RiskZ score. Simply having that magic number and comparing it to the actual values on my charts isn't valuable if it's not accurate because I can't figure out what's wrong in the set of calculations. In reality, I only need the RiskZ values. I do want those in a table because I want to run correlation studies with it on my actual day trading results.

And now I get to show what a SQL beginner I am. One of the reasons I haven't used joins is because I really don't understand them well. I wouldn't be able to tell you that your joins are the way to go over my SQL, nor how it works, nor that yours are non-standard joins. The only joins in my queries came from someone else telling me to use them. I've read the SQL for Dummies book for JOINs and simply don't get it.

Further, I don't know what a QBE is.

That said, I'm willing to work, study, and learn, so I'll give your queries a shot.

Sincerely, thank you for your patience.
Eric

[CJ_London]

QBE is Query Builder Environment - where you can see tables, create joins, select fields

If you had provided the data (you were asked several times), I could have worked with that. But at least this way you will learn as you adapt it to your code

simple lesson:

in the first query there is an aliased query called MA which gets the initial averages


SELECT MA.PK, MA.sDate, MA.Price, MA.MA10, MA.MA30, [MA30]/[MA10] AS MA30_10
FROM
(SELECT tblFTSE.PK, tblFTSE.sDate, tblFTSE.Price, Avg(t10.Price) AS MA10, Avg(t30.Price) AS MA30
FROM
(tblFTSE INNER JOIN tblFTSE AS t10 ON (t10.PK >tblFTSE.PK-10) AND (t10.PK <=tblFTSE.PK)) INNER JOIN tblFTSE AS t30 ON (t30.PK >tblFTSE.PK-30) AND (t30.PK <=tblFTSE.PK) GROUP BY tblFTSE.PK, tblFTSE.sDate, tblFTSE.Price) AS MA

WHERE MA.PK>=300

GROUP BY MA.PK, MA.sDate, MA.Price, MA.MA10, MA.MA30, [MA30]/[MA10];

you can copy this out and paste into a new query and save - suggest you name it MA

The bits in red are the none standard joins and will be lost if you view in the QBE as the QBE cannot represent these types of joins. The easiest way for you if you try it is to remake the joins as simple joins e.g. t10.PK =tblFTSE.PK then when done go to sql view and replace it with the non standard joins.

Assuming you have saved the query as MA, then the above sql simply becomes

SELECT MA.PK, MA.sDate, MA.Price, MA.MA10, MA.MA30, [MA30]/[MA10] AS MA30_10

FROM MA

WHERE MA.PK>=300

GROUP BY MA.PK, MA.sDate, MA.Price, MA.MA10, MA.MA30, [MA30]/[MA10];

[EricRex]

Hey CJ_London,

Thanks for the detailed description above. I will study it in detail.

And, your code above works beautifully! Thank you so much! I simply replaced table names and field names to be consistent with my existing naming convention.

To make it even more beautiful, I changed query zSMA to refer to your query zSMA 2 and it get everything in one go without an intermediate table.

For completeness, query_zSMA2:

SELECT  query_zSMA.PK, query_zSMA.sDate, query_zSMA.Price, query_zSMA.MA10, query_zSMA.MA30, query_zSMA.MA30_10,  Avg(A.MA30_10) AS avMA30_10, StDev(A.MA30_10) AS StDevOfMA30_10,  ([query_zSMA].[MA30_10]-Avg([a].[ma30_10]))/StDev([a].[MA30_10]) AS risk
FROM query_zSMA AS A INNER JOIN query_zSMA ON (A.PK <= query_zSMA.PK) AND (A.PK>query_zSMA.PK-300)
GROUP BY query_zSMA.PK, query_zSMA.sDate, query_zSMA.Price, query_zSMA.MA10, query_zSMA.MA30, query_zSMA.MA30_10;

And query_zSMA:

SELECT MA.PK, MA.sDate, MA.Price, MA.MA10, MA.MA30, [MA30]/[MA10] AS MA30_10
FROM (SELECT tblFTSE.PK, tblFTSE.sDate, tblFTSE.Price, Avg(t10.Price) AS MA10, Avg(t30.Price) AS MA30 FROM (tblFTSE INNER JOIN tblFTSE AS t10 ON (t10.PK >tblFTSE.PK-10) AND (t10.PK <=tblFTSE.PK)) INNER JOIN tblFTSE AS t30 ON (t30.PK >tblFTSE.PK-30) AND (t30.PK <=tblFTSE.PK) GROUP BY tblFTSE.PK, tblFTSE.sDate, tblFTSE.Price)  AS MA
WHERE MA.PK>=300
GROUP BY MA.PK, MA.sDate, MA.Price, MA.MA10, MA.MA30, [MA30]/[MA10];

Also, I apologize for not providing the source data. I thought you were asking for the output data.

Absolutely magic! You're my hero.
Eric

[June7]

CJLondon provided those details, not I.

[EricRex]

CJLondon provided those details, not I.

My mistake. Regardless, my sincere thanks for your help.

[EricRex]

Hey guys,

I just found out the icky side effect of having the non-standard Joins in the above queries: You can't create a table or append to a table. Access throws an error message about not being able to represent something in Design view (QBE). The only thing you can do is copy the data into Excel and then copy it back into the Access table.

Not the worst thing in the world, but not as elegant as I'd hoped.

Regardless, thanks again!
Eric

[CJ_London]

Is that my query or your version which references itself?

[June7]

Certainly can run INSERT or SELECT INTO action with non-standard join dataset. I just did both.

Either build entirely in SQL View or save non-standard query object and reference it in QBE.

[EricRex]

Is that my query or your version which references itself?

My queries taken pretty directly from yours with appropriate renaming:

qryRiskZSMAs:

SELECT SMA.ID, SMA.BarDate, SMA.Close, Round(SMA.SMA10, 6) AS SMA10, Round(SMA.SMA30,6) AS SMA30, Round([SMA30]/[SMA10],6) AS SMA30_10
FROM (SELECT VIX_DailyHist.ID, VIX_DailyHist.BarDate, VIX_DailyHist.Close, Avg(t10.Close) AS SMA10, Avg(t30.Close) AS SMA30 FROM (VIX_DailyHist INNER JOIN VIX_DailyHist AS t10 ON (t10.ID <=VIX_DailyHist.ID) AND (t10.ID >VIX_DailyHist.ID-10)) INNER JOIN VIX_DailyHist AS t30 ON (t30.ID <=VIX_DailyHist.ID) AND (t30.ID >VIX_DailyHist.ID-30) GROUP BY VIX_DailyHist.ID, VIX_DailyHist.BarDate, VIX_DailyHist.Close)  AS SMA
WHERE SMA.ID>=300
GROUP BY SMA.ID, SMA.BarDate, SMA.Close, SMA.SMA10, SMA.SMA30, [SMA30]/[SMA10];

qryRiskZ_AvgStDevRiskZ:

SELECT [qryRiskZ_SMAs].ID, [qryRiskZ_SMAs].BarDate, [qryRiskZ_SMAs].SMA10, [qryRiskZ_SMAs].SMA30, [qryRiskZ_SMAs].SMA30_10, Round(Avg(A.SMA30_10),6) AS AvgSMA30_10, Round(StDev(A.SMA30_10),6) AS StDevSMA30_10, Round(([qryRiskZ_SMAs].SMA30_10-Avg(A.SMA30_10))/StDev(A.SMA30_10),6) AS RiskZ
FROM qryRiskZ_SMAs AS A INNER JOIN qryRiskZ_SMAs ON (A.ID<=[qryRiskZ_SMAs].ID) AND (A.ID>[qryRiskZ_SMAs].ID-300)
GROUP BY [qryRiskZ_SMAs].ID, [qryRiskZ_SMAs].BarDate, [qryRiskZ_SMAs].SMA10, [qryRiskZ_SMAs].SMA30, [qryRiskZ_SMAs].SMA30_10;

Is it because instead of writing the full SQL of INSERT INTO or INTO... I used the Query Design Tab feature of Make Table?

Cheers,
Eric

[EricRex]

Is it because instead of writing the full SQL of INSERT INTO or INTO... I used the Query Design Tab feature of Make Table?

Just confirmed that INSERT INTO works as I'd hoped. Sorry for thinking something might be easy!

Cheers,
Eric