How can i solve the problem?

[moonrb]

Hi
I am making a database for a diagnostic lab.

I am using tables with field like below
TABLE1-DOCTORInfo
Name (value: Dacter x, Dacter y, Dacter z)
Address

TABLE1-DOCTOR x
FIELD-Percentege of TestX
FIELD-Percentege of TestY
FIELD-Percentege of TestZ

TABLE2-DOCTOR Y
FIELD-Percentege of TestX
FIELD-Percentege of TestY
FIELD-Percentege of Testz

TABLE3-DOCTOR Z
FIELD-Percentege of TestX
FIELD-Percentege of TestY
FIELD-Percentege of Testz

TABLE4-Test info
FIELD-TestName (value: TestX, Testy, TestZ)
FIELD-COST Of Test


TABLE5-Memo
FIELD-SrNo.
FIELD-NameOfDacter
FIELD-TestName1
FIELD-Percentege1
FIELD-TestName2
FIELD-Percentege2
FIELD-TestName3
FIELD-Percentege3

Here Every Doctor takes different percentage for Different Test (Example. TestX,TestY,TestZ) from the diagnostic lab.

I want When I select value for “NameOfDacter” and “TestName1” or “TestName2” “TestName3” in Memo Form, Percentege of that test should filled up from the respective DOCTOR table automatically Or by an update query.
How Can I do this? Plz help me…………..
Thanx In Advance.

[zmbd]

moonrb;

Respectfully, your database is very poorly designed at this stage.
Please read:> Database Normalization and Table Structures.

Please work thru:
MS Access 2010 Tutorials
This link also has two links to theory on database design. I highly recommend working thru both of those links.

[Patrickwwevans]

I think I would concur with the last response if it is true you have separate table for each doctor. Fundamentally, I think what you are trying to do is this: Based on a test, provide a specific percent value to a doctor where for each test, the percent can change.
If the above business problem is stated correctly, then I would think you would need the following. First and obviously a table for the physicians where you have a unique doctor ID and data on the doctor that may be helpful in other business processes. Then you would need a table for the tests with testID, test name, etc. Then a look up table containing DrID, TestID, and Percent Value. Then when you select a test, it could then refer to other queries to provide the total assigned doctors with the numeric percent value for a particular test. This is high level of course and you would need to know more than basic Access to really do a good job. In essence though you need to build the ability to have a relations to a doctor to a test in a table that is many to many