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
|
|||
|
|||
Access 2007 - Intederminate relationship
Im trying to create a database that contains info. about our agents and the
company they work for. I created two tables - Agent table and Company table. In the agent table I have agency code, agent name, line of business, email and category (commercial, contract, fidelity). In the company table I have agency code, agency address, web address, total premium. The problem is, I am having trouble creating a relationship. I let Access select the primary key and then I tried creating a relationship with agency code. I keep getting a "indeterminate relationship". I'm guessing this is because the agency code is listed numerous times in my source data? I imported my excel spreadsheet into the database and I want to keep the source data as it is. Basically, I have many categories for one agent so the agency code for that agent is listed more than once. ie.. john smith is an agent for commercial, contract, fidelity business. In my excel spreadsheet John Smith along with the agency code is listed 3 times and that is how it uploaded into Access. Do you have any suggestions on how I can keep the data the same but tie the agency codes for both tables together? Or could I do one big table and avoid the relationship? |
#2
|
|||
|
|||
Access 2007 - Intederminate relationship
Sounds like you have data whe
- one agent can serve many companies, and - one company can have many agents. This is a many-to-many relation, so in a relational database you would use 3 tables: - Agent table (one record for each agent, with a unique AgentID primary key) - Company table (one record for each company, with a unique CompanyID p.k.) - CompanyAgent table, with fields: CompanyID relates to Company.CompanyID AgentID relates to Agent.AgentID. You can then create 2 one-to-many relationships between these 3 tables. Excel is not a database. You either create a relational design in Access, or you don't have a relational database. For further examples of resolving a many-to-many into a pair of one-to-many relations, see: Relationships between Tables (School Grades example) at: http://allenbrowne.com/casu-06.html and: Don't use Yes/No fields to store preferences at: http://allenbrowne.com/casu-23.html If you want more reading, search on 'normalization.' Here's a starting point: http://www.accessmvp.com/JConrad/acc...abaseDesign101 -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "kgoo" wrote in message news Im trying to create a database that contains info. about our agents and the company they work for. I created two tables - Agent table and Company table. In the agent table I have agency code, agent name, line of business, email and category (commercial, contract, fidelity). In the company table I have agency code, agency address, web address, total premium. The problem is, I am having trouble creating a relationship. I let Access select the primary key and then I tried creating a relationship with agency code. I keep getting a "indeterminate relationship". I'm guessing this is because the agency code is listed numerous times in my source data? I imported my excel spreadsheet into the database and I want to keep the source data as it is. Basically, I have many categories for one agent so the agency code for that agent is listed more than once. ie.. john smith is an agent for commercial, contract, fidelity business. In my excel spreadsheet John Smith along with the agency code is listed 3 times and that is how it uploaded into Access. Do you have any suggestions on how I can keep the data the same but tie the agency codes for both tables together? Or could I do one big table and avoid the relationship? |
#3
|
|||
|
|||
Access 2007 - Intederminate relationship
Allen,
Thank you. Actually, the agents are from one company. I guess you could say one company can have many agents & many agents can have many jobs? For example: JOBS AGENCY NAME AGENCY CODE AGENT NAME Owner Good Bakery 12345 Donna Gooding Baker Good Bakery 12345 Donna Gooding Accountant Good Bakery 12345 " " Owner Johns Bread&CO 6789 John Dewars Baker Johns Bread&CO 6789 John Dewars How can I relate the agent table to the company table if the agency code (unique identifier) is listed more than once? I think I'm confusing myself !! "Allen Browne" wrote: Sounds like you have data whe - one agent can serve many companies, and - one company can have many agents. This is a many-to-many relation, so in a relational database you would use 3 tables: - Agent table (one record for each agent, with a unique AgentID primary key) - Company table (one record for each company, with a unique CompanyID p.k.) - CompanyAgent table, with fields: CompanyID relates to Company.CompanyID AgentID relates to Agent.AgentID. You can then create 2 one-to-many relationships between these 3 tables. Excel is not a database. You either create a relational design in Access, or you don't have a relational database. For further examples of resolving a many-to-many into a pair of one-to-many relations, see: Relationships between Tables (School Grades example) at: http://allenbrowne.com/casu-06.html and: Don't use Yes/No fields to store preferences at: http://allenbrowne.com/casu-23.html If you want more reading, search on 'normalization.' Here's a starting point: http://www.accessmvp.com/JConrad/acc...abaseDesign101 -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "kgoo" wrote in message news Im trying to create a database that contains info. about our agents and the company they work for. I created two tables - Agent table and Company table. In the agent table I have agency code, agent name, line of business, email and category (commercial, contract, fidelity). In the company table I have agency code, agency address, web address, total premium. The problem is, I am having trouble creating a relationship. I let Access select the primary key and then I tried creating a relationship with agency code. I keep getting a "indeterminate relationship". I'm guessing this is because the agency code is listed numerous times in my source data? I imported my excel spreadsheet into the database and I want to keep the source data as it is. Basically, I have many categories for one agent so the agency code for that agent is listed more than once. ie.. john smith is an agent for commercial, contract, fidelity business. In my excel spreadsheet John Smith along with the agency code is listed 3 times and that is how it uploaded into Access. Do you have any suggestions on how I can keep the data the same but tie the agency codes for both tables together? Or could I do one big table and avoid the relationship? |
#4
|
|||
|
|||
Access 2007 - Intederminate relationship
Sorry the example didn't come out very clear. This is how my excel
spreadsheet is set up. I plan on uploading into Access 07. For example: JOBS AGENCY NAME AGENCY CODE AGENT NAME Owner Good Bakery 12345 Donna Baker Good Bakery 12345 Donna Accountant Good Bakery 12345 Donna Owner Johns Bread&CO 6789 John Baker Johns Bread&CO 6789 John How can I relate the agent table to the company table if the agency code (unique identifier) is listed more than once? "kgoo" wrote: Allen, Thank you. Actually, the agents are from one company. I guess you could say one company can have many agents & many agents can have many jobs? For example: JOBS AGENCY NAME AGENCY CODE AGENT NAME Owner Good Bakery 12345 Donna Gooding Baker Good Bakery 12345 Donna Gooding Accountant Good Bakery 12345 " " Owner Johns Bread&CO 6789 John Dewars Baker Johns Bread&CO 6789 John Dewars How can I relate the agent table to the company table if the agency code (unique identifier) is listed more than once? I think I'm confusing myself !! "Allen Browne" wrote: Sounds like you have data whe - one agent can serve many companies, and - one company can have many agents. This is a many-to-many relation, so in a relational database you would use 3 tables: - Agent table (one record for each agent, with a unique AgentID primary key) - Company table (one record for each company, with a unique CompanyID p.k.) - CompanyAgent table, with fields: CompanyID relates to Company.CompanyID AgentID relates to Agent.AgentID. You can then create 2 one-to-many relationships between these 3 tables. Excel is not a database. You either create a relational design in Access, or you don't have a relational database. For further examples of resolving a many-to-many into a pair of one-to-many relations, see: Relationships between Tables (School Grades example) at: http://allenbrowne.com/casu-06.html and: Don't use Yes/No fields to store preferences at: http://allenbrowne.com/casu-23.html If you want more reading, search on 'normalization.' Here's a starting point: http://www.accessmvp.com/JConrad/acc...abaseDesign101 -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "kgoo" wrote in message news Im trying to create a database that contains info. about our agents and the company they work for. I created two tables - Agent table and Company table. In the agent table I have agency code, agent name, line of business, email and category (commercial, contract, fidelity). In the company table I have agency code, agency address, web address, total premium. The problem is, I am having trouble creating a relationship. I let Access select the primary key and then I tried creating a relationship with agency code. I keep getting a "indeterminate relationship". I'm guessing this is because the agency code is listed numerous times in my source data? I imported my excel spreadsheet into the database and I want to keep the source data as it is. Basically, I have many categories for one agent so the agency code for that agent is listed more than once. ie.. john smith is an agent for commercial, contract, fidelity business. In my excel spreadsheet John Smith along with the agency code is listed 3 times and that is how it uploaded into Access. Do you have any suggestions on how I can keep the data the same but tie the agency codes for both tables together? Or could I do one big table and avoid the relationship? |
#5
|
|||
|
|||
Access 2007 - Intederminate relationship
Tables will be something like this:
Agency table (one record for each agency): - AgencyCode Number (?) primary key - AgencyName Text Agent table (one record for each person): - AgentID AutoNumber primary key - Surname Text - FirstName Text Role table (one record for each type of job, e.g. 'baker'): - RoleID Text (24) primary key Job table: - JobID AutoNumber primary key - AgentID Number relates to Agent.AgentID - AgencyCode Number relates to Agency.AgencyCode - RoleID Text (24) relates to Role.RoleID - StartDate Date/Time when this person started this job at this agency. - EndDate Date/Time when stopped. Blank for current. Example of data in the last table: JobID AgentID AgencyCode RoleID StartDate End Date 1 4 12345 Owner 1/1/08 2 4 12345 Baker 1/1/08 3 4 12345 Accountant 1/1/08 So, if Donna is agent 4, and Good Bakery is agency 12345, she started 3 jobs there at the beginning of the year. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "kgoo" wrote in message ... Sorry the example didn't come out very clear. This is how my excel spreadsheet is set up. I plan on uploading into Access 07. For example: JOBS AGENCY NAME AGENCY CODE AGENT NAME Owner Good Bakery 12345 Donna Baker Good Bakery 12345 Donna Accountant Good Bakery 12345 Donna Owner Johns Bread&CO 6789 John Baker Johns Bread&CO 6789 John How can I relate the agent table to the company table if the agency code (unique identifier) is listed more than once? "kgoo" wrote: Allen, Thank you. Actually, the agents are from one company. I guess you could say one company can have many agents & many agents can have many jobs? For example: JOBS AGENCY NAME AGENCY CODE AGENT NAME Owner Good Bakery 12345 Donna Gooding Baker Good Bakery 12345 Donna Gooding Accountant Good Bakery 12345 " " Owner Johns Bread&CO 6789 John Dewars Baker Johns Bread&CO 6789 John Dewars How can I relate the agent table to the company table if the agency code (unique identifier) is listed more than once? I think I'm confusing myself !! "Allen Browne" wrote: Sounds like you have data whe - one agent can serve many companies, and - one company can have many agents. This is a many-to-many relation, so in a relational database you would use 3 tables: - Agent table (one record for each agent, with a unique AgentID primary key) - Company table (one record for each company, with a unique CompanyID p.k.) - CompanyAgent table, with fields: CompanyID relates to Company.CompanyID AgentID relates to Agent.AgentID. You can then create 2 one-to-many relationships between these 3 tables. Excel is not a database. You either create a relational design in Access, or you don't have a relational database. For further examples of resolving a many-to-many into a pair of one-to-many relations, see: Relationships between Tables (School Grades example) at: http://allenbrowne.com/casu-06.html and: Don't use Yes/No fields to store preferences at: http://allenbrowne.com/casu-23.html If you want more reading, search on 'normalization.' Here's a starting point: http://www.accessmvp.com/JConrad/acc...abaseDesign101 -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "kgoo" wrote in message news Im trying to create a database that contains info. about our agents and the company they work for. I created two tables - Agent table and Company table. In the agent table I have agency code, agent name, line of business, and category (commercial, contract, fidelity). In the company table I have agency code, agency address, web address, total premium. The problem is, I am having trouble creating a relationship. I let Access select the primary key and then I tried creating a relationship with agency code. I keep getting a "indeterminate relationship". I'm guessing this is because the agency code is listed numerous times in my source data? I imported my excel spreadsheet into the database and I want to keep the source data as it is. Basically, I have many categories for one agent so the agency code for that agent is listed more than once. ie.. john smith is an agent for commercial, contract, fidelity business. In my excel spreadsheet John Smith along with the agency code is listed 3 times and that is how it uploaded into Access. Do you have any suggestions on how I can keep the data the same but tie the agency codes for both tables together? Or could I do one big table and avoid the relationship? |
#6
|
|||
|
|||
Access 2007 - Intederminate relationship
Thank you. This is really helpful. I didn't realize I needed more than two
tables to relate the data. Thanks again!! "Allen Browne" wrote: Tables will be something like this: Agency table (one record for each agency): - AgencyCode Number (?) primary key - AgencyName Text Agent table (one record for each person): - AgentID AutoNumber primary key - Surname Text - FirstName Text Role table (one record for each type of job, e.g. 'baker'): - RoleID Text (24) primary key Job table: - JobID AutoNumber primary key - AgentID Number relates to Agent.AgentID - AgencyCode Number relates to Agency.AgencyCode - RoleID Text (24) relates to Role.RoleID - StartDate Date/Time when this person started this job at this agency. - EndDate Date/Time when stopped. Blank for current. Example of data in the last table: JobID AgentID AgencyCode RoleID StartDate End Date 1 4 12345 Owner 1/1/08 2 4 12345 Baker 1/1/08 3 4 12345 Accountant 1/1/08 So, if Donna is agent 4, and Good Bakery is agency 12345, she started 3 jobs there at the beginning of the year. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "kgoo" wrote in message ... Sorry the example didn't come out very clear. This is how my excel spreadsheet is set up. I plan on uploading into Access 07. For example: JOBS AGENCY NAME AGENCY CODE AGENT NAME Owner Good Bakery 12345 Donna Baker Good Bakery 12345 Donna Accountant Good Bakery 12345 Donna Owner Johns Bread&CO 6789 John Baker Johns Bread&CO 6789 John How can I relate the agent table to the company table if the agency code (unique identifier) is listed more than once? "kgoo" wrote: Allen, Thank you. Actually, the agents are from one company. I guess you could say one company can have many agents & many agents can have many jobs? For example: JOBS AGENCY NAME AGENCY CODE AGENT NAME Owner Good Bakery 12345 Donna Gooding Baker Good Bakery 12345 Donna Gooding Accountant Good Bakery 12345 " " Owner Johns Bread&CO 6789 John Dewars Baker Johns Bread&CO 6789 John Dewars How can I relate the agent table to the company table if the agency code (unique identifier) is listed more than once? I think I'm confusing myself !! "Allen Browne" wrote: Sounds like you have data whe - one agent can serve many companies, and - one company can have many agents. This is a many-to-many relation, so in a relational database you would use 3 tables: - Agent table (one record for each agent, with a unique AgentID primary key) - Company table (one record for each company, with a unique CompanyID p.k.) - CompanyAgent table, with fields: CompanyID relates to Company.CompanyID AgentID relates to Agent.AgentID. You can then create 2 one-to-many relationships between these 3 tables. Excel is not a database. You either create a relational design in Access, or you don't have a relational database. For further examples of resolving a many-to-many into a pair of one-to-many relations, see: Relationships between Tables (School Grades example) at: http://allenbrowne.com/casu-06.html and: Don't use Yes/No fields to store preferences at: http://allenbrowne.com/casu-23.html If you want more reading, search on 'normalization.' Here's a starting point: http://www.accessmvp.com/JConrad/acc...abaseDesign101 -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "kgoo" wrote in message news Im trying to create a database that contains info. about our agents and the company they work for. I created two tables - Agent table and Company table. In the agent table I have agency code, agent name, line of business, and category (commercial, contract, fidelity). In the company table I have agency code, agency address, web address, total premium. The problem is, I am having trouble creating a relationship. I let Access select the primary key and then I tried creating a relationship with agency code. I keep getting a "indeterminate relationship". I'm guessing this is because the agency code is listed numerous times in my source data? I imported my excel spreadsheet into the database and I want to keep the source data as it is. Basically, I have many categories for one agent so the agency code for that agent is listed more than once. ie.. john smith is an agent for commercial, contract, fidelity business. In my excel spreadsheet John Smith along with the agency code is listed 3 times and that is how it uploaded into Access. Do you have any suggestions on how I can keep the data the same but tie the agency codes for both tables together? Or could I do one big table and avoid the relationship? |
#7
|
|||
|
|||
Access 2007 - Intederminate relationship
On Sat, 23 Aug 2008 03:41:00 -0700, kgoo wrote:
Thank you. This is really helpful. I didn't realize I needed more than two tables to relate the data. Thanks again!! "Allen Browne" wrote: Tables will be something like this: Agency table (one record for each agency): - AgencyCode Number (?) primary key - AgencyName Text Agent table (one record for each person): - AgentID AutoNumber primary key - Surname Text - FirstName Text Role table (one record for each type of job, e.g. 'baker'): - RoleID Text (24) primary key Job table: - JobID AutoNumber primary key - AgentID Number relates to Agent.AgentID - AgencyCode Number relates to Agency.AgencyCode - RoleID Text (24) relates to Role.RoleID - StartDate Date/Time when this person started this job at this agency. - EndDate Date/Time when stopped. Blank for current. Example of data in the last table: JobID AgentID AgencyCode RoleID StartDate End Date 1 4 12345 Owner 1/1/08 2 4 12345 Baker 1/1/08 3 4 12345 Accountant 1/1/08 So, if Donna is agent 4, and Good Bakery is agency 12345, she started 3 jobs there at the beginning of the year. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. I think I would have a Unique index on (AgentID,AgencyCode,RoleID,StartDate). You also need a constraint to check for overlaps. This could be done in a form event procedure. This prevents someone assigning AgentID 4 AgencyCode 12345 RoleID Baker starting on 18 Jan 2008. In other words, someone cannot be assigned a job they are already assigned. |
#8
|
|||
|
|||
Access 2007 - Intederminate relationship
Hello,
I am still having problems with this relationship. I think a major part of the problem is importing the data from excel into Access. I have two spreadsheets. One for agent information and one for company information. The agent information includes the agent "roles" (ie...baker, jeweler, etc..) already so I'm not sure if I even need a ROLES table? I keep getting an error message when I run a query...it says "The wizard is unable to open your query in datasheet view, possibly because another user has a source table open in exclusive mode. your query will be opened in design view" HELP! This is what I did: Company Table --CompanyID, Role, Agency Code (pk), Agency Name, Address Agent Table--AgentID (PK),Role, Agenct Code Role Table -- RoleID(PK), Roles Jobs-- JobID, AgentID (PK), Agency Code, RoleID My excel spreadsheet for agents looks something like this: Janie Dewalters, ABC Company, Make-upartist, code:12345 Janie Dewalters, ABC Company, Jeweler, code:12345 Janie Dewalters, ABC Company, Hairstylist, code: 12345 My excel spreadsheet for company looks something like this: ABC Company, code:12345, New York, NY 11235 "Michael Gramelspacher" wrote: On Sat, 23 Aug 2008 03:41:00 -0700, kgoo wrote: Thank you. This is really helpful. I didn't realize I needed more than two tables to relate the data. Thanks again!! "Allen Browne" wrote: Tables will be something like this: Agency table (one record for each agency): - AgencyCode Number (?) primary key - AgencyName Text Agent table (one record for each person): - AgentID AutoNumber primary key - Surname Text - FirstName Text Role table (one record for each type of job, e.g. 'baker'): - RoleID Text (24) primary key Job table: - JobID AutoNumber primary key - AgentID Number relates to Agent.AgentID - AgencyCode Number relates to Agency.AgencyCode - RoleID Text (24) relates to Role.RoleID - StartDate Date/Time when this person started this job at this agency. - EndDate Date/Time when stopped. Blank for current. Example of data in the last table: JobID AgentID AgencyCode RoleID StartDate End Date 1 4 12345 Owner 1/1/08 2 4 12345 Baker 1/1/08 3 4 12345 Accountant 1/1/08 So, if Donna is agent 4, and Good Bakery is agency 12345, she started 3 jobs there at the beginning of the year. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. I think I would have a Unique index on (AgentID,AgencyCode,RoleID,StartDate). You also need a constraint to check for overlaps. This could be done in a form event procedure. This prevents someone assigning AgentID 4 AgencyCode 12345 RoleID Baker starting on 18 Jan 2008. In other words, someone cannot be assigned a job they are already assigned. |
#9
|
|||
|
|||
Access 2007 - Intederminate relationship
Ultimately, what you need depends on what you need to achieve. We can
comment on how's a good way to set this up in Access (normalized tables), but there is still the question of how to import the data. Why do you need a roles table? Without it, any rubbish (misspellings) can get stored in the column, which messes up reporting, counts, etc. The concurrency issue could have many causes. If you're still linked from the spreadsheet, it could even be in Excel rather than Access. Writing a robust import routine does take some effort, but if you are doing it regularly it may be worth the effort. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "kgoo" wrote in message ... Hello, I am still having problems with this relationship. I think a major part of the problem is importing the data from excel into Access. I have two spreadsheets. One for agent information and one for company information. The agent information includes the agent "roles" (ie...baker, jeweler, etc..) already so I'm not sure if I even need a ROLES table? I keep getting an error message when I run a query...it says "The wizard is unable to open your query in datasheet view, possibly because another user has a source table open in exclusive mode. your query will be opened in design view" HELP! This is what I did: Company Table --CompanyID, Role, Agency Code (pk), Agency Name, Address Agent Table--AgentID (PK),Role, Agenct Code Role Table -- RoleID(PK), Roles Jobs-- JobID, AgentID (PK), Agency Code, RoleID My excel spreadsheet for agents looks something like this: Janie Dewalters, ABC Company, Make-upartist, code:12345 Janie Dewalters, ABC Company, Jeweler, code:12345 Janie Dewalters, ABC Company, Hairstylist, code: 12345 My excel spreadsheet for company looks something like this: ABC Company, code:12345, New York, NY 11235 "Michael Gramelspacher" wrote: On Sat, 23 Aug 2008 03:41:00 -0700, kgoo wrote: Thank you. This is really helpful. I didn't realize I needed more than two tables to relate the data. Thanks again!! "Allen Browne" wrote: Tables will be something like this: Agency table (one record for each agency): - AgencyCode Number (?) primary key - AgencyName Text Agent table (one record for each person): - AgentID AutoNumber primary key - Surname Text - FirstName Text Role table (one record for each type of job, e.g. 'baker'): - RoleID Text (24) primary key Job table: - JobID AutoNumber primary key - AgentID Number relates to Agent.AgentID - AgencyCode Number relates to Agency.AgencyCode - RoleID Text (24) relates to Role.RoleID - StartDate Date/Time when this person started this job at this agency. - EndDate Date/Time when stopped. Blank for current. Example of data in the last table: JobID AgentID AgencyCode RoleID StartDate End Date 1 4 12345 Owner 1/1/08 2 4 12345 Baker 1/1/08 3 4 12345 Accountant 1/1/08 So, if Donna is agent 4, and Good Bakery is agency 12345, she started 3 jobs there at the beginning of the year. I think I would have a Unique index on (AgentID,AgencyCode,RoleID,StartDate). You also need a constraint to check for overlaps. This could be done in a form event procedure. This prevents someone assigning AgentID 4 AgencyCode 12345 RoleID Baker starting on 18 Jan 2008. In other words, someone cannot be assigned a job they are already assigned. |
#10
|
|||
|
|||
Access 2007 - Intederminate relationship
Hi Allen,
Do you know where I can find information on writing a good import routine? I have a huge "company" spreadsheet of about 16,000 records and an agent spreadsheet of about "8,000" records. Let's say all my data uploaded without any problems... do you think the structure of the tables should work based on my information provided? "Allen Browne" wrote: Ultimately, what you need depends on what you need to achieve. We can comment on how's a good way to set this up in Access (normalized tables), but there is still the question of how to import the data. Why do you need a roles table? Without it, any rubbish (misspellings) can get stored in the column, which messes up reporting, counts, etc. The concurrency issue could have many causes. If you're still linked from the spreadsheet, it could even be in Excel rather than Access. Writing a robust import routine does take some effort, but if you are doing it regularly it may be worth the effort. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "kgoo" wrote in message ... Hello, I am still having problems with this relationship. I think a major part of the problem is importing the data from excel into Access. I have two spreadsheets. One for agent information and one for company information. The agent information includes the agent "roles" (ie...baker, jeweler, etc..) already so I'm not sure if I even need a ROLES table? I keep getting an error message when I run a query...it says "The wizard is unable to open your query in datasheet view, possibly because another user has a source table open in exclusive mode. your query will be opened in design view" HELP! This is what I did: Company Table --CompanyID, Role, Agency Code (pk), Agency Name, Address Agent Table--AgentID (PK),Role, Agenct Code Role Table -- RoleID(PK), Roles Jobs-- JobID, AgentID (PK), Agency Code, RoleID My excel spreadsheet for agents looks something like this: Janie Dewalters, ABC Company, Make-upartist, code:12345 Janie Dewalters, ABC Company, Jeweler, code:12345 Janie Dewalters, ABC Company, Hairstylist, code: 12345 My excel spreadsheet for company looks something like this: ABC Company, code:12345, New York, NY 11235 "Michael Gramelspacher" wrote: On Sat, 23 Aug 2008 03:41:00 -0700, kgoo wrote: Thank you. This is really helpful. I didn't realize I needed more than two tables to relate the data. Thanks again!! "Allen Browne" wrote: Tables will be something like this: Agency table (one record for each agency): - AgencyCode Number (?) primary key - AgencyName Text Agent table (one record for each person): - AgentID AutoNumber primary key - Surname Text - FirstName Text Role table (one record for each type of job, e.g. 'baker'): - RoleID Text (24) primary key Job table: - JobID AutoNumber primary key - AgentID Number relates to Agent.AgentID - AgencyCode Number relates to Agency.AgencyCode - RoleID Text (24) relates to Role.RoleID - StartDate Date/Time when this person started this job at this agency. - EndDate Date/Time when stopped. Blank for current. Example of data in the last table: JobID AgentID AgencyCode RoleID StartDate End Date 1 4 12345 Owner 1/1/08 2 4 12345 Baker 1/1/08 3 4 12345 Accountant 1/1/08 So, if Donna is agent 4, and Good Bakery is agency 12345, she started 3 jobs there at the beginning of the year. I think I would have a Unique index on (AgentID,AgencyCode,RoleID,StartDate). You also need a constraint to check for overlaps. This could be done in a form event procedure. This prevents someone assigning AgentID 4 AgencyCode 12345 RoleID Baker starting on 18 Jan 2008. In other words, someone cannot be assigned a job they are already assigned. |
|
Thread Tools | |
Display Modes | |
|
|