table joins

[tlynn]

I have 4 tables (Quality, Safety, Process, Maintenance) each with the primary
key set as "Report#" (no dupliactes allowed). I would like to relate these 4
tables to one table (Corrective Actions) that would store common information
about the 4 tables by Report#. I am able to set up the
one-to-many-relationship and enforce referential integrity. I have created a
Corrective Action subform in each of the 4 forms but when I go to enter data
it tells me "I cannot add or change a record because a related record is
required in table "Safety". I had 4 tables in one large table (7,500
records) but was taking to long to filter data. Can I relate 4 tables to
one? If so, what am I missing? Intermediate user of Acess 2003 SP1.

 

[Jeff Boyce]

You've described "how" you are trying to do something (your table structure,
your form design, ...).

Would you please describe "what" you want to accomplish, and a bit more
about the data itself (an example would help)? In describing "what", turn
off your computer and use terms an 80 year old grandmother might relate
to...

 

[tlynn]

Hi Jeff,

I want the one Corrective Action table to act as the common table to store
corrective action data that relates to each of the 4 tables by Report# or
some common ID. I am trying to avoid one BIG table which would consist of
the Quality, Safety, Maintenance, Process data and one joined smaller
Corrective Action table.

Corrective Action Table:
Report#
Team Members
Root Cause
Corrective Action
Preventative Action

Quality, Safety, Maintenance, Process Tables:
Report#
Part#
Description of Problem
Etc. (many other fileds)

I Hope this helps

Thank You,
Tim.

 

[Jeff Boyce]

Tim

An 80 year old grandmother doesn't know from "tables"... you're still
describing what you want to accomplish (a business need) in terms of "how"
(table structure, etc.).

And on what basis have you decided that a "BIG table" is undesirable? Is
this a personal issue, or are there relational database design
considerations? And define "BIG" -- are you concerned with how many rows,
how many bytes, how many fields, ...?

If it doesn't infringe on proprietary information, please provide an example
.... not of the table structure you are already using, but of the data you
wish to do something with (hint, hint, hint!).

 

[tlynn]

Jeff,

Sorry, I am trying my best to describe my situation with the limited
knowledge I have with Access.

I originally started out with four seperate databases (Quality Safety,
Process and Maintenance), each seperate projects/files. These databases were
used to enter data that related to part quality problems, equipment failues,
accidents, etc.). Just recently, I wanted to create a fifth database (common
to the other four) to enter data that described what we did to correct the
problem. Only specific records in the other four databases would required a
record in the corrective action database.

I made these databases common in structure but still maintaned them as four
separate databases, and then placed them into one project/file. I tried to
join the four databases to the corrective action database, with a One-to-Many
join enforcing R/I, but then could not enter data without the error.

I proceded to combine the four databases into one database (Database1) and
joined this one common database to the corrective action database (Database2).

Database1 (Quality, Safety, Process Maintanance) data currenly has 7800
records (rows of data) with about 25 fileds per row. Data is entered into
this database at a rate of 10 records per workday. Joined to this database
is the corrective action database (Database2).



Database1 - Quality, Safety, Process Maintenance Data:
Type (Quality, Safety, Process, Manitenance)
PART# (1234)
Report# (5678)
Description (1.500 length u/s to 1.200)
Etc Data (about 15 more data/number/date fields).

Database2 - Corrective Action Data:
Report# (1234)
Due Date (12/16/05)
Car Team (Names)
Root Cause (255 characters)
Corrective Action (255 characters)
Preventative Action (255 characters)
Date Closed (12/16/05)

I am seeking help because my current configuration with Database1 and
Database2 seems to have slowed down my filter/query time (5-7 seconds per
search/query). So I was again trying to split the table back into four
sperate tables with a fifth joined table for corrective action data.

I have no problem using my current configuration (Database1 and Database2)
except for the slow filter/query times. I was hoping that the four separate
databases linked to the fifth common database would resolve my search time.

Thanks,
Tim

 

[Jeff Boyce]

Tim

Your knowledge of/experience with Access is not the issue ... I assume
that's one reason you're posting in the newsgroups.

What I've been trying to get you to do is to step (totally) away from the
way you've approached your situation and explain it in non-Access,
non-database, non-technical terminology. It may be that what you want to
accomplish can be done a different way than you've used ... if only it were
clearer what it is you are trying to accomplish!

Let me try this... I'll paraphrase what I suspect you are trying to do. You
respond with corrections where I don't understand. Once there's a clearer
picture, perhaps other folks will chime in with suggestions...

You are recording information about incidents (you are calling them other
things, and categorizing them).

Some incidents relate to Quality issues, some to Safety issues, some to
Process issues and some to Maintenance issues.

I'm not clear if what you need to know about Quality issue incidents is
appreciably different that what you need to know about Safety (or ...) issue
incidents. However, if most of the information about the incident has the
same characteristics (e.g., ReportedBy, DateReported, ...) and it is only
the "category" (Quality, Safety, ...) and a description that are different,
you could use a single table (oops! sorry, I jumped ahead).

You also seem to want to record information about how the issues get
handled. Based on what you've said so far, it sounds like you would keep
largely similar information about "Corrective Action", regardless of the
category of incident. Perhaps you are keeping something like:
DateResolved, ActionTaken, ResolvedBy, ... (oops again!).

Does this accurately reflect your situation? You have incidents, with
characteristics, and you have resolutions ("Corrective Actions"), with
characteristics. You want to connect one/more Corrective Action Taken to
the appropriate incident.

Let me know where I read too much into your descriptions, and we'll see what
threads develop.

Good luck

Jeff Boyce
<Office/Access MVP>

 

[tlynn]

Jeff,

You are absolutely correct in your description.
No corections needed.

 

[tlynn]

Jeff,
This may be a duplicate post, not sure if 1st went through.
You are absolutely correct in how you describe my situation.
Continue.

 

[Jeff Boyce]

Tim

Again, perhaps because I'm not there and can't see what you're working with,
this is just one person's opinion...

tblIncident (this table holds information about incidents)
-IncidentID (a Primary Key, an Access Autonumber, unless you have an
incident reporting system that creates a unique identifier for each
incident)
-IncidentCategory (Quality, Safety, ... -- you could create another
table to hold IncidentCategory, then use that here)
-DateReported
-ReportedBy (you could create another table to hold the folks who can
report, referred, get referred to, carry out corrective action, etc., then
use it everywhere you'd need a person)
-IncidentDescription
-DateReferredForAction
-ReferredTo
-ReferredBy
-(... any other characteristics of the incident that you need to keep
track of)

trelCorrectiveAction (the table that tracks what gets done)
-CorrectiveActionID (Primary Key, ... see above...)
-ActionDate
-ActionTakenBy
-ActionDescription
-OriginalReporterContacted (this was my own invention -- letting the
original caller know what happened)
-(... any other characteristics of the Corrective Action that you need
to keep track of)

Once you have your data in tables like these (and any other
supporting/lookup tables, i.e., persons, incident categories, etc.), you can
create queries to join corrective action(s) to incident. You can use forms
for collecting both the incident and the related corrective action(s). You
can use reports to print out detail or summary of incident and action.

Hope that helps...

Regards

Jeff Boyce
<Office/Access MVP>

 

[tina]

Jeff, PMFJI, but...in tblCorrectiveAction, are you missing the foreign key
field IncidentID from tblIncidents? (or maybe i'm missing something...)

 

[Jeff Boyce]

Big time! Thanks for catching that -- no foreign key, no Parent! Don't you
just love the brain power the groups bring!

I must have gotten so wrapped up in detail that I forgot the forest.

Regards

Jeff Boyce
<Office/Access MVP>