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

 

[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!)