Inspections and Treatments

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" <Fred.TakeThisOut@discussions.microsoft.com> wrote in message
> news:F0C2F19A-17DF-4342-9318-2DF2DAF6BE14@microsoft.com...
> > 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
> >
> >
> >
> >
> >
>
>
>