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

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

Kathryn wrote:
>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

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201004/1

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 wrote:
>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

-- 
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201004/1

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


"KenSheridan via AccessMonster.com" wrote:

> 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
> 
> Kathryn wrote:
> >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
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201004/1
> 
> .
> 

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



"J_Goddard via AccessMonster.com" wrote:

> 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 wrote:
> >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
> 
> -- 
> John Goddard
> Ottawa, ON Canada
> jrgoddard at cyberus dot ca
> 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201004/1
> 
> .
>