Relationship problem

[Lambi000]

I have two tables, one with employee names and the other with work groups
they will be in. There are many groups and the employees can be in any
number of them. None of them have been assigned yet, so I have created a
table and form for HR to use for the assignments.

The table has the Employee ID and the Job ID and it works fine with the form
and updates the table properly, but when I query from the table to get the
information for a report, I am never successful in getting the name from the
Employee table or the Group name from the Group table. And if I do get a
name, it's the wrong one.

I have tried every single way of putting the relationship together, but it
just never works right. All I want is the Employee ID in the Assignment
table to pull the appropriate name from the Employee table so it can show up
in a report. The same with the groups.

What am I missing here?

 

[BruceM]

An employee may be in many groups, and a group may have many employees.
That is a many-to-many relationship, which you need to resolve through a
third table, often called a junction table. It contains its own primary
key, plus an EmployeeID field and a GroupID field to correspond to the
primary key fields from the Employee and Group tables. There is a
one-to-many relationship between each of the PK fields and the corresponding
fields in the junction table.
You would typically have a main form based on either the Employee table or
the Group table, and a continuous subform based on the junction table. If
you have based the main form on the Group table, the juction table would
typically contain a combo box that gets its row source from the Employees
table. It would be bound to the EmployeeID field. Basing the main form on
Group will let you fill in the group roster. Basing it on Employee will let
you view all of an employees group assignments. You could have both,
depending on your needs. Same for reports.
You mentioned JobID, but made no mention of how this fits into the picture.

 

[Lambi000]

It worked beautifully. I did have a problem with this because I had the the
GroupID set up as a combo box in the junction table, but when I got rid of
that, I had a very nice working system here.

The Job ID is a one-to-many relationship. Every job can have a number of
groups workiing on it. I haven't done anything with that yet. Any
suggestions?

 

[BruceM]

That would depend somewhat on what you want to see on your Job form or
report. In general you would add a JobID to the Groups table, and link Job
to Group one-to-many. If you want to see a listing of all the groups at
once you would use a continuous form, but you would not be able to see the
group rosters without opening a separate form, or something of that sort.
This is because a continuous form can only be used at the last level of a
form > subform setup. If you use single form as the default view for your
Groups form in this Job/Group setup, you can then set up a form based on the
junction table as a subform to that form, but you will only be able to view
the groups one at a time.
I know there are ways that you could view a listing of all the groups (maybe
a list box) while still seeing the roster details of a single group, but I
am not the best person to advise on this. I could probably come up with
something, but if I happen upon the best choice (or even one of several
valid options) it would be by chance. I really don't know the best way, so
if you need more than what I have been able to offer, and if nobody jumps in
during the next twelve hours or so, you may do best to start a new thread.
If you do so, it would help to summarize your table structure in a format
such as:

tblJob
JobID (primary key)
StartDate
Client, etc.

tblEmployee
EmployeeID (primary key)
LastName, etc.

tblGroup
GroupID (primary key)
JobID (foreign key)
Group Name, etc.

tblGroupRoster (junction table)
RosterID (primary key)
EmployeeID (foreign key)
GroupID (foreign key)

When you describe your forms, name the tables from which they are built.
For a combo box, name the row source table (or query).

I'm not sure why you had a problem with the GroupID set up as a combo box.
Is the main form based on tblEmployee? Was the combo box on the form based
on the junction table, or was it a lookup field within the table? If it was
a combo box within the table, you are well rid of that.

 

[Lambi000]

Great. I have a cascading form that has the top level of the project, then
all the layers down from that down to the Project levels for maintenance by
the HR staff. Then I have a form for matching employees and Groups as in the
first discussion. I print reports that show HR whatever they wish to see in
that way. I have to admit that it's pretty impressive.

Thanks for all your help. I really appreciate it!!

 

[BruceM]

I guess you're saying it has all worked out pretty well so far. Glad I
could help.