 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / November 2006Field in table based on avg of qry fields?
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 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? [quoted text clipped - 14 lines] > Thank you, > Richard 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 [quoted text clipped - 35 lines] > > Thank you, > > 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 [quoted text clipped - 40 lines] > > > Thank you, > > > Richard 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 [quoted text clipped - 52 lines] > > > > Thank you, > > > > Richard 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 > Tina, > [quoted text clipped - 70 lines] > > > > > Thank you, > > > > > Richard 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 > 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 [quoted text clipped - 101 lines] > > > > > > Thank you, > > > > > > Richard we're getting beyond my general skill level in SQL, i'm afraid. suggest you take what we've come up with so far, and start a new thread in the microsoft.public.access.queries newsgroup. recommend you give complete information in your new post, rather than just directing people back to this thread - though you may want to *also* include a link back to this thread. hth > Tina, > [quoted text clipped - 126 lines] > > > > > > > Thank you, > > > > > > > Richard Just to clarify, the TotFP and TotAT criteria fields are different than the 4 money fields. -R > 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 [quoted text clipped - 52 lines] > > > > Thank you, > > > > Richard |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.