Form, subform, sub-sub form?

[Claude]

Let' say we have an application for a production facility running 24/7
broken into 3 shifts 6-2,2-10,10-6

each production report date contains 3 shifts as above

in each shift there can be from one to several different product runs (the
average is about 4) but it could be more or less - it's not defined or
definite for many reasons

Then, for each run there is a production report of product, good pieces,
bad pieces total run time for that product etc. etc.

then for each run there is a list for # of rejects and reject code

SO I have ProductionRuns with rejects subform 1 to many but this results in
repeated entries of shift, date, time. So i could make a main table for
these and have the production run as a subform to this, but then I have a
subform to the prodution run form.

This gets confusing and requires me to put buttons for starting new date or
shift. Any better way of doing this?

[Larry Linson]

Without knowing the structure of your data, it would seem to me that you
would have a form with production run as the record... with a selection of
Date and Shift in the Form Header. Within that production run, same-level
but separate subforms for regular production information and reject
information.

Using Combo or List Boxes in the header to select date and shift, then
replacing the RecordSource with an SQL statement using date and shift as
criteria would limit the production runs to that particular date/shift. Or,
if you'd rather, use a calendar control to select the date.

If there's something about your data so this doesn't seem logical, follow up
with clarification. I may not see it, but surely someone will have a
suggestion.

Larry Linson
Microsoft Access MVP

"Claude" <Cl***********@quickpic.net> wrote in message
news:KySic.32795$aQ6.1714744@attbi_s51...

Let' say we have an application for a production facility running 24/7
broken into 3 shifts 6-2,2-10,10-6

each production report date contains 3 shifts as above

in each shift there can be from one to several different product runs (the
average is about 4) but it could be more or less - it's not defined or
definite for many reasons

Then, for each run there is a production report of product, good pieces,
bad pieces total run time for that product etc. etc.

then for each run there is a list for # of rejects and reject code

SO I have ProductionRuns with rejects subform 1 to many but this results in repeated entries of shift, date, time. So i could make a main table for
these and have the production run as a subform to this, but then I have a
subform to the prodution run form.

This gets confusing and requires me to put buttons for starting new date or shift. Any better way of doing this?

[Don Leverton]

Hi Claude,

I'd try one main form, and have 4 (or more) *cascading* subforms on it.
These subforms *could* be on seperate pages of a tab control if nessecary.

Main form ("frmProductionDate") has the production date info.(ShiftDate,
Plant, etc)
First subform: "sbfShifts" -- (ShiftDate, ShiftNumber, StartTime, EndTime,
etc)
Second subform: "sbfProductionRuns" (ShiftNumber, RunNumber, StartRun,
EndRun, etc.)
Third subform: "sbfProducts" (RunNumber, ProductNumber, ProductDescription)
Fourth subform: "sbfProductResults" (ProductNumber,
UnitsProduced,UnitsRejected, RejectCode, etc)

Each form or subform above should be bound to it's own table, with
one-to-many links defined.
Use an unbound textbox to retreive the unique ID for each subform, then use
that textbox as the LinkMaster for the subform (hierachly) below it.

i.e. "txtShiftNumber" textbox control with
"=Me![sbfShifts].Form![ShiftNumber]" as it's control source, which is then
used as the Link Master, and "ShiftNumber" as the LinkChild for the
"sbfProductionRuns" subform. Repeat this procedure as nessecary for the rest
of the subforms.

If this is all set up correctly:
1.) Choosing a date on the main form will display a list of shifts for that
day in "sbfShifts", with the record pointer on the first of 3 records
2.) sbfProductionRuns will display ALL the runs that occured during the
selected shift. Selecting a different shift in sbfShifts will cause
sbfProductionRuns to show Production runs for that shift....
3.) ...and so on, and so on for each of the subforms beneath, in a cascading
fashion.
4.) These suforms can be used for viewing AND for entering new data, which
make this approach quite flexible.
5.) Data can be summarized at each subform level... summed, counted,
percentages calculated, etc. This can be done by inserting calculated
controls in each of the subforms form footers, then referencing those form
footer controls from other controls on the main form.

Other considerations:
Is it possible that there may be several RejectCodes for the same product,
during the same production run?
If so you may need another subform, and even a way to identify
(sequentiallly) each item that came off the production line.

Clear as mud, right? :)

--
HTH,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================
"Claude" <Cl***********@quickpic.net> wrote in message
news:KySic.32795$aQ6.1714744@attbi_s51...

Let' say we have an application for a production facility running 24/7
broken into 3 shifts 6-2,2-10,10-6

each production report date contains 3 shifts as above

in each shift there can be from one to several different product runs (the
average is about 4) but it could be more or less - it's not defined or
definite for many reasons

Then, for each run there is a production report of product, good pieces,
bad pieces total run time for that product etc. etc.

then for each run there is a list for # of rejects and reject code

SO I have ProductionRuns with rejects subform 1 to many but this results in repeated entries of shift, date, time. So i could make a main table for
these and have the production run as a subform to this, but then I have a
subform to the prodution run form.

This gets confusing and requires me to put buttons for starting new date or shift. Any better way of doing this?