query calculations

[Guest]

Hi!

I need to calculate stuff and I'm planning to do it in a query. My
calculations aren't simple (like a+b-c), so I cannot do then straight into a
field in design mode, I'm doing them in code. It is not a problem for me, it
works. My question is:

if I select fields from a table and sort them (so that the order stays
always the same if I use/open the query) can I create calculated fields to a
query so that the calculated results stay in their right places?

(To be clearer: If I do the calculations record by record will for example
the results on my 10th record be the same always (source data not changing),
even if I open and close and open and close and open and close the query to
check it out with my two eyes? I don't want the results be bouncing around..)

-Beginner-

 

[Allen Browne]

You can use a VBA function to perform a calculation in a query.

However, if the function is meant to calculate something that is affected by
the query's sorting or criteria, it's not so simple. There is no guarantee
Access will call your function in the order of the records in the query. In
fact, there's a good chance it won't do that: the optimizer calls the
function only when it needs a value to display, so if you display the first
screenful, and jump to the end of the query, the function will not be called
for all the intervening records.

Similarly if your function is affected by the sorting and filtering (e.g. a
running sum), you will not be able to build the logic of the function so
that it automatically stays up to date when the query's criteria or sorting
row are changed, or even if the user applies a Filter or OrderBy to the
query results.

 

[Guest]

Hmm.. Okay.. Then I have a problem. I ment to do all the calculations in
queries so that first I would create a query like this:

Dim db As Database
Dim qryDef As QueryDef
Dim strsql As String
strsql = "SELECTfield1, field2, '0' AS Field3 FROM Table1 ORDER BY field1"
Set db = CurrentDb
Set qryDef = db.CreateQueryDef("MyQuery", strsql)

Then I would have calculated values in code for Field 3 (this is a simple
example). I cannot do that because I cannot change the 0 value I put in field
3 as I create it.. Is it possible to change that value?

I have this working in the way that I create the fields (where I need to put
the calculated info in) to a table, and then I make a query and select * from
that table. So I get empty fields with datatype double into the query. Then I
make the calcucations, and everything works fine, but my results go into the
table too.. And I'm afraid of that, should I be?

- Beginner -

 

[Allen Browne]

The temporary table might be the best way to calculate the results when you
need them (e.g. for a report.) That's a common technique when things get
complex. You have not broken any normalization rules by storing the result
in a temporary table.

 

[Guest]

Okay. I'll definately look into that.

By the way, do you happen to know what will happen, if I do it as I said in
my previous post:

I have this working in the way that I create the fields (where I need to put
the calculated info in) to a table, and then I make a query and select * from
that table. So I get empty fields with datatype double into the query. Then I
make the calcucations, and everything works fine, but my results go into the
table too.. And I'm afraid of that, should I be?

So if I have a code that calculates values into the query but it also puts
them into the table, will the values in the query be correct even tough the
records in the table can be in whatever order? I sort the table when I query
it. (If I for example open and close and open the query, does it store the
calculated values in itself or does it take them from the table? And if I
sort the table in the query, do the values go into right places in the table
(for example id 100 values to id 100 int the table)?)

- Beginner -

 

[Allen Browne]

If you use a Select query statement that has an ORDER BY clause that fully
specifies the sort order (e.g. primary key field in a one-table query), and
the turn it into an Append query (Append on Query menu in query design), I
would expect Access to process the records in order, and so to call the
function sequentially.

The other approach would be to append the other fields, and then
OpenRecordset in code on the temp table, and walk the records.

 

[Guest]

Okay, thank you for the answers!

- Beginner -