Inspections and Treatments

Looking for some db design help. Although this db is going to be more
complex, I'd like to start with a portion of it first.

tblWork
WorkID
DateSched
WorkDescr
WorkType

tblInspections
InspectionID
InspStart
InspEnd
Notes

tblTreatments
TreatmentID
TreatStart
TreatEnd
ChemUsed
ChemAmt
ChemUnit

So, I will need to have many inspections, many treatments, and for now, one
work item. Ultimately, I will need a "Work" form that will have 2 subforms
(inspections, treatments).

1. Should I include WorkID's in each of the tblInspection and tblTreatment
tables? Or should I include InspectionID and TreatmentID in the tblWork?

2. Can I have one "Work" form with 2 subforms of tblInspection and
tblTreatment?

3. What would change if tblWork will have a many-to-one relate with another
table?

A DB records real-world data and real world relationships. "One to one"
relationships are documented by putting them in a table, and relationships
with "many" in them are documented by linkages between records (tables)
First you have to answer (at least to yourself) What are the real world
relaitonships between these items? Your post implies that treatment items
are related to work items, and that inspection items are related to work
items. Now, are treatment items related to inspection items? You also
have to determine whether any of these relationships go beyond "one-to-many"/
"many-to-one" to "many-to-many". The answer to your question #3 is "lots".
(and #2 is "Yes")

After you determined the real world relationships, make your table structure
(*tables and linksages) follow those. And then create queries, forms and
report that fufil your mission, built upon the above table structure.

Hope that helps a little.

Fred

Thnks, I appreciate it.

Ok, so then here's what isn't working for me....

tblWork
WorkID
EmpID (FkID: tblEmployees)
CustomerID (FkID: tblCustomers)

tblEmpolyees
EmpID
Inits

tblCustomers
CustomerID
Address

tblInspections
InspID
WorkID (FkID: tblWork)
DateInsp

tblTreatments
TreatID
WorkID (FkID: tblWork)
DateTreat


I am having difficulting setting up the data entry process. I don't expect
a detailed solution, but I cannot seem to understand how to setup forms (with
the wizard) and keep referential integrity intact. For example,

CustomerForm
WorkSubform (incld: tblEmployees)
InspectionSubform (incld: tblInspections)

Then I guess I'd have to have a similar set of forms for the treatment data
entry

CustomerForm
WorkSubform (incld: tblEmployees)
TreatmentSubform (incld: tblTreatments)


The problem I am encountering is upon entering a new customer, then I cannot
add any new record into the WorkSubform (it just "dings" and I cannot type
any values into the field).

What am I missing here?





"Fred" wrote:

A DB records real-world data and real world relationships. "One to one"
relationships are documented by putting them in a table, and relationships
with "many" in them are documented by linkages between records (tables)
First you have to answer (at least to yourself) What are the real world
relaitonships between these items? Your post implies that treatment items
are related to work items, and that inspection items are related to work
items. Now, are treatment items related to inspection items? You also
have to determine whether any of these relationships go beyond "one-to-many"/
"many-to-one" to "many-to-many". The answer to your question #3 is "lots".
(and #2 is "Yes")

After you determined the real world relationships, make your table structure
(*tables and linksages) follow those. And then create queries, forms and
report that fufil your mission, built upon the above table structure.

Hope that helps a little.

Fred

Your main foundation is understanding your data items and the relationship
between them and then your tables and the relationships between them / their
record. . Your only partially describing these makes me guess that you are
not giving that part of the job sufficient importance and work.

Here's a mixture of confiriming my guesses, things that your structure
implies / hints at, plus making suggestions.

-A "Work" item is for one and only one customer
-A "Work" item is done by one and only one employee
-Your intent is to record occurences of inspections and occurences of
treatments. -You are not trying to create any standardized "Picklist"of
inspections or treatments. You intend to just free-form describe the
treatment (If this is wrong, and youARE trying to do this, then you
would, for example, need 2 "inspection" tables, one is occurences of
inspecitons, the other the Picklist" of standardized inspections.)

A "Work" item may include many instances of inspections. But that occurence
of an inspection relates only to that work item
A "Work" item may include many occurences of treatments. But that occurence
of a treatrment relates only to that "Work" item.

IF all of the above is true, then, to the extent that you described it, what
you listed is OK. But here's what you forgot to do or say:

IMPORTANT!: The namesake ID of each table is set to be it's Primary Key.
E.G. "WorkID" is set as the PK of the "Work" table etc.

Do "Show only when they are equal" joins from Work table to Customers table
and Work table to Employee Table.

Do a "Show all Work items......" join from work table to inspections table.
Do a "Show all Work items......" join from work table to treatments table.

Create datasheet style forms (which will be used as subforms) for
inspections and treatments.

I'm good at organizing these things and data structures, but other people
who read these know Access 10 times better than me and I welcome them to
comment on or add to my Access stuff.

Create a query which shows all , customer and employee fields. Use it as a
record source for a "Work" form which show all desired fields from those 3
tables.

Add the already made inspections and treatments forms to it as subforms
using subform wizard.

I think that that should work.

Sincerely,

Fred

i basically agree with Fred's tables/relationships analysis, and have
comments on form design. see below.


"Fred" wrote in message
...
Your main foundation is understanding your data items and the relationship
between them and then your tables and the relationships between them /
their
record. . Your only partially describing these makes me guess that you
are
not giving that part of the job sufficient importance and work.

Here's a mixture of confiriming my guesses, things that your structure
implies / hints at, plus making suggestions.

-A "Work" item is for one and only one customer
-A "Work" item is done by one and only one employee
-Your intent is to record occurences of inspections and occurences of
treatments. -You are not trying to create any standardized "Picklist"of
inspections or treatments. You intend to just free-form describe the
treatment (If this is wrong, and youARE trying to do this, then you
would, for example, need 2 "inspection" tables, one is occurences of
inspecitons, the other the Picklist" of standardized inspections.)

A "Work" item may include many instances of inspections. But that
occurence
of an inspection relates only to that work item
A "Work" item may include many occurences of treatments. But that
occurence
of a treatrment relates only to that "Work" item.

IF all of the above is true, then, to the extent that you described it,
what
you listed is OK. But here's what you forgot to do or say:

IMPORTANT!: The namesake ID of each table is set to be it's Primary Key.
E.G. "WorkID" is set as the PK of the "Work" table etc.

Do "Show only when they are equal" joins from Work table to Customers
table
and Work table to Employee Table.

if Fred is referring here to the Join Type in the Edit Relationships dialog
of the Relationships window....well, frankly, i never bother with that. it
isn't necessary at the table level, and when i write queries i set the joins
as needed at the time.


Do a "Show all Work items......" join from work table to inspections
table.
Do a "Show all Work items......" join from work table to treatments table.

ditto above.


Create datasheet style forms (which will be used as subforms) for
inspections and treatments.

datasheet style not a problem here, but not a requirement, either.


I'm good at organizing these things and data structures, but other people
who read these know Access 10 times better than me and I welcome them to
comment on or add to my Access stuff.

Create a query which shows all , customer and employee fields. Use it as
a
record source for a "Work" form which show all desired fields from those 3
tables.

this isn't necessary, and is often counterproductive, but it's a common
mistake made by inexperienced Access developers. for a *data entry* form,
it's rarely necessary to use more than one table in the RecordSource, and on
those occasions it's usually two tables with a one-to-one relationship.

first, before forms, make sure the table relationships are set up in the
Relationships window, with the links going *from* the parent table *to* the
child table. for example: from tblCustomers to tblWork. from tblWork to
tblInspections. and be sure to checkmark the Enforce Referential Integrity
option in the Edit Relationships dialog of each link. now you're ready to
move on to forms.

from your post, your process flow seems to be to start with a new customer,
or with an existing customer record, and then add a work record and its'
related inspections and treatments. suggest the following setup - and, btw,
don't bother trying to do this using a form wizard. just set it up yourself.

create a form bound to tblCustomers, and call it frmCustomers. in Design
view, open the Properties box (click View | Properties on the menu bar), and
set the FormView property to SingleForm.
create a form bound to tblWork, and call it frmWork. in Design view, include
a combobox control bound to the EmpID foreign key field, with its' RowSource
set to tblEmployees. set the FormView property to SingleForm.
create a form bound to tblInspections. you can set the FormView to
SingleForm, ContinuousForms, or Datasheet, depending on what will best suit
your data entry needs.
create a form bound to tblTreatments. ditto above re the FormView setting.

in Design view of frmWork, add a subform control (you can add it from the
Toolbox toolbar, which in turn you'll find by clicking View on the menu
bar). open the Properties box (View menu again) and set the subform
control's Name property as ChildInspections.
set the SourceObject property as frmInspections.
set the LinkChildFields property as WorkID (this refers to the foreign key
field in tblInspections, which is the table used as the RecordSource of
frmInspections).
set the LinkMasterFields as WorkID (this refers to the primary key field in
tblWork, which is the table used as the RecordSource in frmWork).
now you have an Inspections subform in frmWork, and linked to frmWork. easy
as pie!

Still in Design view of frmWork, add another subform control and name it
ChildTreatments. set the SourceObject as frmTreatments, and set the
LinkChildFields and LinkMasterFields to the corresponding foreign key and
primary key fieldnames, as described above. then save and close frmWork.

open frmCustomers in Design view and add a subform control, naming it
ChildWork. set its' SourceObject to frmWork, its' LinkChildFields to
CustomerID (the foreign key field in tblWork), and its' LinkMasterFields to
CustomerID (the primary key field in tblCustomers). save and close the form.

now when you open frmCustomers, you can add a new customer record or go to
an existing customer record, then add a new work record or go to an existing
one, then add or edit inspection records and/or treatment records for that
work record for that customer.

if you find that using nested subforms results in a large and busy, crowded
form for data entry, then you might consider opening a form to add/edit
customer records only. then open a separate form (not a subform) to add/edit
work records and their related inspection and treatment records; in the work
form, you can use a combobox control bound to foreign key field CustomerID,
with RowSource as tblCustomers, to choose the customer for each new work
record.

and if you really want to get creative, you can add a tab control to the
work form, and put the inspection subform on one tab and the treatment
subform on another tab. that saves space and makes the form look simpler and
cleaner. you can do this whether the work form is opened separately, or is a
subform on the customer form.

the only thing that raises a question for me here, is a possible
relationship between inspections and treatments. does your company sometimes
do treatments after an inspection, but also sometimes do treatments without
a preceding inspection? if yes, then the current setup probably makes sense.
but if a treatment is *always* preceded by an inspection, i wonder if
tblTreatments should be a child table as the "many" side of a one-to-many
relationship with tblInspections. if the latter is the case, then the forms
design above would have to be revamped to handle that tables relationship.

hth


Add the already made inspections and treatments forms to it as subforms
using subform wizard.

I think that that should work.

Sincerely,

Fred

Hello Tina,

A Thank You from me too as I learned a bunch from your post.

One question. I was thinking that they would want to sho the Customer and
Employee names in the main work form, and suggested doiing this by using a
three table query as a recordsource. You mention that that was not a good
idea. Did you mean that that "display" goal was a bad idea, or that my
suggested method was? If it's the latter, what would be the preferred way
to do that?

Thanks

Fred

multi-table queries are often not updateable. and even when they are, it's a
waste of resources to pull fields that you don't need into the RecordSource.
i often use multi-table queries for data *review* forms which are read-only,
to avoid using combobox controls - for a cleaner look to the form. but in a
data *entry* form, where the user will add/edit data, a combobox control
with RowSource set to the related table is simpler and cleaner than
including that related table in the form's RecordSource.

re your specific idea of showing the customer and employee in the work form,
see the following from my previous post:

create a form bound to tblWork, and call it frmWork. in Design view,
include
a combobox control bound to the EmpID foreign key field, with its'
RowSource
set to tblEmployees.

that takes care of adding/editing/"seeing" the employee in the work form.

open frmCustomers in Design view and add a subform control, naming it
ChildWork. set its' SourceObject to frmWork, its' LinkChildFields to
CustomerID (the foreign key field in tblWork), and its' LinkMasterFields
to
CustomerID (the primary key field in tblCustomers). save and close the
form.

in the "work form as subform of customer form" solution, the customer record
is the parent record, so the customer will always be "seen" in conjunction
with any related work record.

if you find that using nested subforms results in a large and busy,
crowded
form for data entry, then you might consider opening a form to add/edit
customer records only. then open a separate form (not a subform) to
add/edit
work records and their related inspection and treatment records; in the
work
form, you can use a combobox control bound to foreign key field
CustomerID,
with RowSource as tblCustomers, to choose the customer for each new work
record.

in the "work form as separate data entry form" solution, that takes care of
adding/editing/"seeing" the customer in the work form.

hth


"Fred" wrote in message
...
Hello Tina,

A Thank You from me too as I learned a bunch from your post.

One question. I was thinking that they would want to sho the Customer
and
Employee names in the main work form, and suggested doiing this by using a
three table query as a recordsource. You mention that that was not a
good
idea. Did you mean that that "display" goal was a bad idea, or that my
suggested method was? If it's the latter, what would be the preferred
way
to do that?

Thanks

Fred

Fred and Tina,

Your commentaries were incredibly insightful and have fully helped me to
develop exactly what I need. It's all very much appreciated.

Thank You,

j

"Fred" wrote:

Your main foundation is understanding your data items and the relationship
between them and then your tables and the relationships between them / their
record. . Your only partially describing these makes me guess that you are
not giving that part of the job sufficient importance and work.

Here's a mixture of confiriming my guesses, things that your structure
implies / hints at, plus making suggestions.

-A "Work" item is for one and only one customer
-A "Work" item is done by one and only one employee
-Your intent is to record occurences of inspections and occurences of
treatments. -You are not trying to create any standardized "Picklist"of
inspections or treatments. You intend to just free-form describe the
treatment (If this is wrong, and youARE trying to do this, then you
would, for example, need 2 "inspection" tables, one is occurences of
inspecitons, the other the Picklist" of standardized inspections.)

A "Work" item may include many instances of inspections. But that occurence
of an inspection relates only to that work item
A "Work" item may include many occurences of treatments. But that occurence
of a treatrment relates only to that "Work" item.

IF all of the above is true, then, to the extent that you described it, what
you listed is OK. But here's what you forgot to do or say:

IMPORTANT!: The namesake ID of each table is set to be it's Primary Key.
E.G. "WorkID" is set as the PK of the "Work" table etc.

Do "Show only when they are equal" joins from Work table to Customers table
and Work table to Employee Table.

Do a "Show all Work items......" join from work table to inspections table.
Do a "Show all Work items......" join from work table to treatments table.

Create datasheet style forms (which will be used as subforms) for
inspections and treatments.

I'm good at organizing these things and data structures, but other people
who read these know Access 10 times better than me and I welcome them to
comment on or add to my Access stuff.

Create a query which shows all , customer and employee fields. Use it as a
record source for a "Work" form which show all desired fields from those 3
tables.

Add the already made inspections and treatments forms to it as subforms
using subform wizard.

I think that that should work.

Sincerely,

Fred

Hello Tina,

Thanks again. But after numerous futile attempts (with and without the
wizard turned on) I could not get a combo box to show the other fields in the
ancillary table. For example, to have it continue to show the employee name,
not just the employee ID #. It shows the other fields only when actively
making the selection; after that it only shows the stored / linked field.
Could I trouble you to tell me the key steps?

Sincerely,

Fred

"does your company sometimes do treatments after an inspection, but also
sometimes do treatments without a preceding inspection? if yes, then the
current setup probably makes sense. but if a treatment is *always* preceded
by an inspection, i wonder if tblTreatments should be a child table as the
"many" side of a one-to-many relationship with tblInspections. if the latter
is the case, then the forms
design above would have to be revamped to handle that tables relationship."


Tina,

Thanks for the tip. While I fully understand the options here, I believe
the existing workflow will work just fine. "Work" *could be* either
Inspections or Treatments, but a treatment record is not always preceded by
an inspection --- there may be a moment where a treatment is simply done
based upon an existing location and not dependent upon inspecting the
location first.

I certainly can appreciate the thought. I'm acting as developer and dba
here, so trying to consider all of the what-if's to get this setup correctly
has been quite interesting. What I am finding out is that doing both app-dev
and db-dev is actually making me a better developer overall.

Thanks again,

j






"tina" wrote:

i basically agree with Fred's tables/relationships analysis, and have
comments on form design. see below.


"Fred" wrote in message
...
Your main foundation is understanding your data items and the relationship
between them and then your tables and the relationships between them /
their
record. . Your only partially describing these makes me guess that you
are
not giving that part of the job sufficient importance and work.

Here's a mixture of confiriming my guesses, things that your structure
implies / hints at, plus making suggestions.

-A "Work" item is for one and only one customer
-A "Work" item is done by one and only one employee
-Your intent is to record occurences of inspections and occurences of
treatments. -You are not trying to create any standardized "Picklist"of
inspections or treatments. You intend to just free-form describe the
treatment (If this is wrong, and youARE trying to do this, then you
would, for example, need 2 "inspection" tables, one is occurences of
inspecitons, the other the Picklist" of standardized inspections.)

A "Work" item may include many instances of inspections. But that
occurence
of an inspection relates only to that work item
A "Work" item may include many occurences of treatments. But that
occurence
of a treatrment relates only to that "Work" item.

IF all of the above is true, then, to the extent that you described it,
what
you listed is OK. But here's what you forgot to do or say:

IMPORTANT!: The namesake ID of each table is set to be it's Primary Key.
E.G. "WorkID" is set as the PK of the "Work" table etc.

Do "Show only when they are equal" joins from Work table to Customers
table
and Work table to Employee Table.

if Fred is referring here to the Join Type in the Edit Relationships dialog
of the Relationships window....well, frankly, i never bother with that. it
isn't necessary at the table level, and when i write queries i set the joins
as needed at the time.


Do a "Show all Work items......" join from work table to inspections
table.
Do a "Show all Work items......" join from work table to treatments table.

ditto above.


Create datasheet style forms (which will be used as subforms) for
inspections and treatments.

datasheet style not a problem here, but not a requirement, either.


I'm good at organizing these things and data structures, but other people
who read these know Access 10 times better than me and I welcome them to
comment on or add to my Access stuff.

Create a query which shows all , customer and employee fields. Use it as
a
record source for a "Work" form which show all desired fields from those 3
tables.

this isn't necessary, and is often counterproductive, but it's a common
mistake made by inexperienced Access developers. for a *data entry* form,
it's rarely necessary to use more than one table in the RecordSource, and on
those occasions it's usually two tables with a one-to-one relationship.

first, before forms, make sure the table relationships are set up in the
Relationships window, with the links going *from* the parent table *to* the
child table. for example: from tblCustomers to tblWork. from tblWork to
tblInspections. and be sure to checkmark the Enforce Referential Integrity
option in the Edit Relationships dialog of each link. now you're ready to
move on to forms.

from your post, your process flow seems to be to start with a new customer,
or with an existing customer record, and then add a work record and its'
related inspections and treatments. suggest the following setup - and, btw,
don't bother trying to do this using a form wizard. just set it up yourself.

create a form bound to tblCustomers, and call it frmCustomers. in Design
view, open the Properties box (click View | Properties on the menu bar), and
set the FormView property to SingleForm.
create a form bound to tblWork, and call it frmWork. in Design view, include
a combobox control bound to the EmpID foreign key field, with its' RowSource
set to tblEmployees. set the FormView property to SingleForm.
create a form bound to tblInspections. you can set the FormView to
SingleForm, ContinuousForms, or Datasheet, depending on what will best suit
your data entry needs.
create a form bound to tblTreatments. ditto above re the FormView setting.

in Design view of frmWork, add a subform control (you can add it from the
Toolbox toolbar, which in turn you'll find by clicking View on the menu
bar). open the Properties box (View menu again) and set the subform
control's Name property as ChildInspections.
set the SourceObject property as frmInspections.
set the LinkChildFields property as WorkID (this refers to the foreign key
field in tblInspections, which is the table used as the RecordSource of
frmInspections).
set the LinkMasterFields as WorkID (this refers to the primary key field in
tblWork, which is the table used as the RecordSource in frmWork).
now you have an Inspections subform in frmWork, and linked to frmWork. easy
as pie!

Still in Design view of frmWork, add another subform control and name it
ChildTreatments. set the SourceObject as frmTreatments, and set the
LinkChildFields and LinkMasterFields to the corresponding foreign key and
primary key fieldnames, as described above. then save and close frmWork.

open frmCustomers in Design view and add a subform control, naming it
ChildWork. set its' SourceObject to frmWork, its' LinkChildFields to
CustomerID (the foreign key field in tblWork), and its' LinkMasterFields to
CustomerID (the primary key field in tblCustomers). save and close the form.

now when you open frmCustomers, you can add a new customer record or go to
an existing customer record, then add a new work record or go to an existing
one, then add or edit inspection records and/or treatment records for that
work record for that customer.

if you find that using nested subforms results in a large and busy, crowded
form for data entry, then you might consider opening a form to add/edit
customer records only. then open a separate form (not a subform) to add/edit
work records and their related inspection and treatment records; in the work
form, you can use a combobox control bound to foreign key field CustomerID,
with RowSource as tblCustomers, to choose the customer for each new work
record.

and if you really want to get creative, you can add a tab control to the
work form, and put the inspection subform on one tab and the treatment
subform on another tab. that saves space and makes the form look simpler and
cleaner. you can do this whether the work form is opened separately, or is a
subform on the customer form.

the only thing that raises a question for me here, is a possible
relationship between inspections and treatments. does your company sometimes
do treatments after an inspection, but also sometimes do treatments without
a preceding inspection? if yes, then the current setup probably makes sense.
but if a treatment is *always* preceded by an inspection, i wonder if
tblTreatments should be a child table as the "many" side of a one-to-many
relationship with tblInspections. if the latter is the case, then the forms
design above would have to be revamped to handle that tables relationship.

hth


Add the already made inspections and treatments forms to it as subforms
using subform wizard.

I think that that should work.

Sincerely,

Fred