Form/Subform problem

[Karen]

I have a form that is based on a table which contains a list of employees and
their personal information. I have another table that includes only job
titles. I want to create a subform (TABULAR) for the job titles to show up
on the "EMPLOYEE" form as a subform, so the user setting up a new employee
can choose from a list a job title (check box?) (or in some cases more than
one title for each employee) I then want to report on this somehow.

Can anyone help direct me in the direction of how the following:
Linking of the two tables (Use relationships?, add fields to the existing
tables? base forms on a query instead of two ind tables?? Please advise

Thank you.

 

[Klatuu]

If an Employee will have multiple job titles, you need an EmployeeTitle table.
It needs a field for the Employee's ID (The primary key field of the
employee table) and a field for the ID (primary key) of the Titles table. So
each employee will have one row in the table for each title they have.
For reporting and display purposes, use queries that join the employee table
to the EmployeeTitle table, and the Title table to the EmployeeTitle table.

 

[KARL DEWEY]

so the user setting up a new employee can choose from a list a job title
(check box?) (or in some cases more than one title for each employee)
Do not use checkboxes to show title of employee. You would need a checkbox
for each possible title and queries to extract all employees with a given
title will be a BEAR. If you add a new title you will need to change ALL
your queries, forms and reports.
Create a table named Titles like this --
Titles --
TitleID - autonumber - primary key
Title - text
Description - text

Create a table named EmpTitle like this --
EmpTitle --
EmpTitleID - autonumber - primary key
EmpID - number - long integer - foreign key
TitleID - number - long integer - foreign key
Assigned - DateTime
WithDrawn - DateTime
Remarks - memo

Set relationships one-to-many from employee table to EmpTitle and from
Titles to EmpTitle.

use a subform on employee form to select/display titles. Form/subform
linked Master/Child on EmpID. Use comb to select title.

 

[Karen]

Thanks Karl. I will try this - Let me ask though.... using a "combo box" on
my subform will not allow someone to choose more than one description.
Unless I am understanding wrong, isn't a tabular subform my best way to go?

Thanks

 

[KARL DEWEY]

I would use a Datasheet view.

 

[Karen]

But using a combo box, within a Datasheet view will not allow a user to
select more than one option within the combo... or am I Missing something?
Thanks

 

[KARL DEWEY]

If they have more than one title they will have additional rows in the
datasheet.
See Dave Hargis (Klatuu) post.

 

[Karen]

OK, I will try it.. thanks

If they have more than one title they will have additional rows in the
datasheet.
See Dave Hargis (Klatuu) post.

 

[Karen]

Karl - HELP!!!

So I did what you described... it's not working....... I'm good with
creating the two tables and I think my issue is with the relationships. You
said "Set relationships one to many from Employee Table" to Employee Title
table and from titles to employee titles." Employee table does not have an
ID# today...... I believe I did this correctly...

Next you said" Use a subform on employee form to select/display titles" with
linking details.. I believe you mean where the employees are stored in the
employee table, not one of the tables I created.. please correct me if I am
wrong. I also linked as you described or tried to except that in title
table, I do not have Employee ID so maybe I am not using the correct table?

Either way, not working. can you help explain what I may be doing wrong?
Thanks much!!!