Inspections and Treatments

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


"jamesfreddyc" wrote in message
...
"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

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


"Fred" wrote in message
...
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

you're very welcome


"jamesfreddyc" wrote in message
...
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

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

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" wrote:

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

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

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" wrote in message
...
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

Hello Tine,

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

Sincerely,

Fred