If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
relationships/ tables
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 |
#2
|
|||
|
|||
relationships/ tables
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/For...arted/201004/1 |
#3
|
|||
|
|||
relationships/ tables
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/For...arted/201004/1 |
#4
|
|||
|
|||
relationships/ tables
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/For...arted/201004/1 . |
#5
|
|||
|
|||
relationships/ tables
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/For...arted/201004/1 . |
Thread Tools | |
Display Modes | |
|
|