Expressions between two tables

[ExcelleradRob]

I am new to Access and have been teaching myself. I currently have two tables with multiple columns of data. Each table has one particular criteria I would need to search for and three or four other corresponding fields that I need to do calculations with. I am attempting to create a macro/form/something? to be able to enter in a patient ZIP code and treatment code (CPT), items that are in two different tables. Once those variables are provided, I want the program to find the matching ZIP or CPT code in the respective tables and provide corresponding data which will then be used in an expression to calculate a reimbursement fee. Below is the formula I will need to calculate. All field names are the same as what you see in the formula.

Reimbursement (no field yet) = [(Wrvu*Wgpci)+(PErvu*PEgpci)+(MPrvu*MPgpci)]*CF

In one table, a specific zip code corresponds to Wgpci, PEgpci and MPgpci

In a second table, a specific CPT code corresponds to Wrvu, PErvu, MPrvu and CF. There are also two modifying components for each CPT code so basically each CPT is shown 3 times in each table.

There are some additional fields which correspond to either CPT or ZIP (in respective tables) and they are not relevant or necessary. I copied these tables from two excel spreadsheets from a US government healthcare website.

I would appreciate any insight from the folks on here. It would seem like this should be something Access could do, I just don't know how to do it. I have played around with Forms, Expressions, Queries and Macros and I am still clueless. I have attached a few screen shots of what I have set up since the layout may be hard to understand from my explanation.



Thank you in advance for any input you may have.

[ssanfu]

Are the two tables related? If so, what field?

In a second table, a specific CPT code corresponds to Wrvu, PErvu, MPrvu and CF. There are also two modifying components for each CPT code so basically each CPT is shown 3 times in each table.

If there are three records, how do you decide which record to use to do the calculations?

You could open two recordsets and get the values from the recordsets to do the calculations..... but 3 CPT records????

[June7]

You have a table for PatientInfo with ZIP code? You have another table to save record with CPT and PatientID (a 'junction' table)?

Try building query that joins PatientInfo and [CPT RVU Values] to CPT_PatientID junction table and [GPCI by ZIP] to PatientInfo.