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: kim s on 27 Jan 2010 14:42 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: Jeff Boyce on 27 Jan 2010 14:51 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: Dorian on 27 Jan 2010 15:23 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. What are the jobs related to and how? -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "kim s" wrote: > 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 15:49 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. -- kim s "Dorian" wrote: > 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. > What are the jobs related to and how? > -- Dorian > "Give someone a fish and they eat for a day; teach someone to fish and they > eat for a lifetime". > > > "kim s" wrote: > > > 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 16:04
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 > > > . > |