linking tables

[RBarber]

Dear all,

I have a question which I think is probably very straight foward for those who know about these things. I have just created a new database, but there are obviously some problems with the design, because when I enter information into a form, I don't seem to be able to access the information through queries or reports.

I have 11 tables, and one single form (I created the tables, and then used the wizard to create the form). The main table is called 'Client Information,' and the key in that table is the CaseNumber. They key in every other table is called something along the lines of 'ClientDetailID,' 'IncidentDetailID,' etc. When I look at the relationships between the tables, the 'ID' field in each table is linked to the 'CaseNumber' field in the Client Information table. However, I don't have any fields actually duplicated between the tables. Do I need to do this? (ie, should I have the 'CaseNumber' field appear in each table, and then the 'ID' field from every other table appear in the 'ClientInformation' table?)

thanks so much, this is probably a really simple problem but I don't think I really understand the whole relationships and linking tables thing.

 

[neileg]

Hard to be 100% definitive on the basis of the information supplied. However, it looks like a good bet that you need to hold CaseNumber in each table, in addition to the tables own primary key (PK). When you hold a PK from another table inorder to link the records, it is known as a foreign key (FK).

 

[RBarber]

Hi Neil,

thanks for the reply. I did that, but am still stuck (ie, I enter information in the forms, and then I try and run a query or report, and no information comes up, which makes me think the tables aren't all linked together properly).

I now have a field called CaseNumber in each table, which is a lookup field from the CaseNumber field (PK) in the Client Information table. Each other table also still has a primary key (incidentdetailID, victiminformationID, etc), which is an auto number. The ID field in each case is linked to the CaseNumber (PK) in the Client Information form. Is there something obvious that I'm doing wrong here? Anyone??

thankyou!

 

[neileg]

OK, you are new to this, but then so was I a couple of years ago.

Firstly, don't use the lookup feature. This is OK for very simple stuff but rapidly gets in the way.

You need to enter your data in forms. You can link the tables together in a query and base your forms on that. The relationship between the tabes is displayed in the query. In the form, you will have a header which holds the details from your main table, and a details section that holds the data from the other tables. This will ensure that the CaseNumber field is automatically populated in the other tables when you enter information.

Alternatively, you may need a form/subform setup. There is a wizard that Access provides that automatically links the ID fields to achieve the synchronisation.

Have a browse of the sample database section in thes forums and see how these setups are achieved.