Problem with keys?

[Guest]

Hi all

I have several tables that I need to relate.

Customers - holds address info for customer offices and is indexed by a
unique customerID, which is set as a primary key.

Contacts - holds contact details for contacts at the customer offices,
indexed also by customerID but duplicates are allowed as we have several
contacts based at the same location. No primary key set.

Notes - holds notes that engineers make about customer sites they have
visited. Indexed again by customerID but as with Contacts there may be
several notes for the same location so primary key is set for this table
either.

How do I get them to relate properly? I have set relationships up between
the tables, but I want to create a form with 2 subforms and I can't get the
form design wizard to allow me to do this. It will, however, let me create a
form with a single subform, so either Customers with Contacts or Customers
with Notes.

I need Customers with Contacts AND Notes though and am assuming there is a
problem with either keys or relationships somewhere!

I would be extremely grateful if anybody can point me in the right direction!

Many thanks!!

Ali

 

[Someone]

Hi Ali

That should be straightforward if you have the relationships set up like you
say you do. In the form design view, just select the subform/subreport
button in the Control Toolbox and click on the form to create it. Follow
the prompts to determine how to relate the subform to the form (using the
IDs you mentioned).

What do you mean when you say it won't let you? What's going wrong?

M

 

[Guest]

Hi M

What happens is this - I select all the fields I want to include from the 3
tables, and then it goes straight to asking me how I want the form layout to
appear, instead of asking me whether I how I want to view my data before
going on to asking me how I want the layout to appear etc (as it still does
if I only use a combination of customer/contacts or customer/notes.)

Ali

 

[Someone]

Hi Ali

That's because you haven't selected the recordsource for the actual form.
If you start a new form using Design View, there's a drop-down box that
allows you to choose your recordsource. Choose the table or query you need
to relate it to.

Now this is done, when you add on a subform, the wizard will ask you how you
want to relate the subform to your form's recordsource.

Does this help?

M

 

[tina]

comments inline.

Hi all

I have several tables that I need to relate.

Customers - holds address info for customer offices and is indexed by a
unique customerID, which is set as a primary key.

Contacts - holds contact details for contacts at the customer offices,
indexed also by customerID but duplicates are allowed as we have several
contacts based at the same location. No primary key set.

the CustomerID field in this table is called a "foreign" key, because it
matches the primary key of the Customers table. this is the correct way to
relate a "child" table such as Contacts to its' "parent" table such as
Customers.

BUT, the Contacts table should also have a primary key field. the easiest
way to set this up is to add a field to the table, i'll call it ContactID,
and set the data type to Autonumber.

Notes - holds notes that engineers make about customer sites they have
visited. Indexed again by customerID but as with Contacts there may be
several notes for the same location so primary key is set for this table
either.

again, the CustomerID field in this table is a foreign key, which is fine.
and again, this table should have its' own primary key field, which you can
add as described above.

How do I get them to relate properly? I have set relationships up between
the tables, but I want to create a form with 2 subforms and I can't get the
form design wizard to allow me to do this. It will, however, let me create a
form with a single subform, so either Customers with Contacts or Customers
with Notes.

don't get hung up on the wizard. if you've set the table relationships
correctly in the Relationships window, including enforcing referential
integrity, then it's easy enough to add multiple subforms to a form. suggest
you create a form bound to either the Contacts table or a query built on the
Contacts table (an quick way to get a basic form set up is to use the
AutoForm button on the database window's toolbar. then you can tweak the
form to suit your needs.) create another form bound to either the Notes
table or a query built on the Notes table. create a main form bound to
either the Customer table or a query that is built on the Customer table.
note that you should *not* include other tables in the record source of any
of these forms - just the table specified.

in the main form's design view, use the Toolbox toolbar to add a subform
control. in the Properties box, set the subform control's SourceObject
property to the name of the Contacts form (select it from the droplist).
Access should set the LinkChildFields and LinkMasterFields properties for
you. if it doesn't, then set the LinkChildFields property to the name of the
foreign key field in the Contacts table. set the LinkMasterFields property
to the name of the primary key field in the Customers table.

repeat the above process to create a Notes subform in the main form.

hth

 

[Guest]

Fantastic! It works now!!

I'm not sure why it wouldn't work following the instructions in the help
file, but creating a form with a subform, and then adding the additional
subform to it is working fine now

Thank you - I can go to bed a happy girl now!!

Ali

 

[Someone]

Why have a primary key for the Contacts when more than one contact can exist
for the master record?

 

[Someone]

Marvellous - I'm glad to hear that

Night night!

 

[tina]

each record in each table should have a unique identifier, so that the
system can identify specific records in the table. this is a basic rule in
relational database design. if not setting a primary key in a table has not
caused problems for you yet, it will - probably sooner than later.

hth

 

[Someone]

I've nearly finished a database. The main table, amongst others, is
connected to a contacts table in a one-to-many relationship, with enforced
referential integrity and cascaded deletes, because more than one contact
could be associated with its parent record. If I used a primary key on the
Contacts table, I would not have been able to achieve my goal.

I don't see how you would have achieved this with a primary key in the
parent and child.

 

[Rick Brandt]

I've nearly finished a database. The main table, amongst others, is connected
to a contacts table in a one-to-many relationship, with enforced referential
integrity and cascaded deletes, because more than one contact could be
associated with its parent record. If I used a primary key on the Contacts
table, I would not have been able to achieve my goal.

You are mistaken. The ContactID wouldn't be the primary key in the parent
table. It would be the Primary Key in the Contacts table.

Every table gets a Primary Key.

 

[tina]

If I used a primary key on the

Contacts table, I would not have been able to achieve my goal.

that's incorrect. a primary key in the Contacts table will have no effect on
the primary/foreign key relationship between the Customer table and the
Contacts table. example:

tblCustomers
CustomerID (primary key)
(other fields that describe a customer)

tblContacts
ContactID (primary key)
CustomerID (foreign key from tblCustomers)
(other fields that describe a contact)

relationship set as
tblCustomers.CustomerID 1:n tblContacts.CustomerID

the ContactID primary key field in tblContacts has nothing to do with
tblCustomers, and it does not affect the CustomerID foreign key field in any
way. it's purpose is to uniquely identify each record in tblContacts. the
purpose of the CustomerID foreign key field is still to relate each record
in tblContacts back to a specific record in tblCustomers, it still allows
duplicate values (because it is NOT a *primary* key), and it still works
exactly the same.

suggest you read up on table design to better understand the role of primary
keys and foreign keys in tables and relationships. see
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
for an excellent list of resources.

hth

 

[Someone]

Interesting. There is no other field that I could use as a primary key
unless I make one up.

Based on how I have it set up currently, what could go wrong?

 

[tina]

hon, i'm not going to describe the various scenarios where lack of a primary
key will cause problems in the database. i can only suggest again that you
read up on relational design principles, and build your database to those
standards for best results.

as for "making up" a primary key, that's very common; it's known as a
surrogate key. and in fact that is the purpose of the Autonumber data type,
to provide an automatically generated surrogate primary key - either for the
developer's convenience, or in cases where a table literally has no
"natural" primary key (that is, a field or combination of fields that serve
to uniquely identify each record in the table).

hth

 

[Someone]

Hi Tina

Thank you for your help. It's been useful and I've learnt something new

I've changed the setup of the database I mentioned so that two tables
related to the main (that were set up in the way I mentioned) now have a
primary key and use a new field to relate the tables together.

Thank you again
M

 

[tina]

well, you're welcome, but note that you didn't need to change the fields
that link the Contacts and Notes tables to the Customers table, those
foreign key fields and links were fine as you originally configured them.

hth

 

[Someone]

I'm confused now.

Unless I'm not understanding you correctly, you said previously: "if not
setting a primary key in a table has not caused problems for you yet, it
will - probably sooner than later.", but now you seem to be saying that what
I did originally was fine.

M

 

[tina]

re-read my previous posts. i explained as clearly as i know how that the
foreign key field in the Contacts table and in the Notes table ARE NOT
PRIMARY KEY FIELDS and that adding a primary key field to each table DOES
NOT AFFECT THE FOREIGN KEY FIELDS IN ANY WAY, SHAPE, OR FORM. once again, i
strongly recommend that you read up on primary keys and foreign keys and
their separate roles in properly designed tables. and again, here is a link
to a number of resources:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

i really hope that you'll do as i suggest, and i wish you luck in your
project.

 

[Someone]

Tina

You obviously know your stuff and to you, Access is easy and you can put
some words together and hope that the other person will understand 'just
like that'. But please, two of your responses have come across as
patronising, just because I know less than you. I am trying to learn, and I
do appreciate your efforts in giving me that knowledge. I've made an
interpretation from what you said, and, if it's wrong, that certainly
doesn't mean that I am at fault; having said that, I too could well have not
explained myself clearly, or you could have misinterpreted what I have said.
I apologise if that's the case. I do appreciate your help but there's no
need to stress out at me for asking questions - questions will inevitably
arise. I hope we don't fall out though

Thank you
M

 

[tina]

if i sounded patronizing, i do apologize for that. if i sounded frustrated,
that is the correct interpretation, because i was. when you keep coming back
to the same point, with the same observations, i can only assume that you
ignored my advice to use the link that i provided, and study relational
design principles so that you can better understand my explanation. at that
point, reiterating the same information again is a waste of my time, and as
i said, frustrating. whether you choose to further your understanding of
database design is up to you, of course, and i do wish you the best of luck.