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?

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?

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?

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?

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,
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?

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,
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?

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.

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.

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.

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.