Les wrote:
Good day,
I am new to Access and need a question answered please.
I have a form based on a table (tblQualityEmployees), for some of the
fields it looks up a value in another table (tblGroups).
The tblQualityEmployees consists of the following:
EmployeeID FirstName LastName Group1 Group2 Group3 Group4 Group5
The tblGroups consists of the following:
GroupID Groups Description
What i want to do is, in the form have text box that displays the
Group description next to each group. I tried making the "Control
Source" field Group1 combo. This brings in the relevant ID number
which is fine, but i want to see the discription which is in field 3
and not field 1.
Is this possible or must i also have the description in the first
table ?
LEt's step back a bit and consider changing your structure a little.
Create a 3rd table, tblEmployeeGrp
EGrpID
EmployeeID
GroupID
For each group that an employee is part of, you have one record in this
3rd table. That way if the employee is only part of one group there's
just one record; if they're part of 10 groups, you can accommodate it.
(Oh, and by the way, you'd then remove those group1 to group5 fiels in
tbleEmployees.)
Now, for your form, you 'll have a master form based on tblEmployees,
and then a subform based on tblEmployeeGroup. The subform will be
driven by a query, which will include tblemployeeGrp and tblGroups.
You'll pull the Description from tblGroups, the other stuff from
tblEmployeeGrp.
I hope this helps. There's a lot of concept here, about forms and
subforms, related tables, queries, etc. You'll have to look in Help, or
at the sample databases, or get an Access book if you want to pursue the
details.
Good luck.