help with setting up tables

[RBarber]

Dear all,

i have having such trouble setting up some tables in a new db, and am wondering if someone could give me some tips as I really don't know what I'm doing (I am new to this).

I have one main table, called Client Information. The PK is CaseNumber. I also have 11 other tables. The PK in each of these is called things like CasedetailsID, witnessinfoID, etc. Each of these tables contains the CaseNumber field (supposed to be from the Client Information table). The Client Information table contains the PK from each of the other tables.

The way I have the relationships set up at the moment is that the Client Information Table is linked to each of the other tables via the fields called casedetailsID, witnessinfoID, casetypeID, etc. The relationships are all one to many (the 'many' side being on the Client Info table, the 'one' side being on the related tables). There is obviously something incorrect about the way I have the tables set up, however, as when I try to enter data into the form, although the data gets stored in the individual tables, none of my queries or reports seem to be working (ie, I try to run a report, but it doesn't show any data).

any tips or advice regarding a good way to go about setting up tables and relationships would be very much appreciated.

 

[neileg]

Wrong way round. The secondary tables should hold CaseNumber.

 

[RBarber]

thank you. Should they be one to many or just one to one? and should I enforce referencial integrity?

 

[neileg]

thank you. Should they be one to many or just one to one?

I don't know. It depends on the facts. You don't need to worry about this, anyway.

and should I enforce referencial integrity?

Up to you. If you want to be able to delete the entry in the main table and have the subsidiary information deleted, too, then yes. I don't usually bother, but I'm not a proper programmer, just a hacker!