Calculate a table field from other tables

[bposkie]

my first post (or at least my first in a long while), and a bit of a virgin in vba code!

I have three tables tblAlmPhilosophy, tblAlmRationalization, tblPointAnalog. Within tblAlmRationalization I have a field 'fldRecHighDeadband' (integer) that I need to automatically populate for each fldPTName (string) record by examining other record fields within tblAlmPhilosophy and tblAlmRationalization. (fldPTName is common between tblAlmRationalization and tblPointAnalog)

tblAlmPhilosophy is a definition table and contains fldFlow1 (string) and fldPrs1 (string), together with fldFlow1DB (integer) and fldPrs1DB (integer). tblPointAnalog contains fldPTName and fldENGUNITS (string).

I believe I need to lookup the fldPTName from tblAlmRationaization within tblPointAnalog to obtain the value of fldENGUNITS, then...

if fldENGUNITS = fldFLow1, then fldRecHighDeadband = fldFlow1DB
if fldENGUNITS = fldPrs1, then fldRecHighDeadband = fldPrs1DB

How do I pull data from these two tables to calculate fldRecHighDeadband :banghead:?

I was assuming I'd have to assign each table a variable name, a variable for each field of each table, but then I wasn't sure if a loop was needed to cycle through each record field (I don't know how to do loops in vba).

I was going to activate the calculation through a button for 'initiate'.

Once I have the fldRecHighDeadband I'll then need to pull fldMin (integer) and fldMax (integer) from tblPointAnalog and multiply fldRecHighDeadband by fldMin and fldMax to calculate two more fields in tblAlmRationalization; fldRecHighDBMin (integer) and fldRecHighDBMax (integer)

your input with code is much appreciated - thanks in advance!

 

[The_Doc_Man]

The biggest question is why you want to calculate ANYTHING in a table. That's what queries are for.

While it is certainly possible to update tables for things like status changes, you really DON'T want to update a table (in general) for things that change on-the-fly. You want to compute stuff in queries. Remember this: A table is great source for recordsets - but queries are at least equally good if not better. Tables should be relatively static. Queries can take more things into account and become more dynamic. Further, when you are talking about doing math, you DEFINITELY want queries, not table updates.

 

[bposkie]

Thanks for the quick reply

I convinced myself to calculate in a table because I want to initialize the values and I don't want to recalculate them. I was also trying to avoid temporary tables and queries. I was attempting to initially calculate the values, then in a query the values will be evaluated case-by-case and possibly refined before final application. The initial values set a 'default' or a starting point to build from. I also wasn't sure how to calculate them in a query and have the values retained in a table.

 

[The_Doc_Man]

I also wasn't sure how to calculate them in a query and have the values retained in a table.

Though I'm sure there are cases where this makes sense, MOST of the time you would want to NOT store anything in a table when you can compute it.

If the items to be stored are already in the table and are then immutable, you could very easily do the computation during the import process. HOWEVER, your question poses the problem that you want to initiate this computation after-the-fact of initial data storage based on what SEEMS to be an environment where some of your contributory information might not immediately be present i.e. mutable data.

Therefore, the first question has to be: How stable are your tables once initially loaded? If the answer is "not very stable" then you do not want to store ANYTHING in the tables except for the raw data.

 

[bposkie]

The two tables, tblPointAnalog and tblAlmPhilosophy are already existing and solid. I wanted to conduct the calculations during the creation of tblAlmRationalization. I was intending to create the fields for tblAlmRationalization, I would copy fldPTName from tblPointAnalog into tblAlmRationalization, then calculate data for some fields as available. I would then create a query between tblPointAnalog and tblAlmRationalization.

 

[The_Doc_Man]

Nothing you have described so far sounds like it needs to be in a table. You can do that in a query called qryAlmRationalization (instead of a table). How many records are we talking about? If it is less than 1000, you'll never see the difference between the query and a table. But assuming that you really honestly need a table, you can do this easily enough.

Do a query design. Change it to a make-table query. In grid view, in the blank space above the grid, add tables as needed to bring in everything you need to see. If there is a relationship between the tables already, the query design wizard will see it (assuming you have wizards enabled). If not, draw connecting lines between the fields that SHOULD have a relationship. Tailor the relationship type.

Now bring down the fields from the tables that are things that will go in your new table. Execute the query. This will build the table, but you aren't done yet. Now open that table's design grid. Add the fields that were NOT available from the two raw tables directly.

Now, back to query design again. Build one or more summation queries that do the required MIN or MAX or whatever else you need along with the necessary key fields, which in this case need to be GROUP BY keyname. Thinks like COUNT, MIN, MAX, SUM, AVG could all be done in the same query if they are all based on the same key field. You might need to establish an alias for each field, though, so that in the final step you can reference the SQL aggregated fields.

One last set of steps. For of the summation queries, design a query between the new table and the summation queries in which you establish a relationship between the keys. (I'm thinking INNER JOIN for this step.) You SHOULD have only one row in the query for each unique key. Do an UPDATE query such that you store the values from your summation query fields in fields in the main record of your new table.

If in doubt about how to do this, look up a nice tutorial on query-building with Access.