Update table using a two criteria query

[Oceaneng]

I'm sure this is a fairly easy and basic operation, but I'm learning ms-access by youtube and have no clue where to start researching this. Ultimately I want to take two fields from my main table, use those fields to pull the primary key from a second table and return that value to a third field in my main table.

Specifically:


I have a table with two fields, each field can have 5 values (1,2,3,4,5) and (A,B,C,D,E) . I have another table with a unique key (RCS) for each permutation of number and letter: {[1,A,1]; [ 2,A,2]; [3,A,3]... [24,E,4]; [25,E,5]}.


So far I've been able to take the input from a form and run a query to spit out the key, so entering B and 5 returns 16, but this only returns the active field in the form, which is fine if I could record that result in the main table. For learning purposes I'd also like to be able to run a query and get the same result for every record I have.

Maybe my relationships are screwy, but when I try to run a query with var1, var2, and the key field for the table, I end up getting the every RCS that applies to var1, almost as if var2 is not "filtering" the query.

[Preston]

You need that key in your first table to point at the appropriate records. Altrnatively, if the data looks exactly as you described or similar, you can concatenate the two fields in a subquery, and relate that concatenated field to the second table.

[Oceaneng]

Aha, not sure if it's what you meant, but I tried redoing the relationships in the query itself and I got what I was looking for. Now to learn how to write the query results into the field I want on another table. Is that what the append query is intended for or is there an easier way?

[Preston]

It's inefficient and risks data integrity to store calculated values. The only common exceptions I've found to this rule are if you are capturing a point in time snapshot of a calculation, or you are building up a dataset for reporting, and the static table of results will work much faster than the underlying query.

To answer your question, that is what the append query is for, but you should bear in mind the above.

[Oceaneng]

Right, I figured this was the quick and dirty way of doing things, but it also seemed like the appropriate stepping stone.

Ultimately what I want is to have a form where the user can pick those two variables for each record and the form will update right away with what the queried value is so that the user can see the value. Is it best to just run that as a query each time a record is opened and leave the values kind of in flux? When I run a report on these values I suppose I could do that as a query too. Sorry for all the questions, all my programming experience has been mathematical modeling in Matlab

[orange]

You can get every permutation of values in 2 fields using a Cartesian query/join. (info at this and this)
If you take the output of that query and add records to a Table that includes an autonumber field as PK, you should get a table with 3 fields --- field1 value, field 2 value and unique autonumber pk.