Data to grid

[JoeAcess]

Hi everyone

I am using access 07. i have a form. The form contains patient details. the pat id is the primary key. so one form per patient kind of thing. It also has two fields called test and test result. for a patient there might be atleast 3-4 test. so i would like each test to be entered. i want each of these that is entered to be on a grid. this grid should also be in the same form and the field should be cleared so that new test can be entered. i should also be able to delete a row on that grid. How can this be done.

Thanks a lot in advance

 

[coyote]

You need three tables.
One for the patient details e.g PatID, PatientName, Address etc.
Another table for tests with the testID as the Primary Key and test details
The third table will be the junction table to connect the patients and test tables. Its primary keys are the patientID and TestID.
Do the relationships and then in your form create a main form with a subform.

 

[JoeAcess]

Thanks you for helping, since i am new to it, i was wondering whether you could tell me the meaning of a junction table. Also if the patient have two of the same test how is it possible to add as it is the primary key and it wouldn't allow you to duplicate. also what kind of conncetion should i give between the two tables. If its too much to explain you could even direct me to a link.

thanks in advance

 

[jal]

Also if the patient have two of the same test how is it possible to add as it is the primary key and it wouldn't allow you to duplicate.

You might want to store the possible KINDS of tests in one table and then, in another table, the actual tests

tblKindsOfTests
tblTestsTaken

i was wondering whether you could tell me the meaning of a junction table

I'm guessing this refers to a common problem in Access - it doesn't like to bind a table to 2 different objects. Therefore if the table is already bound to the textboxes on a form, and then you try to bind in a subform (for example using a join query), then the subform will fail. However if you create a JOIN table to hold the results of the join query, you could bind the subform to that instead (I'm not sure the best way to do it, though).

. . also what kind of conncetion should i give between the two tables. If its too much to explain you could even direct me to a link.

Just create join queries as needed, meaning that if you run a SELECT query only to find you also need data from a second table, then add a JOIN clause to the query (or add a second table using the wizards which will then add the JOIN clause for you). JOIN on a column common to the two tables joined:

SELECT * FROM Customers as C
INNER JOIN Orders as O
ON C.CustID = O.CustID