Best way to incorporate 3 job functions

[carriey]

I'm stumped on how to go about designing this database. It currently houses
a number of different job functions which are all tied to locations
(Tbl_MAIN).

One of those functions is Inspections (which are conducted by agencies) and
I have this set up as an Inspection Table with a Deficiencies SubTable (and a
form, sub-form for data entry) - it works great the way it is. There can be
multiple Deficiencies attached to one inspection.

I need to set up 2 other roles. One is Obligations/Self-Dec's and one is
Internal Audits. While some of the information is the same in each (ie. a
staff person assigned, status, date closed), there are also quite a few
different fields required. Each function can have deficiencies assigned and
some are the same for all 3 roles but some are specific to the type.

For sure, each one needs to be on a seperate form but sometimes an Internal
Audit will require a Self-Dec (Self-Dec's and Obligations can come from
other sources too) so, I need some key information to auto-fill from the
Internal Audit form to the Self-Dec form. This will be specific to the
deficiency in the subtable though as the Internal Audit may turn up 5
deficiencies but only 2 require a Self-Dec. I was thinking that somehow I
could achieve this by having a Self-Dec required check box beside the
deficiency?

The reason I want the data to auto-fill is so that the person responsible
for Self-Dec's can then run a report to see what he has to do, and then
complete the information for his job rather than everyone passing paper and
emails back and forth as the workload is very high.

I have been struggling with whether I should modify my Inspection Table and
Deficiency Sub-table to include all 3 roles and have the user select either
Inspection, Self-Dec, Obligation, Internal Audit from a drop-down or whether
I should create a seperate table for each role. If I create a seperate table
for each role, do I use the same Deficiency Sub-Table or do I also create a
seperate one for each?

Reporting for each role (ie. counts of how many conducted, and what
deficiencies) need to be seperate although I realize this could be
accomplished even in the one table.

Just looking for some thoughts on which way would be the most efficient, and
how I can get the Internal Audit data recorded as a Self-Dec also where
required?

Appreciate your help - hopefully this made sense!

 

[Piet Linden]

I follow some of it, but not all of it. Before worrying about forms,
you absolutely have to get the table design right. Otherwise, you'll
end up with data that you cannot analyze/summarize - which is pretty
worthless. The easiest way to do it (well, easy is a relative term)
is to get out a pen and paper and write down a description of what's
going on in a [noun] [action verb] [noun] format.

For example...

an Agency conducts one or more Inspections. Each Inspection may
reveal one or more Deficiencies. ... so

Agency---(1,M)---Inspection---(1,M)---Deficiency

I'm sure that's not what you wanted to hear, but it's MUCH easier to
proof a design on paper (once you've figured out how to read the
diagram) than it is to do it after you have spent a ton of time
designing (or mis-designing) your database.

Basically, you need to identify all the nouns in the "world" you're
describing, and then the verbs that connect those nouns. Just create
sentences like those above. Once you have done that, you can start
diagramming and adding "things" to your map with verbs to connect
them. Once that's done, you can think about creating tables. But
honestly, before you understand how the real world things are related,
you cannot really build a database to model this situation.

I would start with a barebones description. and work my way out from
there.

Hope this helps a little.

 

[Fred]

What Piet Linden wrote is a pretty cool that I've not seen before to approach
the all-important step one.

If I may speak directly in an effort to be helpful, your post had a lot of
words but didn't have the needed ones which are definitions of your data
elements. I'm assuming that your own thought process on this has this same
issue/gap , and such a "cart before the horse" thinking process is hurting
your progress on this. The recommended sequence would be:

1. Define the data that you are trying to database, plus your mission.
Other than maybe a few references to the items of the next step (tables and
relationships) leave out database terminology at this step.

2. Define a table and relationship structure that will database the above
and support accomplishment of your mission.

3. Design queries, forms, reports etc. that will build on the above
foundation and accomplish your mission

Of course, this is just an outline. To get more help, you might want to do
#1 as a post and then ask folks in the forum to recommend a table/
relationship structure.