Tina,
We're close to solving it. For the data to be useful in comparisons, let me
explain how I'd like it to show:
1) All of the other fields in the query display normally, one record at a
time. The query shows ALL of the records in the database, not just ones with
particular TotFPs and TotATs.
2) One of the fields (in the query showing ALL records) is a calculated
field that does a subquery (I think) using the particular TotFP and TotAT of
that particular record (So, it needs to LOOK for that particular record's
TotFP and TotAT). After the subquery is done, the AVG:sum of
Money1-4/Count([PK]) is done and is displayed in the calculated field of that
particular record.
I guess what we need to add to the code you sent is subquery code looking
for a particular record's TotFP and TotAT. Would it go in a Totals query,
with all the other fields as Group By (in total row), and Expression in the
calculated field? What is the proper syntax for adding the subquery code, or
is there another way?
Thank you.
Richard
tina said:
okay, try the following: write a SELECT query to pull the records you want
with the criteria on TotFP and TotAT fields, putting only those two fields
in the query grid. next, in query Design view, uncheck the Show boxes for
those two fields. add a calculated field to the first blank column in the
grid, as
Avg: (Sum([Money1])+Sum([Money2])+Sum([Money3])+Sum([Money4]))/Count([PK])
the above goes all on one line in the column's Field space. replace Money1,
Money2, etc, with the correct names of the money fields in the table, of
course. replace PK with the name of the primary key field in the table
(probably any field would do here, but i usually use the PK field).
hth
Rwh5757 said:
Tina,
My database consists of past results of financial situations. For each
example (record) of a financial situation, the results ($) show up in
multiple columns, reflecting 4 different opportunities (times) to
invest,
and
how well each one did.
Entering two separate criteria values (TotFP, TotAT) into a query, produces
records from the database, matching those same criteria. In the query
results, if all of the money figures from all 4 coumns are added up, then
divided by the number of records, an average $ per record is produced. This
number is then used to predict future situations, for those that have the
same two criteria values.
Hopefully this is clear.
Thanks.
Richard
:
re-reading the steps you posted, it's not clear what mathematical equation
you're attempting to create. forgetting the mechanics of how we
might do
it
in Access, can you explain a bit about your data and what statistical
information you're trying to get out of it?
hth
Okay, I understand that it is better not to store that information
in
the
table. However, my primary question was not how to store it in a table,
but
how to get the information out in first place. If there is an alternate
method of calculating it in a query, what is it?
:
recommend you don't. here's MVP John Vinson's reply to basically the
same
question in another thread, which gives an excellent explanation
of
why
it's
not a good idea:
"Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.
Just redo the calculation whenever you need it, either as a
calculated field in a Query or....in the control source of a
Form or
a
Report textbox."
hth
How do I populate a field in one table with an average of
other
fields
in
a
query based on the first table?
Breakdown(ideally):
1) In Table1, in the first record, the values from field1 and field 2
are
retrieved.
1) Query (based on entire Table1) gets the two values from
field1
and
field2, query is run.
2) In the query results, all the values (in all resulting
records)
in
field3, and field4 are added together and averaged.
3) The averaged figure is then put in field5 in Table1, in the first
record.
4) Go to the next record in Table1 and continue until EOF.
I would appreciate any help you can give on this. If you have sample
code,
that would be great also.
Thank you,
Richard
