Filling in a column using data from another table

[RawToast]

I have a list of 4300 clients. The clients' personal information and ID numbers are listed in the table BasicInformation. Another table, DiseasesAndValues lists 17 diseases and a "scheduled value" (long story short - an amount of money) for each disease. Each client has one of these 17 diseases. I would like to have a column in the BasicInformation table which shows the scheduled value for the disease that a particular client has by going back to the DiseaseAndValues table and checking to see what the scheduled value of their disease is. Alternatively, I could use a query that could enter in these values with a click of a button. Any ideas?

[June7]

Not really understanding what the issue is.

Each client will have only 1 associated disease? The clients table has a field for the disease ID? Build a query that joins the tables.

[ssanfu]

I would create a third table
"Info_Disease"

Fields
Info_DiseasePK (autonumber)
BasicInformationFK (Number - Long)
DiseaseAndValuesFK (Number - Long)

Link the primary key field from "BasicInformation" to the foreign key field "BasicInformationFK" in table "BasicInformation".
Link the primary key field from "DiseaseAndValues" to the foreign key field "DiseaseAndValuesFK" in table "DiseaseAndValues".

Have a form in continuous view, The record source would be with table "Info_Disease".
Have two combo boxes, one bound to the field "BasicInformationFK", the other bound to "DiseaseAndValuesFK" (maybe named cboBasic and cboDisease.

The row source for cboBasic would be a query based on the table "BasicInformation".
The row source for cboDisease would be a query based on the table "DiseaseAndValues".

Set the column widths appropriately

Without knowing the field names in each table, I can't be more specific.

Can one client have more than 1 disease??