Tables & Relationships
in [Getting Started]
|
Prev: Linking fields in different table in same database
Next: Jeanette, thank you for your help. I thought of doing it
From: KenSheridan via AccessMonster.com on 27 Jan 2010 17:29 Kim: You are missing the point which Dorian was making. As each contact can presumably only work for one company, once you know who is the contact you then know the company. It is said that contact 'determines' company. So if you have columns in the Jobs table for both the company and the contact, the company column introduces redundancy into the table, so it is not correctly normalized as it includes a 'transitive functional dependency', Job--- >Contact---->Company. This leaves the table at risk of inconsistent data as there is nothing to stop a row in the table including a contact and company for whom the contact does not work (as you have found out). So you should only have a column for the contact in the Jobs table, not the company; the integrity of the data is then protected It is still possible when entering a row in the Jobs table to do so by selecting a company first, then a contact, by using correlated combo boxes, the one to select the company being unbound. As regards the correlated combo boxes how you set these up depends on whether the form is in continuous forms view or single form view. If the former, and you are using 'surrogate' numeric keys for the tables (ContactID in the example below as names are unsuitable as 'natural' keys being legitimately duplicated) then you cannot use combo boxes alone as controls in rows will go blank if you select a different higher level value (Company in the example below in the other). The solution is to use hybrid controls by superimposing a text box on each combo box so that it looks like a single combo box control to the user. Single forms are much simpler and don't require the hybrid controls, merely the combo boxes A. For continuous forms here's an example of a Jobs form which includes correlated combo boxes for Company and Contact, while maintaining the Jobs table in Third Normal Form by having an ContactID column, but not a Company column: 1. An unbound cboCompanies with a RowSource property of: SELECT Company FROM Companies ORDER BY Company; and an AfterUpdate event procedure of: Private Sub cboCompanies_AfterUpdate() ' requery Contacts combo box to ' show Contacts with selected Company Me!cboContacts.Requery ' clear Contacts combo box Me!cboContacts = Null End Sub 2. A bound cboContacts combo box with a ControlSource property of ContactID and a RowSource property of: SELECT ContactID, Contact FROM Contacts WHERE Company =Form!cboCompanies ORDER BY Contacts.Contact; The ColumnCount property is 2 and the ColumWidths property is 0cm;8cm to hide the first column. Note the use of the Form property in the above rather than a full reference to the form. This is possible as both controls are ion the same form. 3. An unbound text box txtCompany superimposed of cboCompanies, with a ControlSource property of: =GetCompany([cboContacts]) 4. An unbound text box txtContact superimposed of cboContacts , with a ControlSource property of: =GetContact([cboContacts]) Along with the other code the form's module in total would thus be: ''''module starts'''' Option Compare Database Option Explicit Private Function GetCompany(varContactID) ' get Company for current value of ContactID field If Not IsNull(varContactID) Then GetCompany = DLookup("Company", "Contacts", "ContactID = " & varContactID) Else GetCompany = Me.cboCompanies End If End Function Private Function GetContact(varContactID) If Not IsNull(varContactID) Then GetContact = DLookup("Contact", "Contacts", "ContactID = " & varContactID) End If End Function Private Sub cboCompanies_AfterUpdate() ' requery Contacts combo box to ' show Contacts with selected Company Me!cboContacts.Requery ' clear Contacts combo box Me!cboContacts = Null End Sub Private Sub cmdClose_Click() DoCmd.Close acForm, Me.Name End Sub Private Sub Form_Current() If Me.NewRecord Then Me!cboCompanies = Null Else Me!cboCompanies = GetCompany(Me!cboContacts) End If Me!cboCompanies.Requery Me!cboContacts.Requery End Sub ''''module ends'''' Basically the way it works is that when you move focus to one of the combo boxes by clicking on its arrow its text box part becomes visible and its list drops down. When you move focus off the control the superimposed text box becomes visible. The functions get the text values for these by looking them up from the relevant table on the basis of the corresponding key value which is the hidden value of the bound cboContacts combo box. B. For single form view you can dispense with the two text boxes txtCompany and txtContact and the module is simpler: ''''module starts Option Compare Database Option Explicit Private Function GetCompany(varContactID) ' get Company for current value of ContactID field If Not IsNull(varContactID) Then GetCompany = DLookup("Company", "Contacts", "ContactID = " & varContactID) Else GetCompany = Me.cboCompanies End If End Function Private Sub cboCompanies_AfterUpdate() ' requery Contacts combo box to ' show Contacts with selected Company Me!cboContacts.Requery ' clear Contacts combo box Me!cboContacts = Null End Sub Private Sub cmdClose_Click() DoCmd.Close acForm, Me.Name End Sub Private Sub Form_Current() If Me.NewRecord Then Me!cboCompanies = Null Else Me!cboCompanies = GetCompany(Me!cboContacts) End If Me!cboCompanies.Requery Me!cboContacts.Requery End Sub ''''module ends'''' Watch out for any lines which your newsreader might have split over two lines in the above. If you'd like a copy of the demo file from which the above code is adapted, mail me at: kenwsheridan<at>yahoo<dot>co<dot>uk For searching purposes you would simply need to base search on a query which joins Job Numbers and Contacts (the Companies table is not needed as the Contacts table includes a Company column as a foreign key). The search can be via an unbound dialogue form with combo boxes cboCompany and cboContact. The RowSource for the former would be: SELECT Company FROM Companies ORDR BY Company; The RowSource for the latter: SELECT ContactID, Contact FROM Contacts WHERE Company = Form!cboCompany OR Form!cboCompany IS NULL ORDER BY Contact; In the AfterUpdate event procedure of cboCompany set to Null and requery cboContact with: Me.cboContact = Null Me.cboContact.Requery From the dialogue form open a form or report based on a query which references the two combo boxes on the form as parameters, testing for Null in each case to allow a search to be made by a company, a contact or a contact selected from a restricted list after first selecting a company: SELECT [Job], [Company], [Contact] FROM [Job Numbers] INNER JOIN [Contacts] ON [Job Numbers] INNER JOIN [Contacts] ON [Job Numbers].[ContactID] = [Contacts].ContactID] WHERE ([Contacts].[ContactID] = Forms![YourDialogueForm]![cboContact] OR Forms![YourDialogueForm]![cboContact] IS NULL) AND ([Company] = Forms![YourDialogueForm]![cboCompany] OR Forms![YourDialogueForm]![cboCompany] IS NULL); Note that the parentheses in the above query's WHERE clause are crucial to force each OR operation to evaluate independently of the AND operation. You can of course return any other columns you wish in the table and could also join the Companies table in the query if there are other columns from that table you'd want returned. Ken Sheridan Stafford, England kim s wrote: >Dorian, the "Jobs" refer to inspections that we perform. I was trying to >include both the Company and the individual Contact (or contacts as the case >may be) associated with each job. Typically we do a Job search based on the >Job # and/or Company and/or item inspected. However, there are cases in >which we do a search based on the Contact. I was running into a problem in >which the Job table was able to give me the option of including a Company and >a Contact. However, ALL my Contact names were listed, instead of simply >those associated with the Company. I have had a difficult time explaining >the situation and I have an Access "How-To" book and it sort of addresses >this issue, but then drops off. I may have found a way around my problem. I >will keep working on it. Thanks for the input. > >> Unless a contact can be shared between companies, the contact already defines >> the company so there is no need for the job to link to both. >[quoted text clipped - 13 lines] >> > see all Contacts with their associated Company by clicking on the plus (+) >> > sign beside each company, but can't see this in the Job Numbers table. -- Message posted via http://www.accessmonster.com
From: Jeff Boyce on 27 Jan 2010 18:07 Kim No offense intended, it was just that I couldn't tell your level of familiarity from your original post. I will stick to my original response, though ... If you are trying to find Jobs associated with a Company, use forms, not tables. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "kim s" <kims(a)discussions.microsoft.com> wrote in message news:502C7798-BFED-404A-933E-12788E1F2129(a)microsoft.com... > Jeff, > > I appreciate your response. However, I am familiar with Access (as this > is > the second database I've created) and well aware that Access is not a > spreadsheet. As a matter of fact, I've been trying to explain to my boss > (who is a spreadsheet guru) that for the type of data that I need to > track, > the size of a spreadsheet would be out of sight and darn-near impossible. > > Essentially, I am trying to track jobs, their associated reports, and the > necessary customer (Company & Contact) information. At this point I have > numerous tables which include (1) the Job & associated details (the job #, > jobs performed & report), (2) the Company (or client), (3) the individual > contacts (as there are numerous contacts for each Company/client), (4) the > personnel from my company associated with the job. This is a simplified > version of what I've actually done, but enough that you get the jist of > it. > I was just trying to find a way that once I input the Company name in the > Job > table, that only the Contacts associated with that Company would be > visible > in the Job table. I wanted to do this in order to simplify matters at a > later date when I have to do a search/query based on the Job # and/or > Company > and/or Contact and/or Personnel. The Contact is honestly a secondary > feature, but a detail that I run into occassionally and would like to > still > have it related to my main Job table. I may have found a way around my > dilemma but it is not necessarily the way I would have wanted it. I > thought > that perhaps I could find my answer on the Discussion Group. But perhaps > I > didn't explain myself correctly... > -- > kim s > > > "Jeff Boyce" wrote: > >> Kim >> >> Access is not a spreadsheet. >> >> If you want to get the most out of the tool, you need to use the features >> as >> designed. >> >> Access tables store data. They don't work well for the kind of lookups >> you >> are describing. >> >> Access forms display data (on screen; use reports to display printed >> data) -- use forms to do that kind of looking up. >> >> Regards >> >> Jeff Boyce >> Microsoft Access MVP >> >> -- >> Disclaimer: This author may have received products and services mentioned >> in this post. Mention and/or description of a product or service herein >> does not constitute endorsement thereof. >> >> Any code or pseudocode included in this post is offered "as is", with no >> guarantee as to suitability. >> >> You can thank the FTC of the USA for making this disclaimer >> possible/necessary. >> >> "kim s" <kims(a)discussions.microsoft.com> wrote in message >> news:126A8061-B7C4-4260-BE78-ED33FE0CE2D3(a)microsoft.com... >> >I have three [primary] tables in a new database I'm trying to create. >> >They >> > are (1) Company, (2) Contacts, which is related to the Company table as >> > we >> > may have multiple Contacts for the same Company, and (3) Job Numbers, >> > which >> > includes details associated with each particular job (including a field >> > for >> > Company and a field for Contact). In the Job Numbers table, using the >> > lookup >> > wizard, I have created a combo box for both the Company and Contacts >> > fields. >> > What I need to find out is how to display only the Contacts associated >> > with >> > the Company I choose in the Job Numbers table. In the Company table I >> > can >> > see all Contacts with their associated Company by clicking on the plus >> > (+) >> > sign beside each company, but can't see this in the Job Numbers table. >> > -- >> > kim s >> >> >> . >>
From: kim s on 27 Jan 2010 20:53 Ken, Thank you very much for your response. I will look into it further tomorrow when I get back to the office. Unfortunately, my problem lies with the fact that the Company is my primary focus and my Contact is my secondary focus. I may have found a temporary solution to my dilemma within the Job form, and will look into including the data that you included in your post. It's one of those issues in which I may have over-thought everything in an effort to simplify things and in the end created a more difficult situation. I will definitely try what you have suggested in your post and am grateful for your response. -- kim s "KenSheridan via AccessMonster.com" wrote: > Kim: > > You are missing the point which Dorian was making. As each contact can > presumably only work for one company, once you know who is the contact you > then know the company. It is said that contact 'determines' company. So if > you have columns in the Jobs table for both the company and the contact, the > company column introduces redundancy into the table, so it is not correctly > normalized as it includes a 'transitive functional dependency', Job--- > >Contact---->Company. This leaves the table at risk of inconsistent data as > there is nothing to stop a row in the table including a contact and company > for whom the contact does not work (as you have found out). So you should > only have a column for the contact in the Jobs table, not the company; the > integrity of the data is then protected > > It is still possible when entering a row in the Jobs table to do so by > selecting a company first, then a contact, by using correlated combo boxes, > the one to select the company being unbound. > > As regards the correlated combo boxes how you set these up depends on whether > the form is in continuous forms view or single form view. If the former, and > you are using 'surrogate' numeric keys for the tables (ContactID in the > example below as names are unsuitable as 'natural' keys being legitimately > duplicated) then you cannot use combo boxes alone as controls in rows will go > blank if you select a different higher level value (Company in the example > below in the other). The solution is to use hybrid controls by superimposing > a text box on each combo box so that it looks like a single combo box control > to the user. Single forms are much simpler and don't require the hybrid > controls, merely the combo boxes > > A. For continuous forms here's an example of a Jobs form which includes > correlated combo boxes for Company and Contact, while maintaining the Jobs > table in Third Normal Form by having an ContactID column, but not a Company > column: > > 1. An unbound cboCompanies with a RowSource property of: > > SELECT Company > FROM Companies > ORDER BY Company; > > and an AfterUpdate event procedure of: > > Private Sub cboCompanies_AfterUpdate() > > ' requery Contacts combo box to > ' show Contacts with selected Company > Me!cboContacts.Requery > ' clear Contacts combo box > Me!cboContacts = Null > > End Sub > > 2. A bound cboContacts combo box with a ControlSource property of ContactID > and a RowSource property of: > > SELECT ContactID, Contact > FROM Contacts > WHERE Company =Form!cboCompanies > ORDER BY Contacts.Contact; > > The ColumnCount property is 2 and the ColumWidths property is 0cm;8cm to hide > the first column. > > Note the use of the Form property in the above rather than a full reference > to the form. This is possible as both controls are ion the same form. > > 3. An unbound text box txtCompany superimposed of cboCompanies, with a > ControlSource property of: > > =GetCompany([cboContacts]) > > 4. An unbound text box txtContact superimposed of cboContacts , with a > ControlSource property of: > > =GetContact([cboContacts]) > > Along with the other code the form's module in total would thus be: > > ''''module starts'''' > Option Compare Database > Option Explicit > > Private Function GetCompany(varContactID) > > ' get Company for current value of ContactID field > If Not IsNull(varContactID) Then > GetCompany = DLookup("Company", "Contacts", "ContactID = " & > varContactID) > Else > GetCompany = Me.cboCompanies > End If > > End Function > > Private Function GetContact(varContactID) > > If Not IsNull(varContactID) Then > GetContact = DLookup("Contact", "Contacts", "ContactID = " & > varContactID) > End If > > End Function > > Private Sub cboCompanies_AfterUpdate() > > ' requery Contacts combo box to > ' show Contacts with selected Company > Me!cboContacts.Requery > ' clear Contacts combo box > Me!cboContacts = Null > > End Sub > > Private Sub cmdClose_Click() > > DoCmd.Close acForm, Me.Name > > End Sub > > Private Sub Form_Current() > > If Me.NewRecord Then > Me!cboCompanies = Null > Else > Me!cboCompanies = GetCompany(Me!cboContacts) > End If > > Me!cboCompanies.Requery > Me!cboContacts.Requery > > End Sub > > ''''module ends'''' > > Basically the way it works is that when you move focus to one of the combo > boxes by clicking on its arrow its text box part becomes visible and its list > drops down. When you move focus off the control the superimposed text box > becomes visible. The functions get the text values for these by looking them > up from the relevant table on the basis of the corresponding key value which > is the hidden value of the bound cboContacts combo box. > > B. For single form view you can dispense with the two text boxes txtCompany > and txtContact and the module is simpler: > > ''''module starts > Option Compare Database > Option Explicit > > Private Function GetCompany(varContactID) > > ' get Company for current value of ContactID field > If Not IsNull(varContactID) Then > GetCompany = DLookup("Company", "Contacts", "ContactID = " & > varContactID) > Else > GetCompany = Me.cboCompanies > End If > > End Function > > > Private Sub cboCompanies_AfterUpdate() > > ' requery Contacts combo box to > ' show Contacts with selected Company > Me!cboContacts.Requery > ' clear Contacts combo box > Me!cboContacts = Null > > End Sub > > Private Sub cmdClose_Click() > > DoCmd.Close acForm, Me.Name > > End Sub > > Private Sub Form_Current() > > If Me.NewRecord Then > Me!cboCompanies = Null > Else > Me!cboCompanies = GetCompany(Me!cboContacts) > End If > > Me!cboCompanies.Requery > Me!cboContacts.Requery > > End Sub > ''''module ends'''' > > Watch out for any lines which your newsreader might have split over two lines > in the above. > > If you'd like a copy of the demo file from which the above code is adapted, > mail me at: > > kenwsheridan<at>yahoo<dot>co<dot>uk > > For searching purposes you would simply need to base search on a query which > joins Job Numbers and Contacts (the Companies table is not needed as the > Contacts table includes a Company column as a foreign key). The search can > be via an unbound dialogue form with combo boxes cboCompany and cboContact. > The RowSource for the former would be: > > SELECT Company FROM Companies ORDR BY Company; > > The RowSource for the latter: > > SELECT ContactID, Contact FROM Contacts WHERE Company = Form!cboCompany OR > Form!cboCompany IS NULL ORDER BY Contact; > > In the AfterUpdate event procedure of cboCompany set to Null and requery > cboContact with: > > Me.cboContact = Null > Me.cboContact.Requery > > From the dialogue form open a form or report based on a query which > references the two combo boxes on the form as parameters, testing for Null in > each case to allow a search to be made by a company, a contact or a contact > selected from a restricted list after first selecting a company: > > SELECT [Job], [Company], [Contact] > FROM [Job Numbers] INNER JOIN [Contacts] > ON [Job Numbers] INNER JOIN [Contacts] > ON [Job Numbers].[ContactID] = [Contacts].ContactID] > WHERE ([Contacts].[ContactID] = Forms![YourDialogueForm]![cboContact] > OR Forms![YourDialogueForm]![cboContact] IS NULL) > AND ([Company] = Forms![YourDialogueForm]![cboCompany] > OR Forms![YourDialogueForm]![cboCompany] IS NULL); > > Note that the parentheses in the above query's WHERE clause are crucial to > force each OR operation to evaluate independently of the AND operation. You > can of course return any other columns you wish in the table and could also > join the Companies table in the query if there are other columns from that > table you'd want returned. > > Ken Sheridan > Stafford, England > > kim s wrote: > >Dorian, the "Jobs" refer to inspections that we perform. I was trying to > >include both the Company and the individual Contact (or contacts as the case > >may be) associated with each job. Typically we do a Job search based on the > >Job # and/or Company and/or item inspected. However, there are cases in > >which we do a search based on the Contact. I was running into a problem in > >which the Job table was able to give me the option of including a Company and > >a Contact. However, ALL my Contact names were listed, instead of simply > >those associated with the Company. I have had a difficult time explaining > >the situation and I have an Access "How-To" book and it sort of addresses > >this issue, but then drops off. I may have found a way around my problem. I > >will keep working on it. Thanks for the input. > > > >> Unless a contact can be shared between companies, the contact already defines > >> the company so there is no need for the job to link to both. > >[quoted text clipped - 13 lines] > >> > see all Contacts with their associated Company by clicking on the plus (+) > >> > sign beside each company, but can't see this in the Job Numbers table. > > -- > Message posted via http://www.accessmonster.com > > . >
From: KenSheridan via AccessMonster.com on 28 Jan 2010 07:41
Kim: You might be thinking of a situation where you want to record a company for a job, but not a specific contact within the company. The model still applies in that situation as what you'd do is for every company include a row in the Contacts table with a value of 'N/A' or similar instead of the contact's name. In a form based on the jobs table you'd then select the company first in the unbound combo box, and 'N/A' in the contacts combo box. The relationship chain from the jobs table to the contacts table to the companies table is then unbroken. Mail me if you'd like the file which demonstrates this. There is a more complex version available online at: http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&tsn=1&tid=23626&webtag=ws-msdevapps but its really far too complex for what you need as it deals with a three- level hierarchy rather than two. Ken Sheridan Stafford, England kim s wrote: >Ken, > >Thank you very much for your response. I will look into it further tomorrow >when I get back to the office. Unfortunately, my problem lies with the fact >that the Company is my primary focus and my Contact is my secondary focus. I >may have found a temporary solution to my dilemma within the Job form, and >will look into including the data that you included in your post. It's one >of those issues in which I may have over-thought everything in an effort to >simplify things and in the end created a more difficult situation. I will >definitely try what you have suggested in your post and am grateful for your >response. > >-- kim s > >> Kim: >> >[quoted text clipped - 253 lines] >> >> > see all Contacts with their associated Company by clicking on the plus (+) >> >> > sign beside each company, but can't see this in the Job Numbers table. -- Message posted via http://www.accessmonster.com |