moving appoinment db

[c0l0nelFlagg]

I have a moving dispatcher database. There are 99 drivers, 99 loaders,
and 50 different vehicles. The scheduler database is built on a 13
4 week month year so that it can be used repeatedly in any subsequent
year and recalculate the dates so that each year it is referenced to
the first available business day of any given week. This is done by
running an update query that calculates the date based on a formula
related to the month week and day desiganator for each day.

For each vehicle there is a 2 hour appointment block 12 times a day. So
in effect there is a 2 dimensional board 12 blocks wide by 50 slots
vertically. The date is selectable from the header in the main form and
to filter all records for that date. For each vehicle slot, there is a
dropdown to select a driver, a dropdown list to select a loader, as well
as a dropdown to select the specific vehicle details. There is also a
dropdown to select the type of move. These are built into a separate
subform for each slot. Once all the the slots are initially booked, the
entire board has to be saved using a cmd button to update the day's
bookings. Once saved and dispatched, a checkbox in each subform
indicates start time, and calculates an ETA back at the dock, which has
to be saved as well but only the changed data in that subform is to be
saved for data traffic efficiency. Once the vehicle returns, a checkbox
calculates the actual time and fuel usage so that it can be scheduled
for use in different slot based on remaining fuel.

The main underlying junction table contains the calendar, and for
each day there will be 12 appts.x 50 vehicles or 600 bookings or
records. There are seven separate normalized tables on the one side of a
one to many link to the junction table. Driver, Loader, Vehicle, trip
detail, block, & slot. There is also an eighth table that is a one to
many link to the block simply to change the block description to reflect
different time slots for each of the seasons and daylight time.

In effect the junction table record pattern is thus:

Date MO WK DA BLK slot driverID loaderID vehicleID tripTypeID Out IN RMKs

Jan 1 1 1 1 1 1
Jan 1 1 1 1 1 2 ......and so on.

I am able to set up the selections using combo boxes for the driver,
loader and vehicle and trip details using the primary tables as the
record sources. I embed a textbox in each subform, invisible with the
date so that the main form and sub forms use that field to link the
parent a child fields. Where I am running up against it, is doing
the save from either the main form header cmd button or each subform cmd
button. It doesn't appear to enter the the data or ID's into the
transaction table as I would expect it to. Referential integrity is
enforced and enabled.

The main form data source is a query directly off the junction table, so
that it can be sorted by date for the combo box. The query also includes
all the related fields from the 1 side tables for ease of interpretation
rather than just viewing key ID numbers.

When I tell it to save the data from a single vehicle slot, it appears
to do the save and then requery that subform as I would hope, so that
the calculated data in the ETA text boxes would be updated, however when
i check the underlying junction table, nothing is added to the
primary or foreign key fields or to the ETA IN OUT fields to save a
record of the appointments.

Any suggestions on what I might be doing wrong, aside from using
something other than MS Access to accomplish this?

Thanks

[Tom van Stiphout]

On Sun, 27 Jul 2008 04:38:12 +0000, c0l0nelFlagg
<c0**********@y ahoo.cawrote:

<clip>

>
The main form data source is a query directly off the junction table, so
that it can be sorted by date for the combo box. The query also includes
all the related fields from the 1 side tables for ease of interpretation
rather than just viewing key ID numbers.

More than likely that's where you went wrong. Rather keep the query on
a single table, and use 2-column dropdowns to look up the
human-readable text for the ID values.

-Tom.
Microsoft Access MVP
<clip>

[c0l0nelFlagg]

Tried that as suggested and still no joy. Back to the drawing boards.
Almost looks as though I need to run SQL insert code to update data
table. Seems a lot of work for the number of subforms when access is
supposed to be easier to use that php or sql code.

Tom van Stiphout wrote:

On Sun, 27 Jul 2008 04:38:12 +0000, c0l0nelFlagg
<c0**********@y ahoo.cawrote:

<clip>

>>The main form data source is a query directly off the junction table, so
that it can be sorted by date for the combo box. The query also includes
all the related fields from the 1 side tables for ease of interpretation
rather than just viewing key ID numbers.

More than likely that's where you went wrong. Rather keep the query on
a single table, and use 2-column dropdowns to look up the
human-readable text for the ID values.

-Tom.
Microsoft Access MVP
<clip>

[Tom van Stiphout]

On Mon, 28 Jul 2008 00:38:44 +0000, c0l0nelFlagg
<c0**********@y ahoo.cawrote:

Your app should work without the painful workarounds. If you zip up
and send me a copy of your database, I will take a look at it. No
guarantees. My email spam trap should be easy to figure out.

-Tom.

>Tried that as suggested and still no joy. Back to the drawing boards.
Almost looks as though I need to run SQL insert code to update data
table. Seems a lot of work for the number of subforms when access is
supposed to be easier to use that php or sql code.

Tom van Stiphout wrote:

>On Sun, 27 Jul 2008 04:38:12 +0000, c0l0nelFlagg
<c0**********@ yahoo.cawrote:

<clip>

>>>The main form data source is a query directly off the junction table, so
that it can be sorted by date for the combo box. The query also includes
all the related fields from the 1 side tables for ease of interpretation
rather than just viewing key ID numbers.

More than likely that's where you went wrong. Rather keep the query on
a single table, and use 2-column dropdowns to look up the
human-readable text for the ID values.

-Tom.
Microsoft Access MVP
<clip>