Relationships and Primary keys

[fazerdave]

I am a bit confused by my relationships and primary keys.

My database contains customer details and each customer can have one or many
maintenance contracts. Each maintenance contract can have any number of call
outs.

I have set up 3 tables, customers, maintenance and callouts - each table has
a primary key ie customers ID etc. I have added the 'customer ID' field to
the maintenance table and the 'customer ID' and 'maintenance ID' to the
callout table.

My ideal result would be that when i select a customer it will show me the
relevant maintenance contracts for that customer and also if I select a
maintenance contract it will show the call outs etc.

I can't seem to get this to work so I must be doing something wrong or have
missed a vital relationship. Please could someone help?

 

[Duane Hookom]

I would expect the maintenance table to have a primary key like MaintenanceID
and a foreign key to the customers like CustomerID. The callouts table would
have a foreign key only to the Maintenance table based on the MaintenanceID.
I would not put the CustomerID in the callout table.

 

[Evi]

Using Duane's structure,
TblCustomer
CustomerID (primary Key)
CFirstName
CAddress
other fields only about the customer

TblMaintenance
MaintenanceID (primary key)
CustomerID (foreign key)
MStartDate
other fields only about the maintenance contract


TblCallOut
CallID
MaintenanceID
CallDate
Other fields only about the Call out

You can achieve what you want with a form and 2 subforms.
The main form has the customer
The first subform has his maintenance contracts and is linked to the main
form by CustomerId
A third subform, linked to the second subform by MaintenanceID, shows the
Callouts to each maintenance contract as you click on them. This can be done
with the correct table structure.

I suspect that you are trying to avoid creating forms by 'forcing' Access to
create datasheets and lookups in your tables. Desist rash youth, ere tis too
late! You will really limit what you can do. Tables are not forms, even if
Access tries to convince you they are.
Evi

 

[fazerdave]

Hi Evi

Thanks, I have done everything you said and it sort of does what I want.
When I open the customers form you can see the subform of maintenance
contracts, but when I go to the maintenance form (with the callout subform) I
can't tell which customer the maintenance contract relates to. (This is also
the same if I just wanted to see a report of the call outs, I can't see which
customer it belongs to let alone the maintenance contract.)

Do I need to put the 'customer name' field in the maintenance table, and if
I do surely that will then appear in the maintenance subform of the customer
form which I don't want.

Any ideas?

 

[Evi]

Definitely DON'T put the customer name into the Maintenance table.
Link CustomerID in the Relationships window From TblCustomer's CustomerID TO
TblMaintenance's CustomerID.
Because CustomerID is in the maintenance table and is linked to CustomerID
in the Customer table, all the fields in the Customer Table are available to
the Maintenance Table. That's what a relational database is about.

To get the advantage of this:


In Design View of the Maintenance Form, Click on the Properties button..
Click on the Data tab. Click just left of the white box after the line that
says Record Source.
You will probably be asked if you want to Evoke the Query Builder. Choose
yes.
In the query that appears. Drag all the fields from TblMaintenance into the
Grid.

Click the Add Table button and choose TblCustomer. Ensure that the
CustomerID fields are linked (have a line going from one to the other)

Add the Customer Name (and Address fields, if you wish) to the grid. DON'T
add CustomerID from the Customer table (you will have that from the
Maintenance Table)

Click the Save Button if you wish. You can use this query for your report
too.

Back in the Form's Design View, click the Field List button.
Drag the Customer name fields (and any other customer fields which you want
to see) into your main form.

You won't want people changing these names and addresses and associating the
wrong customer with the wrong maintenance contract so next you are going to
lock those fields so that they are Read Only. Click on the Fields. In the
Propreties box choose the Data tab again. Next to Locked choose Yes

Another way of displaying a linked field's data - if you wanted to change
the customer for another one, would be a combo box but you will see why that
wouldn't be a good idea here.

Evi