Populate table column using field comparison in the same table

[EricRex]

Hey guys,

This is a follow up question from https://www.accessforums.net/showthr...070#post530070

The source table contains these example records:

ID	BarDate	SMA30	SMA10	SMA30_10	AvgSMA30_10	StDevSMA30_10	RiskZ	Direction
303	3/16/2022	28.235333	31.574	0.894259	0.874747	0.014714	1.326075
304	3/17/2022	28.354667	31.093	0.911931	0.882184	0.02095	1.419907
305	3/18/2022	28.338667	30.282	0.935825	0.891124	0.028822	1.550953
306	3/21/2022	28.349	28.99	0.977889	0.903519	0.042044	1.768866
307	3/22/2022	28.351667	27.771	1.020909	0.918193	0.056901	1.805178
308	3/23/2022	28.422667	26.883	1.057273	0.933646	0.070585	1.751459
309	3/24/2022	28.479667	26.027	1.094235	0.949705	0.083711	1.726534

I want to populate the Direction field with a +1 if the RiskZ value in the previous record (ID -1) is less than or equal to the current record or -1 flag if the previous record is greater than the current record.

I have a query that does that calculation correctly:

SELECT A.ID, IIF(A.RiskZ <= B.RiskZ, -1, +1) AS Direction
FROM RiskZ AS A, RiskZ AS B
WHERE B.ID = A.ID-1
ORDER BY A.ID;

It returns this sample output:

ID	Direction
303	1
304	1
305	1
306	1
307	1
308	-1
309	-1

I'm trying to create an UPDATE query using the above SELECT query but haven't come up with a working query.

This one fails by prompting for the value of A.ID:

UPDATE RiskZ SET Direction = (SELECT A.ID, IIF(A.RiskZ < B.RiskZ, -1, +1) AS Direction
FROM RiskZ AS A, RiskZ AS B
WHERE B.ID = A.ID-1
ORDER BY A.ID)
WHERE RiskZ.ID = A.ID;

Similarly, I set up the SELECT query above as a saved query, but didn't make any progress that way either.

Ideas?
Cheers,


Eric

[Bob Fitz]

IMHO it might be better NOT to save the calculation to a table but rather do the calculation whenever and wherever it is required.

[jojowhite]

imho, you do not need Direction field, since you can calculate it on your Query (first query on your post).
doing this in a query, means you don't need to worry Updating any field, plus you reclaim field space.

always keep in mind to only use Field if they are going to be filled on data entry. If they are results
of calculation use Query and the Query will Always return correct (if your formula is correct, that is).

[CJ_London]

As an aside to the question- what is the Id field? An autonumber or calculated? What happens if a record is deleted or in the wrong date order?

[EricRex]

Hey guys,

This isn't really a data entry type of table. It's an indicator of the VIX financial data, populated using the query from the original post: https://www.accessforums.net/showthr...070#post530070.

The fallling and rising query is fixed for the two days under consideration because the VIX historical value won't change. The only way the VIX historical data table will change is if I retrieve more current date values. For example, I now have data up to Jan-8-2025. If I update that, I'll have to recreate the table.

CJ_London, the ID field is calculated, not autonumber. By definition, it's guaranteed to be in monotonically increasing order and sorted by date. This is totally in my control and won't change. Records won't be deleted except by huge mistake, and isn't super important to the end goal of creating a statistical analysis of over 1600 trades. I'm trying to get overview info, not individual date/trade info. That would be a minor glitch.

My goal is to create a histogram of actual stock trading results with the RiskZ values sorted into buckets contained in another table, including whether or not the RiskZ value was falling or rising and what its actual value was.

The query that creates that histogram not including the RiskZ Direction (aka, rising/falling) is:

SELECT  DayTrades.Direction, RiskZThresholds.MaxRiskZ,  COUNT(IIF(cTradePL > 0,  cTradePL, NULL)) AS NumWinners, ROUND(COUNT(100 * IIF(cTradePL > 0,  cTradePL, NULL))/COUNT([DayTrades].cTradePL), 0) AS PctWinners,  COUNT(IIF(cTradePL < 0, cTradePL, NULL)) AS NumLosers,  ROUND(COUNT(100 * IIF(cTradePL < 0, cTradePL,  NULL))/COUNT([DayTrades].cTradePL), 0) AS PctLosers
FROM DayTrades, RiskZ, RiskZThresholds
WHERE  DayTrades.TradeDate = RiskZ.BarDate AND RiskZ.RiskZ >=  RiskZThresholds.MinRiskZ AND RiskZ.RiskZ < RiskZThresholds.MaxRiskZ
GROUP BY DayTrades.Direction, RiskZThresholds.MaxRiskZ
ORDER BY DayTrades.Direction, RiskZThresholds.MaxRiskZ;

I just want to add the rising and falling criteria to the histogram, but I think adding it to the existing table is the best way to do it.

Thanks,
Eric