Referencing multiple people in one table? (Table structure set-up)

  • Thread starter Thread starter annie via AccessMonster.com
  • Start date Start date
A

annie via AccessMonster.com

Hi -

I am having troubles figuring this out, although I'm sure I'm just not
thinking this completely through...

I'm building a job tracking db, and for each unique job I want to store, for
example, the sales representative (employee), the project director (employee),
data processor (employee), account executive (employee), etc.

I want to have all of the above in an employee table, and then a job table
with the employee id saved for each individual field (sales rep, project
director, etc.) along with the other information I want to capture about the
job.

Currently, I have a table split out by each type of employee (sales rep,
project director, etc), but that is redundant and an employee could be a
sales rep and a project director for the same job. How do I structure this?

Part of my tables look like this right now:

Job_Info
JobID (job id)
ARID (account rep id)
PDID (project director id)
SRID (sales rep id)

etc.

Account_Rep
ARID
Name
Email

etc.

and so on for each position. How can I make one employee table and save
Account Rep, Project Director, etc. into one job table? And be able to have
the same employee be the acct. rep as the project director??

Any help/guidance would be GREATLY appreciated!!!

Thanks!
 
I'm assuming here that a "job" is something you take on for a client (e.g.
the result of winning a contract), and so one job can have many people
working on it in different roles until the job is complete.

These tables should do it:

Client table (one record for each person/company you do jobs for):
ClientID AutoNumb primary key
ClientName Text the company name
...

Job table (one record for each job for a client):
JobID AutoNum primary key
ClientID Number relates to Client.ClientID
DueDate Date/Time the date the job needs to be complete.
...

Role table (one record for each kind to role a person could have, such as
"Sales Rep" or "Project Director"):
RoleID Text primary key.

Employee table (one record for each person):
EmployeeID AutoNumber primary key
Surname Text
...

JobEmployee table (one record for each combination of Job + Employee):
ID AutoNum primary key
JobID Number relates to Job.JobID
EmployeeID Number relates to Employee.EmployeeID
RoleID Text relates to Role.RoleID

Example: If there are 3 people working on Job 99, you have 3 records in the
JobEmployee table. The 3 rows look like this:
ID JobID EmployeeID RoleID
1 99 7 Sales Rep
2 99 13 Project Director
3 99 22 Director
 
Thanks!

Correct - a job is something that we do for a client.

Now another question - I have the tables set up as suggested, but I can't
figure out how to build my form from this. I have my form set up with
information from my job table and client tables. The form is for entering
new jobs, so a new job number and all associated fields are entered from
scratch. On the form, I want to be able to assign the employee/role. So,
for example, I want to be able to pick an employee's name, from a combo box,
and assign them as 'project director' (without having to pick the role, so
possibly having a label that says 'project director' and a populated combo
box beside it. On the exact same form, I also want to assign 'Sales Rep' in
the same manner. I can't figure out how to get both on the same form, with
only having to choose my employee name. Thoughts? Or should I take this to
the Forms discussion?

Thank you!

Allen said:
I'm assuming here that a "job" is something you take on for a client (e.g.
the result of winning a contract), and so one job can have many people
working on it in different roles until the job is complete.

These tables should do it:

Client table (one record for each person/company you do jobs for):
ClientID AutoNumb primary key
ClientName Text the company name
...

Job table (one record for each job for a client):
JobID AutoNum primary key
ClientID Number relates to Client.ClientID
DueDate Date/Time the date the job needs to be complete.
...

Role table (one record for each kind to role a person could have, such as
"Sales Rep" or "Project Director"):
RoleID Text primary key.

Employee table (one record for each person):
EmployeeID AutoNumber primary key
Surname Text
...

JobEmployee table (one record for each combination of Job + Employee):
ID AutoNum primary key
JobID Number relates to Job.JobID
EmployeeID Number relates to Employee.EmployeeID
RoleID Text relates to Role.RoleID

Example: If there are 3 people working on Job 99, you have 3 records in the
JobEmployee table. The 3 rows look like this:
ID JobID EmployeeID RoleID
1 99 7 Sales Rep
2 99 13 Project Director
3 99 22 Director
I am having troubles figuring this out, although I'm sure I'm just not
thinking this completely through...
Click to expand...
[quoted text clipped - 41 lines]
Thanks!
Click to expand...
Click to expand...
 
Create a form bound to the Job table.
Add a subform bound to the JobEmployee table, for assigning the employees to
that job (one per line.)

In the main form, you will have a combo where you select the client, and a
text box for entering the date. In the subform you will have a combo for
selecting the employee.

You say you don't want to have to select the role in the subform, but in the
original post you said the person could have different roles in different
jobs. I assume there is a "normal" role that an employee will have in most
jobs, but you want the freedom to change it? If so, you could include a
field in the Employee table for the person's normal role. You could then use
the AfterUpdate event of the EmployeeID combo to assign the person's normal
role to the RoleID field, which saves you having to select it but still
gives you the freedom to change it when needed.

To do that, you would set up these properties for the EmployeeID combo:

RowSource: SELECT EmployeeID, Surname, FirstName, RoleID
FROM Employee ORDER BY Surname, FirstName;
Bound Column: 1
Column Count: 4
Column Widths: 0;1;1;0
After Update: [Event Procedure]

Then click the Build button (...) beside the AfterUpdate property.
Access opens a code window, where you enter something like this:
Private Sub EmployeeID_AfterUpdate()
With Me.EmployeeID
If Len(Nz(.Column(3), "")) > 0 Then
Me.RoleID = .Column(3)
End If
End With
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

annie via AccessMonster.com said:
Thanks!

Correct - a job is something that we do for a client.

Now another question - I have the tables set up as suggested, but I can't
figure out how to build my form from this. I have my form set up with
information from my job table and client tables. The form is for entering
new jobs, so a new job number and all associated fields are entered from
scratch. On the form, I want to be able to assign the employee/role. So,
for example, I want to be able to pick an employee's name, from a combo
box,
and assign them as 'project director' (without having to pick the role, so
possibly having a label that says 'project director' and a populated combo
box beside it. On the exact same form, I also want to assign 'Sales Rep'
in
the same manner. I can't figure out how to get both on the same form,
with
only having to choose my employee name. Thoughts? Or should I take this
to
the Forms discussion?

Thank you!

Allen said:
I'm assuming here that a "job" is something you take on for a client (e.g.
the result of winning a contract), and so one job can have many people
working on it in different roles until the job is complete.

These tables should do it:

Client table (one record for each person/company you do jobs for):
ClientID AutoNumb primary key
ClientName Text the company name
...

Job table (one record for each job for a client):
JobID AutoNum primary key
ClientID Number relates to Client.ClientID
DueDate Date/Time the date the job needs to be complete.
...

Role table (one record for each kind to role a person could have, such as
"Sales Rep" or "Project Director"):
RoleID Text primary key.

Employee table (one record for each person):
EmployeeID AutoNumber primary key
Surname Text
...

JobEmployee table (one record for each combination of Job + Employee):
ID AutoNum primary key
JobID Number relates to Job.JobID
EmployeeID Number relates to Employee.EmployeeID
RoleID Text relates to Role.RoleID

Example: If there are 3 people working on Job 99, you have 3 records in
the
JobEmployee table. The 3 rows look like this:
ID JobID EmployeeID RoleID
1 99 7 Sales Rep
2 99 13 Project Director
3 99 22 Director
I am having troubles figuring this out, although I'm sure I'm just not
thinking this completely through...
Click to expand...
[quoted text clipped - 41 lines]
Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Yeah, that's exactly what I meant with the people and having a "normal role"
so you wouldn't have to change it every time.

Thanks for all of your help, I'll give it a whirl tonight!!

Thanks again!!

Allen said:
Create a form bound to the Job table.
Add a subform bound to the JobEmployee table, for assigning the employees to
that job (one per line.)

In the main form, you will have a combo where you select the client, and a
text box for entering the date. In the subform you will have a combo for
selecting the employee.

You say you don't want to have to select the role in the subform, but in the
original post you said the person could have different roles in different
jobs. I assume there is a "normal" role that an employee will have in most
jobs, but you want the freedom to change it? If so, you could include a
field in the Employee table for the person's normal role. You could then use
the AfterUpdate event of the EmployeeID combo to assign the person's normal
role to the RoleID field, which saves you having to select it but still
gives you the freedom to change it when needed.

To do that, you would set up these properties for the EmployeeID combo:

RowSource: SELECT EmployeeID, Surname, FirstName, RoleID
FROM Employee ORDER BY Surname, FirstName;
Bound Column: 1
Column Count: 4
Column Widths: 0;1;1;0
After Update: [Event Procedure]

Then click the Build button (...) beside the AfterUpdate property.
Access opens a code window, where you enter something like this:
Private Sub EmployeeID_AfterUpdate()
With Me.EmployeeID
If Len(Nz(.Column(3), "")) > 0 Then
Me.RoleID = .Column(3)
End If
End With
End Sub
Thanks!
Click to expand...
[quoted text clipped - 64 lines]
Click to expand...
 
Hi -

One last question, then I think I'll have it (I hope! :) )! I need to just
create one subform for assigning all employees, correct? I tried using one
subform, with multiple combo boxes for Employee ID and Role ID, but when I
add/change one, it changes the one I'm selecting/changing, but then clears
the records for other employees (for instance if I've assigned project
director using the first set of combo boxes, and I go to assign the sales rep
in the second set, it overwrites the first). So, basically, it only creates
one unique id in the JobEmployee table for each job. How can I solve for
this?

Thanks so much!
 
Suggestions:

1. Make sure the combo is bound to the RoleID field. If the combo is
unbound, it will show the same role on every row.

2. If you cannot see multiple rows in the subform, open the subform in
design view. Make sure its Default View is Continuous Form or Datasheet, not
Single Form.

3. Check the relationship between your Role and JobEmployee tables. I
suggested a text-based primary key in the Role table, but if you used an
AutoNumber in the Role table, in the JobEmployee table you will need a
Number field and in the lower pane of table design you should see size as
Long Integer and Default Value left blank. Presumably you did create the
relationship between these 2 tables with Referential Integrity (Tools |
Relationships.)

4. Check the properties of the RoleID combo. Its RowSource will be your Role
table. Column Count will be 2 if you used an AutoNumber, and the Column
Widths will be 0.
 
Back
Top