relationships/ tables

[Kathryn]

Hi

Using Access 2007.

Despite lots of reading around the subject, relational tables seem to be a
real block and this is causing issues with trying to set up forms that work
:-( I have set up forms that have taken data from two tables and that has
worked reasonably OK. However, I realised that I had 2 many-many tables and
that this might be the cause of some of the problems I encountered (data not
showing up in forms) - so I added what I hoped would be a junction table that
would resolve the issue.

The scenario:
tblContacts - includes contact details for mailing list, attendees of
events, billing contacts. Contacts may have no links with events - or have
attended several.
tblEvents - includes all details for organisation of events. Events may link
to several attendees.
tblBilling - the junction table. This includes the ContactID and the EventID
- both PKs in their own tables and then things like Invoicing. Each bill will
relate to one Contact and one Event.

The Contacts and Events were based on the templates that come with 2007 -
though the fields have been changed quite a bit. I have played around with
the database as I have been learning so will need to review it before
embarking on the final (hopefully) version, but at the moment the Tables look
like this:

tblContacts
Contact ID - AutoNumber
Title - Text
FirstName - Text
LastName - Text
Company - Text
JobTitle - Text
etc for Add1-4/ E-mail address (Text)
Telephone numbers, etc (Numbers)

tblEvents
EventID - AutoNumber
DateofEvent - Date/Time
EventTitle - Text
etc for Start/ end times/ Rooms required/ Catering
It also had ContactID but then I ended up with 20,000 records (presumably
evey contact attending every event!)

tblBilling
BillingID - AutoNumber
EventID - Number
ContactID - Number
CostperPerson - Currency
etc for Invoiced (Yes/No)/ DateInvoiced, etc.

The Relationships (set up using the tools in Database Tools) are
Contacts (ContactID)1:Billing (ContactID) Many
Events (EventID) 1:Billing (EventID) Many
(This feels as if it should be different but I can't see how to change it!)

I've done things like Rule validations and they come up as OK.

One error message I get when I try to add a field (say Contact.Title to
form:Event Details) is that "To complete this operation Access must modify
the RecordSource property of the current form... that it will create a new
query and the form will no longer be based on the Current Events query..."
This happens even if I set up an entirely new form with no relationship at
all to the Current Events query!

Hoping that something really obvious is the problem and that it's easy to
sort out! And even if I have to start afresh with the final version of the
database, I would really like to get to grips with where I'm going wrong with
relating tables!

Many thanks

 

[KenSheridan via AccessMonster.com]

The relationships between tblBilling and tblContacts and tblEvents
respectively are fine. A so-called 'junction' table models a many-to-many
relationship, between tblContacts and tblEvents in this instance, by
resolving it into two one-to-many relationships, which is exactly what you
have here. It does assume of course that each contact's only relationship
with an event is a billing one, so you could not have a situation where
multiple contacts from one company attend an event, but there is only one
billing relationship with the company. This would require the model to be
extended, so that the relationship between contacts and events is an
'attendance' one, while that between events and companies is a 'billing', one
which would mean having a Companies table and separate tables to model the
'attendance' and 'billing' relationships.

When designing a database you should really not think in terms of tables
initially, but in terms of the 'entity types' (contacts, companies etc) and
the relationships between them (attendance, billing etc). Then create the
tables to represent these entity types; a relationship type is really just a
special kind of entity type.

So, while your basic model is essentially sound, you have made one important
design error by having separate columns (fields) for the multiple email
addresses and phone numbers per contact. These should be separate rows in
related tables in which there is a foreign key ContactID column. So a
contact with two email addresses would have two rows in an emails table, one
with three would have three rows. Similarly with phone numbers you'd have
separate rows in a related table for each number per contact. This table
would also have a PhoneType column to identify the type of number in each
case.

Having Company as a text column in the contacts table is fine provided that
(a) all company names are distinct and (b) you have a separate companies
table with one row per company and Company as its primary key. You can then
relate the tables on Company and enforce referential integrity. This ensures
only valid company names can be entered in the contacts table. If company
names are not distinct you should have a numeric CompanyID column in the
contacts table and a numeric CompanyID primary key column for the companies
table.

The error you are experiencing with the form appears to be due to your
putting the cart before the horse. If you need to include a new field in a
form then, if the field does not yet exist in the table you must first add it
to the table, and then amend the form's RecordSource so that it includes the
field. You should then be able to add a control bound to the field to the
form with no problem. However, the example you have given (Contact.Title to
form:Event Details) suggests that you might be going about it the wrong way.
As each event will include many contacts the usual set up would be to have a
form (in single form view) based on the events table and within this form a
subform based on the table which models the relationship between events an
contacts, tblBilling in your case. The subform would be linked to the parent
form on EventID and would normally be in continuous forms view. To show each
contact attending, along with their job title and company for instance, the
subform would include the following controls:

1. A combo box bound to the ContactID field and set up as follows:

Name: cboContact

ControlSource: ContactID

RowSource: SELECT ContactID, JobTitle, Company, FirstName & " " &
LastName FROM tblContacts ORDER BY LastName, FirstName;

BoundColum: 1
ColumnCount: 4
ColumnWidths: 0cm;0cm;0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
three dimensions are zero to hide the first three columns.

2. An unbound text box set up as follows:

Name: txtJobTitle

ControlSource: =cboContact.Column(1)

The Column property is zero based, so Column(1) is the second column, i.e the
JobTitle, in the cboContact controls RowSource.

3. An unbound text box set up as follows:

Name: txtCompany

ControlSource: =cboContact.Column(2)

In this case Column(2) references the third column, Company.

The unbound text boxes will show the job title and company of the contact in
the combo box, and if you add a new record in the subform by selecting a
contact in the combo box their tile and company will immediately appear in
the text boxes.

Ken Sheridan
Stafford, England

 

[J_Goddard via AccessMonster.com]

Hi Kathryn -

Your table structure is fine, and the relationships are correct - each
contact can be in many billings, and each event can be in many billings.
Your tblBilling is the junction table that resolves the many-to-many issue.

Now - for the problem with the form. What data are you adding with the form,
and what is it's record source? Just from what you have told us so far, you
should not need a query for the record source.

Tell us a bit more, please.

John

 

[Kathryn]

Thanks so much Ken

This is really helpful. It has been pretty much a standing start with Access
and learning as I go and I got the feeling there was some carts before horses
stuff going on! I'll take a day or so to work through your reply and tie it
in with the database and see how we get on!

Many thanks

 

[Kathryn]

Hello John

Many thanks (sorry for delay in responding - I was working away from the
desk yesterday!)

Thanks for the note about the tables .. Ken has also suggested some ideas
which I'll explore and see how they go.

For the form...
This was intended to be a one-stop form for the receptionist to use - which
I now realise was probably not that wise!
Basically, I had one form with everything about an event - from the contact
details for the person making the booking (who may or may not be the same
person as the one who pays the bill) - to which rooms they would be using in
our Centre - what equipment they needed - catering options - and billing
information. The data (I thought!) would then feed into (and from?) the
appropriate table - as they were the fields I'd added to the form. Up to a
point this worked fine - the problem was when it stopped working and I
couldn't see why. That was why I decided to relook at the tables/
relationships - and also found that the form's recordsource was based on a
built-in query from the Events template I had used to start me off (For info:
the Current events query - which I don't use as I've devised queries that
suit us better - but hadn't deleted and had not - knowingly! - set as the
recordsource!)

I have now separated out the Contacts element so that this "looks after" the
various contact information. A Contact may be the organiser of an event - or
the person who pays the bill - or someone who has come to an event - or
someone on our mailing list who hasn't yet come to anything but wants to be
kept informed. At this stage I haven't made any distinction between them
though I will (at least in terms of the opt-in to general mailings).
Basically - the Contacts table/ form lists contact details.

The Events form... ideally I would still like be one form (to save the
receptionist having to go to different places to put in the information). My
thought was for her to be able to fill it in from the paper/electronic
version that comes in with all the information about an event going into one
form - including the details of the organiser and bill-payer. (As we get
repeat business, I was hoping that Access would be clever enough not to keep
adding Contacts each time they book - hope that's the case!) I thought this
would then fill the Contacts/Events/and now, Billing tables - which would
then provide the information for queries and reports.

I don't know if that's any help? It was as if I had done something but I
cannot retrace my steps and see what. A fresh start might be the best way -
unless something I have said rings any bells.

My aim is to have something very straightforward for the receptionist/
others to use ... if they have to put one thing into one form and another bit
in another (apart from the odd adding a new Contact to the Contacts form) I
think they will feel that it's a waste of time and prefer to stick with paper
copies in a ring-binder (which works - but doesn't pull out the queries/
reports, etc which I can see will make life easier in the long run!)

Thanks for any suggestions you can offer... even if it is to go back to a
drawing board! (Better now with only around 300 records than when we have
more!)