Staff Names occuring multiple times - Relationship issue

[colonelk]

Hi, and a big thanks in advance !

I'm re-writing a database based on capturing Corrective and Preventative
actions (CAPA) for QA. Previously the guy had 1 big table and several lookup
tables without relationships, and with table lookups !!! eek .

In the main table which captures most of the CAPA issue data we have the
need to capture the details of the CAPAInitiator, CAPAInvestigator,
ResponsibleManager, and CAPAActionsAssignee (amongst other things)

I'm trying to create 1 table (tblNames) with the name, email and company
name details for our employees and customers/suppliers, each of which may be
an Initiator, Investigator, ResponsibleManager or Assignee.

Now, its been a while since I dabbled in Access, but I cannot create
multiple relationships from the same table (tblNames) with the tblCAPA table
(i.e have multiple NamesID foreign keys in the CAPA table) and relate them.

The only way I've been able to do it is to have 4 tables (tblCAPAInitiator,
tblCAPAInvestigator, tblResponsibleManager, tblCAPAAssignee) and have the
foreign keys of those tables in tblCAPA. However I then have to replicate
the name, email and company name data across 4 tables. Seems like lots of
replication and no normalisation at all!

is there any way I can alter the relationships so that I have 1 table
(tblNames) that contains name, email, and company name data, and have the
main table refer to that one table for the name of the Initiator,
Investigator, and Responsible Manager etc etc even though the Initiator may
be a different person to the Investigator etc.......

Thanks

Tino

 

[Duane Hookom]

I'm not sure why you "cannot create multiple relationships from the same
table (tblNames) with the tblCAPA table". You can create relationships with
multiple instances of tblNames.

IMHO, you should not have fields like CAPAInitiator, CAPAInvestigator,
ResponsibleManager, and CAPAActionsAssignee. I would create a related table
with the primary key value from the main table, the primary key value from
tblNames and field identifying the role (CAPAInitiator, CAPAInvestigator,
ResponsibleManager, and CAPAActionsAssignee). This would remove your issue
and normalize your table structure.

 

[Larry Daugherty]

FWIW I'd rework things to get rid of those lookup fields before doing
anything else.

Then, I'd fully analyze the application as to what it's supposed to be
modeling in the real world. What are the inputs, what are the outputs
and what are the Entities involved? Of course, those last are some of
your tables. Design your new tables according to your analysis. The
relationships will come; probably after you've labored over the roles
in the design a bit.

Quite often things that model as complex don't really have to be.
Lots of many-many relationships don't really have to be implemented
that way.. In what you describe the key may be in how you identify the
various roles and what you have in your People table.

Taking Issues as the big deal; if that is the element of focus for a
form then you could step to various comboboxes, named for roles which
are based on queries that point back into the People table and that
present just the proper list of candidates for that role.

HTH

 

[colonelk]

Many thanks to you both.

I believe the other tables are pretty much as normalised as I can get them
Larry. It was just conceptualising this multiple role problem which I was
scratching my head over.

Many thanks to Duane on that count as I'd not thought of a separate role
table to include the primarys from CAPA and Names. It works well THANKS