Best strategy?

[Laurel]

I have a situation where I need to do a lot of calculations on the details
of a query before displaying the summarized results. I'm having a
difficult time sorting out the best approach for behind-the-scenes
traversing of recordsets, since everything in Access seems tied to a form.
The idea I have so far is this....

Note - If the manipulations could be done directly in the query, I'm not
good enough at SQL to manage it, so that's not a solution.

1 - Initially assign the detail query to my form.
2 - Set the fields to visible = false (since I don't want the user to see
the details at all)
3 - In my Load event, step through the recordset. Gather up my summary
information, do my manipulations, and write the results out to a reusable
summary table (I'm not sure how to do that in visual basic... could someone
help out here? Can you just do a SQL Insert statement inside of code? Or do
you have to use an Insert Query?)
4 - Reasssign the form's Recordsource value to the new summary table and do
a Me.Requery. Make everything visible.
The summary table will have the same column names as the original detail
table.

This seems pretty klugey. I'm hoping I'm just missing some basic technique
for working behind the scenes in Access. Please advise.

 

[PC Datasheet]

You say you have a lot of calculations. You might consider writing the data out
to a preformatted Excel workbook, let Excel crunch the numbers and then import
the results back to Access.

 

[Laurel]

Well, not a lot.... not to warrant setting up a spreadsheet.... just to much
for me to do comfortably inside the SQL statement. I need to learn to
traverse records inside code, and I think I'm getting closer.... See my
second posting "accessing tables directly."

 

[DanK]

It sound like you might want to use multiple queries, or
nested queries.

 

[Ed Warren]

Create a new module (MODULE1)

Create a new function MyCalculation.

Note: Need to make sure F1-F4 are appropriate data types when passed into
the function e.g. numbers of the proper type
and check for invalid numbers within the function.

Call this from a query with tables that contain the requied fields, say
myField1, myField2, myField3, myField4

In the query results field enter :
NameValue:MyCalculation(myField1,myField2,myField3,myField4)

Your results will be a dataset with the calculations done for you and
seperated out into a module where you can make changes.

Hope this helps to get you started.

Ed Warren

---------------------------------------------------------------------code
Public Function MyCalculation(F1 as integer, F2 as double, F3 as long, F4 as
currency) As Double

'Returns ((F1+F2 TIMES F3+F4) TIMES F1) DIVIDED BY 60

Dim returnValue As Double
returnValue = ((F1 + F2) * (F3 + F4)) * F1
returnValue = returnValue / 60
MyCalculation = returnValue
End Function