Update multiple fields in a table using multiple select subqueries

[EricRex]

Hey there,



I'm way out of my league here and hoping for the usual great help here. This is a follow up question to this post:
https://www.access-programmers.co.uk/forums/threads/n-period-moving-average-in-queries.37137/#post-1948362.

From that post, I have a table that has multiple values calculated from a source data table containing VIX stock data. The new table has Autonumber ID, Date, 30-day avg, 10, day avg, a field of the 30d avg/ 10d avg, and two other values that should contain the 300-day of the 5th field and 300-day StDev of the 5th field. I have populated the first 5 fields with an Append query.

I'm trying to figure out how to update the remaining two fields based on select statements of the values in the table.

The source and destination table is named VIX_RiskZ with snippet here:
ID BarDate SMA30 SMA10 SMA30_10 AvgSMA30_10 SDevSMA30_10 RiskZ <--- will be invalid until the previous two fields are populated with non-zero values.
1 11/4/2022 24.55 24.55 1 0 0 #Div/0!
2 11/7/2022 24.45 24.45 1 0 0 #Div/0!
3 11/8/2022 24.81 24.81 1 0 0 #Div/0!
4 11/9/2022 25.13 25.13 1 0 0 #Div/0!
5 11/10/2022 24.81 24.81 1 0 0 #Div/0!

The following query seems to generate proper values, although I can't verify them for certain until I get them into the table for the final calculated field, RiskZ to do its magic:

SELECT b.BarDate, Round((SELECT AVG(SMA30_10) from VIX_RiskZ where ID BETWEEN b.ID and b.ID-299), 2) AS AvgSMA30_10, Round((SELECT StDevP(SMA30_10) from VIX_RiskZ where ID BETWEEN b.ID and b.ID-299), 2) AS SDevSMA30_10
FROM VIX_RiskZ AS b;

I tried creating an update query from the above and got a Property Not Found error.

I tried creating an update query from only one of the above selects and again got a Property Not Found error. That query is:

UPDATE VIX_RiskZ SET 
AvgSMA30_10 = Round((SELECT ID, AVG(SMA30_10) from VIX_RiskZ where ID BETWEEN b.ID and b.ID-299 FROM VIX_RiskZ AS b), 2)
WHERE ID = ID

I'm not sure what the WHERE clause should contain so that the new field values are in the right ID/BarDate record, and not clear how to get the subquery to operate properly.

Also looked at:
https://www.access-programmers.co.uk/forums/threads/using-select-as-a-subquery-within-update-query.280714/

And tried:
UPDATE VIX_RiskZ, Round((SELECT AVG(SMA30_10) from VIX_RiskZ where ID BETWEEN b.ID and b.ID-299 FROM VIX_RiskZ AS b), 2) AS AvgSMA300 SET AvgSMA30_10 = AvgSMA300
WHERE ID = ID

And still get Property not found.

Thanks,
Eric

[June7]

To begin with, in Access an UPDATE action cannot involve an aggregate query. If it can be calculated for an UPDATE it can be calculated when needed.

Why would you need to save this Avg() calc?

[EricRex]

To begin with, in Access an UPDATE action cannot involve an aggregate query. If it can be calculated for an UPDATE it can be calculated when needed.

Why would you need to save this Avg() calc?

So I can do further calculations based on these calculations. The RiskZ column in the table is the average in this query divided by the StDevP in this query.
And then I can run correlation studies against the RiskZ values.

If you're saying this is impossible in an Update query, how can I put these values into the table from the original Insert query? The first three calculated values were inserted with this query:

INSERT INTO VIX_RiskZ ( BarDate, SMA30, SMA10, SMA30_10 )
SELECT b.BarDate, Round((SELECT sum(close) from VIX_DailyHist where ID BETWEEN b.ID and b.ID-29)/IIf(ID>30,30,ID),2) AS SMA30, Round((SELECT sum(close) from VIX_DailyHist where ID BETWEEN b.ID and b.ID-9)/IIf(ID>10,10,ID),2) AS SMA10, Round(SMA30/SMA10,2) AS SMA30_10
FROM VIX_DailyHist AS b;

I couldn't figure out how to calculate a moving average from the SMA30_10 values in the above query. Can I do the same syntax as the other fields, as in:

Round((SELECT sum(SMA30_10) from ??????   WHERE ID BETWEEN b.ID and b.ID-299)/IIf(ID>300,300,ID),2) AS AvgSMA30_10

Thanks,
Eric

[EricRex]

Since I can't use a direct UPDATE statement with these queries, decided to try separating the two queries like this:

UPDATE VIX_RiskZ 
SET AvgSMA30_10 = (Select AvgSMA30_10 from qryVIX_SMA_AvgStDev),
StDevSMA30_10 = (Select StDevSMA30_10 from qryVIX_SMA_AvgStDev)
WHERE VIX_RiskZ.BarDate = qryVIX_SMA_AvgStDev.BarDate

and qryVIX_SMA_AvgStDev is as follows:

SELECT ID, b.BarDate, Round((SELECT AVG(SMA30_10) from VIX_RiskZ where ID BETWEEN b.ID and b.ID-299), 2) AS AvgSMA30_10, Round((SELECT StDevP(SMA30_10) from VIX_RiskZ where ID BETWEEN b.ID and b.ID-299), 2) AS StDevSMA30_10
FROM VIX_RiskZ AS b;

Not sure how to get the records in the second query to match the main UPDATE query. Either the ID field or the BarDate field could be used in the WHERE clause.

When I run the first query, it prompts me for the value of qryVIX_SMA_AvgStDev.BarDate.

Thanks!

[June7]

Query still involves data produced by aggregate query. Doesn't matter that the aggregate query is not in this query, it's in the sequence.

And your WHERE clause is not valid.

And AFAIK, fieldname = (SELECT field FROM table) would never work anyway, even if only 1 record is retrieved by the subquery (I tested).

Alternatives are VBA looping recordset or using domain aggregate function (DSum, DAvg, etc) or saving aggregate data to a 'temp' table and using that as source for UPDATE.

This is a common topic.

[EricRex]

And your WHERE clause is not valid.

And AFAIK, fieldname = (SELECT field FROM table) would never work anyway, even if only 1 record is retrieved by the subquery (I tested).

Yeah, I get that the WHERE isn't valid. And there's no way to get the ID or Date field from the subquery to make it valid?

[orange]

Eric,
Can you post a copy of your database or a partial version showing the fields and data and description of exactly what data/values you want to add to your table?

[EricRex]

Eric,
Can you post a copy of your database or a partial version showing the fields and data and description of exactly what data/values you want to add to your table?

Posted in the original post:
The source and destination table is named VIX_RiskZ with snippet here:
ID BarDate SMA30 SMA10 SMA30_10 AvgSMA30_10 StDevSMA30_10 RiskZ <--- will be invalid until the previous two fields are populated with non-zero values.
1 11/4/2022 24.55 24.55 1 0 0 #Div/0!
2 11/7/2022 24.45 24.45 1 0 0 #Div/0!
3 11/8/2022 24.81 24.81 1 0 0 #Div/0!
4 11/9/2022 25.13 25.13 1 0 0 #Div/0!
5 11/10/2022 24.81 24.81 1 0 0 #Div/0!

All values other than ID and Date are Single values. AvgSMA30_10 and StDevSMA30_10 fields are the 300-day moving average and StDev of the SMA30_10 field. The RiskZ field is AvgSMA30_10 / StDevSMA30_10

So, I haven't figured out a way to calculate all of these in one pass in SQL, given that I have to calculate at 4 levels: SMA30 and SMA10 are the moving averages of a different table's field, SMA30_10 is SMA30/SMA10, AvgSMA30_10 and StDevSMA30_10 fields are based on SMA30_10, and RiskZ is based on AvgSMA30_10 and StDevSMA30_10.

From that description, is it possible to calculate all of these in multiple levels of subqueries? Something like
SELECT AvgSMA30_10/StDevSMA30_10 AS RiskZ FROM (Select SMA30_10 FROM StDevSMA30_10 FROM (SELECT ID, b.BarDate, Round((SELECT AVG(SMA30_10) from VIX_RiskZ where ID BETWEEN b.ID and b.ID-299), 2) AS AvgSMA30_10, Round((SELECT StDevP(SMA30_10) from VIX_RiskZ where ID BETWEEN b.ID and b.ID-299), 2) AS StDevSMA30_10 FROM VIX_RiskZ AS b));

Part of the reason I'm trying to make this as straightforward as possible is for future iterations of the source data table updates. If it were only this one time, I'd do it quick and dirty. But I'll update the VIX price table as often as practical, and having multiple steps in the process of calculating these derived values makes it prone to errors.

I suppose I could just write this all in VBA and be done with it, but that doesn't further my understanding of SQL.

Cheers,
Eric

[orange]

Didn't find a link to your database.

[June7]

For providing tabular data in post, use table builder on Advanced post editor. Or simply copy/paste Access records or Excel cells.

To provide db, follow instructions at bottom of my post.

Normal approach would be an aggregate SQL with grouping field(s) that would allow JOIN to other dataset. But in your case, it looks like every record would show the same values for AvgSMA30_10 and StDevSMA30_10. A Cartesian query could possibly produce the output you want. That is a query without a JOIN clause which causes every record in each dataset to associate with every record of other dataset. If each dataset has 3 records the output would be 9 records. So if one table has 5 records and aggregate query has 1, output will be 5 records.

SELECT VIX_RiskZ.*, MyAggregateQuery.* FROM VIX_RiskZ, MyAggregateQuery WHERE {filter criteria if needed}

Actually, could possibly build Cartesian query and use that as source for INSERT.

INSERT INTO VIX_RiskZ(BarDate, SMA30, SMA10, SMA30_10, AvgSMA30_10, StDevSMA30_10) SELECT GroupedQuery.*, SMA30_10AggQuery.* FROM GroupedQuery, SMA30_10AggQuery WHERE {filter criteria if needed}

[EricRex]

ID	BarDate	SMA30	SMA10	SMA30_10	AvgSMA30_10	StDevSMA30_10	RiskZ
13	11/22/2022	23.82	23.52	1.01	0	0
14	11/23/2022	23.57	22.95	1.03	0	0
15	11/24/2022	23.36	22.64	1.03	0	0
16	11/25/2022	23.18	22.44	1.03	0	0
17	11/28/2022	23.13	22.28	1.04	0	0
18	11/29/2022	23.06	22.02	1.05	0	0
19	11/30/2022	22.93	21.66	1.06	0	0
20	12/1/2022	22.77	21.26	1.07	0	0
21	12/2/2022	22.6	20.85	1.08	0	0
22	12/5/2022	22.51	20.69	1.09	0	0

[EricRex]

Hey June7,

I take it that you mean my GroupQuery is the one I used to populate the first 4 fields of the table:

SELECT b.BarDate, Round((SELECT sum(close) from VIX_DailyHist where ID BETWEEN b.ID and b.ID-29)/IIf(ID>30,30,ID),2) AS SMA30, Round((SELECT sum(close) from VIX_DailyHist where ID BETWEEN b.ID and b.ID-9)/IIf(ID>10,10,ID),2) AS SMA10, Round(SMA30/SMA10,2) AS SMA30_10
FROM VIX_DailyHist AS b;

And the aggregate query is the one I'm trying to use to populate the other 3 columns?

SELECT ID, b.BarDate, Round((SELECT AVG(SMA30_10) from VIX_RiskZ where ID BETWEEN b.ID and b.ID-299), 2) AS AvgSMA30_10, Round((SELECT StDevP(SMA30_10) from VIX_RiskZ where ID BETWEEN b.ID and b.ID-299), 2) AS StDevSMA30_10
FROM VIX_RiskZ AS b;

In this case, I don't think that will work because the first three columns or sets of numbers must exist before I can do the second set of calculations.

I will work on a subquery version of what I'm thinking and post here.

Cheers,
Eric

[June7]

Use GroupQuery object as source for second aggregation or maybe second aggregation can also use raw data table.

Want to provide db for analysis?

[EricRex]

Use GroupQuery object as source for second aggregation or maybe second aggregation can also use raw data table.

Want to provide db for analysis?

I don't really want to mess with paring down my DB to a test set. It's pretty big.

Here's what I've come up with in the subquery realm so far:

SELECT b.ID, b.BarDate, b.SMA30, b.SMA10 , b.SMA30_10, Round((SELECT AVG(SMA30_10) FROM qrySMA_RISKZ_v3 as c WHERE b.ID BETWEEN c.ID and c.ID-299), 2) AS AVGSMA30_10, Round((SELECT StDevP(SMA30_10) FROM qrySMA_RISKZ_v3 as d WHERE b.ID BETWEEN d.ID and d.ID-299), 2) AS StDevSMA30_10
FROM qrySMA_RISKZ_v3 as b;

Sadly, it returns the same value for every AVGSMA30_10 row and the same value for every StDevSMA30_10 row, although those two values are different from each other. Looks like my WHERE isn't working as hoped.

Result snippet:

ID	BarDate	SMA30	SMA10	SMA30_10	AVGSMA30_10	StDevSMA30_10
13	11/22/2022	23.82	23.52	1.01	1.01	0.09
14	11/23/2022	23.57	22.95	1.03	1.01	0.09
15	11/24/2022	23.36	22.64	1.03	1.01	0.09
16	11/25/2022	23.18	22.44	1.03	1.01	0.09
17	11/28/2022	23.13	22.28	1.04	1.01	0.09
18	11/29/2022	23.06	22.02	1.05	1.01	0.09
19	11/30/2022	22.93	21.66	1.06	1.01	0.09
20	12/1/2022	22.77	21.26	1.07	1.01	0.09
21	12/2/2022	22.6	20.85	1.08	1.01	0.09

Pretty close to what I'm hoping for.

Cheers,
Eric

[June7]

If you won't provide db, could provide sample data as tables in post. A table of raw data and a table of desired output.

Maybe the WHERE clause is backwards.

WHERE d.ID BETWEEN b.ID AND b.ID-299