Access 2007 - Intederminate relationship

  • Thread starter Thread starter kgoo
  • Start date Start date
K

kgoo

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?
 
Sounds like you have data where:
- 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/accessjunkie/resources.html#DatabaseDesign101
 
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 !!
 
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?
Click to expand...
 
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.
 
Thank you. This is really helpful. I didn't realize I needed more than two
tables to relate the data.

Thanks again!!
 
Thank you. This is really helpful. I didn't realize I needed more than two
tables to relate the data.

Thanks again!!
Click to expand...

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.
 
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
 
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.
 
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?
 
Imports tend to be quite specific. There are so many problems that can
occur, e.g. inconsistent columns (data above/below/in the middle of the
valid columns that are just comments), inconsistent types (e.g. "N/A" in a
date column), new columns inserted since last import (so the columns are not
in the right order, or the names in the first row are different),
inconsistent data (e.g. "Acme Corp" is company 123 in the first row, but it
is company 456 in a later row), and so on.

Additionally the good data structure you are trying to import into varies
(e.g. a column can't be null, or has a validation rule, or is uniquely
indexed, or there is a validation rule, or has to match a foreign key value,
or ...)

Therefore the import routine needs to be written to test the data. One
approach is to import it (as is) into a temporary table, where you can run a
series of tests for all the things that could go wrong, and provide a form
where the user can fix the bad data. Once all critical problems are fixed,
you execute the series of append query statements that will write the data
into the real tables in your database.
 
Back
Top