Inspections and Treatments

[jamesfreddyc]

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?

 

[Fred]

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

 

[jamesfreddyc]

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]

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

 

[tina]

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

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

 

[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

 

[tina]

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

 

[jamesfreddyc]

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]

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

 

[jamesfreddyc]

"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]

i agree - since treatments may exist independent of inspections, then the
tables design laid out earlier sounds correct.

and i agree, too, that process analysis, both from the broader view of
business needs and the narrower view of user work flow, skill level, etc, is
quite an eye-opener. what i've often found is that even the people who
perform the work don't realize *everything* that goes into it (what i call
"where did it come from and where is it going") - and management almost
never does! <g>

 

[tina]

first key step: forget the wizard. you need to learn how comboboxes work,
and when you do, they'll be easy to work with - and they're a great tool.

second key step: open a new form in Design view. add a combo box control -
no wizard! - to the form. open the Properties box. combobox controls have
many properties that are the same as textbox control properties. they also
have properties that other controls don't have (the exception being listbox
controls, which share many of these special properties).

read up on the following properties in Access Help:
RowSourceType
RowSource
ColumnCount
ColumnHeads
ColumnWidths
BoundColumn (hint: don't confuse the bound column with the ControlSource
property, which binds a control to a field in the form's RecordSource.)
ListRows
ListWidth
LimitToList
AutoExpand
OnNotInList event

another hint: an easy way to go to the Help topic on a property in a form
(or report) is to click on the property "line" in the Properties box, and
press F1.

start there, Fred, experiment some more, and then come back with specific
questions re anything you need help figuring out.

hth

 

[tina]

you're very welcome

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]

Tina,

Thank you again. I read all of those that you recommended (plus
ControlSource) and experimented with them, and it was very informative.
I'm still stuck at the same place, albiet with greater knowledge. Using
JamesFreddyC's inquiry as an example, let's say that the record source for a
form is tblWork, and we desire to show the address of the customer that the
work was for. I think that you said that a combo box is good way to do this.


I found the BoundColumn and ControlSource help files confusing, I think it
was because I couldn't find the "needle" of what these basically are in the
general-case "haystack" of the help text.

But all of those properties seem to just set what show when the user is
actively using the dropdown arrow....I can get that to work fine. My
problem is how to control what shows when nobody is doing anything with the
combo box. The ControlSource property seems to determine this. However,
it has a dropdown list which is just the fields of the RecordSource of the
Form, and I can't get it to take anything related to the related table that
is the source of the combo box.

E.G. so how would one make the address of the customer show in the combo box
when nobody is clicking on the combo box?

Thanks Again.

Sincerely,


Fred

 

[jamesfreddyc]

Fred,

My initial thought to your question is... Why would you need to display a
Customer's address in a comboBox? Further, a particular "work" related
record would logically only occur at ONE location, therefore, there wouldn't
be a need to display many addresses (as in "filling" a comboBox with items).

Perhaps I am misunderstanding your requirement here.

Also: I have since expanded this db model a bit to take into account that a
particular customer address could very well be different from the address of
a work item. For example, in my particular model, while the customer calling
to request service might be the owner of a particular residence (the address
for the work to be done), they might not actually live there (perhaps it is a
rental property).

j

 

[Fred]

Hello jamesfreddyc

I was just using your applicaiton as an example to try to learn something.
With customer being a sort of "lookup" I had recommended showing that via
including it
(via a query) in the record sources of the form. Tina indicated that a
combo box would be a better way of doing that and I was just trying to learn
how to do that.

Sincerely,

Fred

 

[tina]

a control's ControlSource property setting determines which field in the
*form's* RecordSource is bound to that particular control. for example, when
control Text1's ControlSource property is set to FieldA, then when you open
the form, go to any particular record, or to a new record, and enter a value
in control Text1, the value will be stored in FieldA in that record, in the
form's underlying table.

a combo box control's BoundColumn property determines which field in the
control's RowSource will be used as the *value* of the control, when a
droplist selection is made. in turn, that value will be stored in the field
set as the control's ControlSource; or, if the control is unbound
(ControlSource is blank), that value will be held by the control until it is
changed by the user or the form is closed.

when you use a combo box control to choose from a list of employees, for
example, the control's RowSource will be fields from tblEmployees,
including:
1.the primary key field - which must be the bound column of the control,
normally i make it the first column (which is column zero (0), because the
combobox column index is zero-based).
2. the employee name fields (usually you'll want to concatenate these into a
single name field, such as EmpName: Lname & ", " & Fname
3. *any other fields that contain data you will want to display/use in the
form*

use the ColumnWidths property to set the width of the first column (column
0) to 0". you don't want the user to see the primary key value of the
employee record chosen from the combobox droplist.

set the width of the second column (column 1) to, say, 2", wide enough to
display most lastname, firstname combinations. so what shows in the
droplist, and what will show in the combobox control after a selection is
made from the droplist, will be a name.

usually, you set the column widths of any additional columns to 0". depends
on what information the user needs to see when s/he is making a selection
from the droplist.

regardless of the column width setting, the data in all columns of the
RowSource are available to be utilized in the form. the values in those
columns can be selectively displayed in unbound controls in the form, or
used in calculations in the form or in VBA - pretty much however you need to
use them, while the form's open. so if the RowSource of cboEmployee is set
to
SELECT EmpID, Lname & ", " & Fname As Emp, DOB FROM tblEmployees ORDER BY
Lname, Fname;
and the ColumnWidths property is set the
0", 2", 0"
then the user sees only the employee name in the droplist and in the control
after a selection is made.
and you can set the ControlSource of a textbox to
= cboEmployee.Column(2)
so the textbox displays the DOB value of the employee chosen in cboEmployee.

and btw, i have to bow out of this thread now, i won't be back in the
newsgroups for a few weeks. for further specific questions about combo box
controls, suggest you google the newsgroups and/or start a new thread in
microsoft.public.access.forms

hth

 

[Fred]

Hello Tine,

I hope that I got this quick big THANK YOU in before you bowed out of the
thread.

Sincerely,

Fred