How to display calculated fields instead of storage

[bknight]

I have browsed many form questions today and combined with my previous
experiences, I have a question for MVP's (or anyone for that matter). Many
of you have commented to users that calculated values need/should not be
stored data. While I don't disagree with that philosophy, I have some
comments about those thoughts and then a question. Many times storing a
calculated is much easier than displaying calculated data, and output to
other programs like excel are done easily when stored. I know all of you
experts can say that these tasks are easily accomplished without storage, but
to novices that is not the case.

Anyway I have an indexed table that contains 5 data fields (one of which is
date). In the same table I have 7 more fields all calculated (everyday by
code via an command button). Of the 7 fields 2 are same date calculations
and the others are calculated from the previous day, some same field others
different fields.

The same date calculations are simple for me but I always had trouble with
the previous day's data calculations, so how does one write code to access
previous data and perform calculations on a mixture of previous and current
data for dispaly in forms (orqueries for output to excel)? I'm using A97
since I have more experience with it. Although I do have A2000.

 

[Allen Browne]

Use a subquery to identify a value from the previous row. Here's how:
http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[bknight]

That is a great link, thanks. However, it doesn't calculate how I need to
calculate. I probaly was not as specific as I should have been in the
original post.
Record n values are calculate using n values and n-1 values, BUT n-1 values
are calculated from n-1 and n-2...n-n values were calculated but I don't have
the previous data so record one is fixed(and I have the calculated values for
it currently stored in the table but these could be hard coded). If I had a
form based on a recordset and jumped to some record the previous records do
affect the values although the further back in time the less the affect. The
decay factors are 0.1 and 0.05.

Any further thoughts?

Thanks for your time and effort.

 

[Allen Browne]

So the subquery needs to accumulate the previous value?

This example assumes you have a table of transactions (like a bank accout),
and you need to calculate the balance on each row (i.e. the sum of the
previous amounts plus this transaction.)

You type an expression like this into the Field row in query design:
Balance: (SELECT SUM(Amount) AS RunningBalance
FROM tblTransaction AS Dupe
WHERE Dupe.TransDateTime <= tblTransaction.TransDateTime)

The example assumes the table is named tblTransaction, with fields:
- Amount Currency the credit or debit amount of the
transaction
- TransDateTime Date/Time the date and time of the transaction.
To work reliably TransDateTime must be required (you have to enter a date),
and uniquely indexed (so you can't have 2 transactions at exactly the same
time.)

It you did have 2 transactions at exactly the same momennt, the concept of
'previous' is undefined.

You could use a DSum() expression instead of a subquery if:
- you need editable results;
- this has to go to a report that gives you a 'multi-level group-by' error;
- you can't figure out the subquery.

 

[bknight]

Some of the calculations are sums and some are products and sums of products.
Fld1- Date
Fld2-Fld5-Numbers
Fld6 Division using Fld2(n) through Fld5(n)(all)
Fld7 Difference of Fld1(n) and Fld2(n)
Fld8 Sum of Fld7(n) and Fld7(n-1)
Fld9 Sum of Fld9(n-1) and prod. of 0.1 and diff. of Fld7(n) and
Fld9(n-1)
Remember Fld7 is difference of Fld1(n) and Fld2(n)
Fld10 Sum of Fld10(n-1) and prod. of .05 and diff. of Fld7(n) and
Fld10(n-1)
Fld11 Sum of Fld9(n) and Fld10(n)
Fdl12 Sum of Fld11(n) and Fld11(n-1)

That is what the code does associated with the command button used daily.
The calculated values don't change once calculated, unless one wanted to
change the decay factor. In that case all the data would be recalculated not
just todays(Except day one which is fixed since I don't have the previous
data. Mathematically day one values would be incorrect but that error would
be narrowed with each succeding day.)

For the query or code to work any record navigated not just in sequential
manner the calculations need to be the same values however that day was
navigated(forward, backward or jump).

Back in the early 90's I toyed with using an arrray but that required all
the data to be calculated everytime the data was accessed and I dropped the
idea in favor of using stored data.

 

[bknight]

I was modifying your subqery to see the output using:

SELECT tblName.Index,
tblName.Day,
tblName.Fld11,
(SELECT TOP 1 Dupe.Fld11
FROM tblName AS Dupe
WHERE Dupe.Day < tblName.Day
ORDER BY Dupe.Day DESC, Dupe.Index) AS PriorValue
FROM tblName;

My first try was identical to your link (missing statement Dupe.Index =
tblName.Index AND Dupe.Day etc). This resulted in output of null previous
values. A lightbulb went on and the Indexes can not be equal (maybe a join
instead of an equal?), so I eliminated that part and re-ran. I got the
results you were showing me.

HOWEVER, two odd aspects occurred and maybe that involved the Index portion.
The first aspect: Query took a long time to run on 5796 records.
The second aspect: Query CHANGED the date of the last record from 5/22/2009
to 5/22/1997???????????

Why would a select statement update a value??

 

[Allen Browne]

First issue is that some of your field names are reserved words, e.g. Index
and day. Wrapping those names in square brackets may help, but it's best to
avoid reserved words. Here's a list:
http://allenbrowne.com/AppIssueBadWord.html

For each of the 5796 rows, the subquery may be examining all 5796 rows in
the table. That's 5796^2 calculations. It may take a while.

Are you saying that executing the SELECT query alters the value in a record
in your table? If so, something else must be wrong. Add the square brackets,
make sure Name AutoCorrect is off, run a compact/repair, etc.

 

[bknight]

OK, changed the names of the two fields and every query, form and code that
refers to them.
I reran your query, and I mis-stated the long delay. The delay was in
navigating to the last record that took so long, the calculation of the
query/sub query as instantaneous as the rest of the queries.
The query didn't change any of the data this time (BTW where is the
autocorrect feature in A97?), but another oddity showed up the output was 3
records short. Finding that interesting I reran and stepped through the
records (steeping through didn't seem to be a long process other than almost
5800 records) and came up 20 short. Ran it a third time and was 2 short.
The missing dates are different in each of the runs. So I am more preplexed
than at the beginning.

 

[Allen Browne]

You're right: A97 didn't have the Name AutoCorrupt feature.

If the number of records varies, you may have a bad index. Run a
compact/repair. More info:
http://allenbrowne.com/ser-47.html#NumberOfRecordsVaries

Clear anything in the query's Filter an OrderBy properties (in the
Properties sheet when examining the properties of the query.) They can have
a bad effect in some queries:
http://allenbrowne.com/Bug-OrderBy.html

Or perhaps the sorting is not defined, so the records are present but in a
different order.

If none of those are the issue, it could be a data types issue. JET may
misunderstand the data type of parameters, calculated fields, or values read
from other places (such as a text box on a form), and then apply the wrong
kind of filtering/sorting and so return the wrong results.

 

[bknight]

I did everything but the query still excludes records. Perhaps I could send
you the Db and let you look at it in person?

 

[Allen Browne]

Please don't forward it.

There will be a reason why it's excluding records. Your task is to figure
out why. Examining what's common to the excluded records may give you an
idea.

 

[bknight]

Using this query (navigation to the end takes about 30 seconds)

SELECT tblName.DateIndex, tblName.TradeDay, tblName.OSC, (SELECT TOP 1
Dupe.OSC
FROM tblName AS Dupe
WHERE Dupe.TradeDay < tblName.TradeDay
ORDER BY Dupe.TradeDay DESC) AS PriorValue
FROM tblName;
1st
Excluded 622
Excluded 703
2nd
Excluded 546
3rd
Excluded 635
4th
Excluded 818
5th (after shutting down the Db and reopening)
Excluded 636
6th (navigating through recordset 1000 records at a time)
Excluded 528
Excluded 756
Excluded 1031
Excluded 1295
Excluded 2199
Excluded 2299
Excluded 2325
Excluded 3209
Excluded 3230
Excluded 3322
Excluded 4133
Excluded 4135
Excluded 4167
Excluded 5223
Excluded 5267
Excluded 5268
7th (navigating to end)
Two Excluded
8th (navigating by 1000)
Fifteen Excluded

Using this query all of the data is displayed although the query still takes
about 30 seconds to complete. I'm not sure whether the logic is correct,

SELECT tblNmae.DateIndex, tblName.TradeDay, tblName.OSC, First((SELECT TOP 1
Dupe.OSC
FROM tblName AS Dupe
WHERE Dupe.TradeDay < tblName.TradeDay
ORDER BY Dupe.TradeDay DESC)) AS PriorValue
FROM tblName
GROUP BY tblName.DateIndex, tblName.TradeDay, tblName.OSC;

I am unable to find a commonality, with the exception that naviagting to the
last record displays more records than navigating by 1000.

 

[Allen Browne]

So the query is this:
SELECT tblName.DateIndex, tblName.TradeDay, tblName.OSC,
First((SELECT TOP 1 Dupe.OSC
FROM tblName AS Dupe
WHERE Dupe.TradeDay < tblName.TradeDay
ORDER BY Dupe.TradeDay DESC)) AS PriorValue
FROM tblName
GROUP BY tblName.DateIndex, tblName.TradeDay, tblName.OSC;

And you are saying that if you get a different number of records returned if
you omit the subquery and just use:
SELECT tblName.DateIndex, tblName.TradeDay, tblName.OSC,
FROM tblName
GROUP BY tblName.DateIndex, tblName.TradeDay, tblName.OSC;

Is that the case?

 

[bknight]

I didn't run the second query, I just experimented and grouped and all the
records were displayed, not excluding any records as without the grouping
excluded records. If you run the second query it doesn't show/calculate data
using previous values which was the original intent.