table relationships

[Walter]

I am working on a DB to store mileage and fuel records for
a trucking company. The data would come from trip sheets
turned in by drivers with OD entries for crossing state
lines, fuel, and stops for load, unload, etc. The Trip
Details table would store date, truck #, driver. An
Odometer table would store the OD readings. An OD
Description table would store OD descriptions (state line,
fuel, stop, Etc.)that explain what that OD is for. A
States table stores the state for each OD. A fuel table
stores gal of fuel purchased. A Stops table stores
purpose for stops (load, unload, etc.) For each OD
reading, you can only have 1 description, 1 state, 1
purpose, etc. However, there may be many OD readings for
each trip entry (main form). I have the form set up
similar to this:

Date: TripID #: (autonumber)

Truck #: Driver:

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
OD Desc State Fuel Purpose


The top info (date, truck, driver) would be main form.
The other info would be a subform in data sheet view
linked by the TripID number. My question is how to define
table relationships for this to work. Would this be a
1:many relationship between Trip Details & the OD tables,
and 1:1 between the OD and related tables? Or, 1:many
between Trip Details and all other tables (Desc, State,
Fuel, etc.)?

If anyone can help with this, I'll greatly appreciate it.

Thanks, Walter

 

[Jeff Boyce]

Walter

What's an OD? I had thought that it was an Odometer, but you mentioned OD
descriptions (state line, ...) and now I'm not sure.

I think you'll find a consensus in this newsgroup (tablesdbdesign) that it
makes your work with a relational database (Access) easier if you start with
the tables, rather than starting with a form/screen and trying to make
tables that fit it.

Help me/us better understand your specific situation. I'll take an
poorly-understood stab at it, for you to set me straight!

Trips happen, and have a truck and driver associated with them.
On any given trip, there are "1-to-many" "waypoints" (sorry if I don't

have the correct terminology). Each of the waypoints has an odometer
reading, a date/time, and a type/description. (I am considering the start
and end of the trip as waypoints, each with a date/time and odometer
reading.) A special kind of waypoint is a "fueling", that has gallons of
fuel and cost associated with it. There may be other waypoints with cost
(e.g., bridge toll, ferry passage, ...).

So far, if I have this correct, I can imagine 6 tables:

tblDriver
DriverID
FirstName
LastName
DOB
EmergencyContact
DriversLicenseNumber
DriversLicenseState
...

tblTruck
TruckID
LicensePlateNumber
LicensePlateState
DateOfAcquisition
TruckTypeID
...

tlkpTruckType
TruckTypeID
Description (e.g., 20' stepvan, ...)

trelTrip
TripID
TruckID
DriverID
TripDate
TripDescription (e.g., "delivery of relief supplies to airport")

trelWaypoint
WaypointID
TripID
WaypointTypeID
OdometerReading
Cost
Amount
Notes

tlkpWaypointType
WaypointTypeID
WaypointTypeDescription (e.g., Fueling stop, Start-of-trip, End-of-trip,
State line, ...)

Regardless of whether this table structure matches your situation, first get
your tables nailed down. Then focus on how you want to show it, on screen
(forms), and on paper (reports). You may find, after first designing your
table structure, then considering forms and reports, that you need to go
back and revisit the table structure. But start there...

--
Good luck

Jeff Boyce
<Access MVP>

 

[Walter]

-----Original Message-----

Jeff,
Thanks for your reply. You have the right idea about the
purpose and tables. I did establish my tables first. My
tables are:

tblEmployees:
EmployeeID (autonumber)
Last Name
FirstName
CDLDriver (yes/no)
etc.

tblTrucks:
TruckID (unique # PK)
Make
Year
etc.

tblTripDetails:
TripDetailsID (autonumber)
TripDate
Location (place where trip ends - next trip begins)
TruckID
OdometerID


tblOdometer:
OdometerID (autonumber)
Odometer
O/DDescriptionID


tblOdometerDescription:
O/DDescriptionID (autoNumber)
O/DDescription (state line, fuel, trip end, etc)
OdometerID

tblFuel:
FuelID (autonumber)
Gallons
Cost
OdometerID


tblStates:
State (state abbreviation - PK)
OdometerID


My question is how to relate these tables. I understand
that each truck will have more than 1 trip details. Each
trip details will have more than 1 Odometer reading
(OD). Each trip details can also have more than 1 OD
description, fuel, and state. However, each OD can only
have 1 OD description, 1 fuel, 1 state. Should I relate
OD description, fuel, and state to the trip details table
as a 1:many relationship or should they be related to the
OD table as a 1:1 relationship?

At the end of each calendar quarter, I will have to
generate a report based on the total number of miles
traveled in each state and the total gallons of fuel
purchased in each state. One thing I was confused about
on the tables you set up was why the difference in table
prefix, i.e. tbl, tlkp, trel. What is tlkp & trel?

I appreciate you taking the time to look at this.
Walter

 

[Jeff Boyce]

Walter

I'm confused by the table structure you've described, and I'm not sure what
your intentions are. see comments in-line below:

Jeff,
Thanks for your reply. You have the right idea about the
purpose and tables. I did establish my tables first. My
tables are:

tblEmployees:
EmployeeID (autonumber)
Last Name
FirstName
CDLDriver (yes/no)
etc.

tblTrucks:
TruckID (unique # PK)
Make
Year
etc.

tblTripDetails:
TripDetailsID (autonumber)
TripDate
Location (place where trip ends - next trip begins)
TruckID
OdometerID

This table seems to only hold the endpoint of an entire trip (all segments).
Or are you using "trip" synonymously with "trip segment"?

tblOdometer:
OdometerID (autonumber)
Odometer
O/DDescriptionID

I don't understand why this is a separate table.

tblOdometerDescription:
O/DDescriptionID (autoNumber)
O/DDescription (state line, fuel, trip end, etc)
OdometerID

I don't understand why this is a separate table.

tblFuel:
FuelID (autonumber)
Gallons
Cost
OdometerID

I don't understand why this is a separate table.

tblStates:
State (state abbreviation - PK)
OdometerID

I don't understand how States and Odometers are related, and this table
looks like you can only have a state associated with one OdometerID.

My question is how to relate these tables.

I'm going to suggest that you revisit your table structure -- rather than
try to force the structure you have to work.

I understand
that each truck will have more than 1 trip details.

You left out "each Truck can have more than one Trip". You need a Truck and
a Trip table, related 1:M.

AND, each Trip can have more than one TripDetail (two tables, 1:M).

Each
trip details will have more than 1 Odometer reading
(OD).

This implies that each TripDetail record (one-to-many, for each Trip) can
have an Odometer reading. I don't understand why a TripDetail record would
need many Odometer readings, if the TripDetail record only recorded facts
about one segment.

Each trip details can also have more than 1 OD
description, fuel, and state. However, each OD can only
have 1 OD description, 1 fuel, 1 state. Should I relate
OD description, fuel, and state to the trip details table
as a 1:many relationship or should they be related to the
OD table as a 1:1 relationship?

Given my previous comment, I don't see this at all.

At the end of each calendar quarter, I will have to
generate a report based on the total number of miles
traveled in each state and the total gallons of fuel
purchased in each state. One thing I was confused about
on the tables you set up was why the difference in table
prefix, i.e. tbl, tlkp, trel. What is tlkp & trel?

I use my own, ideosyncratic, but fairly common "prefixing" convention to
help identify the type of table (and query, and form, and ...). For my use,
"tbl" is Table, a base table; "tlkp" is a "lookup table", useful in a
support role, but not of primary significance (e.g., a table of State
abbreviations); "trel" is a "relation table" (also known as a junction or
resolver table), used to handle the junction between two many-to-many
tables.

 

[Walter]

-----Original Message-----
Walter

I'm confused by the table structure you've described, and I'm not sure what
your intentions are. see comments in-line below:



This table seems to only hold the endpoint of an entire trip (all segments).
Or are you using "trip" synonymously with "trip segment"?

The table name may be confusing. This table is to store

the basic information for each trip: date, truck number,
driver. Some of the FK fields in the tables I'm sure are
unnecessary and may need to be deleted. I included them
while I was trying to decide how the relationships should
be defined. The location field may be better in another
table. It's purpose is to verify accounting for all
miles. On the main form when a truck is selected(from
combo box list of active trucks)the last trip's ending
location and odometer reading will be displayed but can't
be changed. There will be a subform in which to enter the
odometer readings for each trip. Maybe some of the
following tables should be combined for this purpose. I
am trying to figure out how to design them in order to
accomplish what I need to do. Each odometer reading can
only have 1 Description(Stop,Fuel,State Line). If the
Description = Fuel there can only be one fuel entry for
that odometer reading. If the Description = State Line
there can only be 1 State entry for that odometer reading,
etc. However, 1 trip can have multiple fuel entries and,
or multiple state entries but each is associated with it's
own odometer reading. Another lookup table of Stop
descriptions(I didn't include this in my last post)would
include the stop description of "Trip End". This
selection would enable an entry in the Location field
which would end data entry for this trip and would be the
source for the next trip's beginning location for this
truck.>

I don't understand why this is a separate table.

for the trip and include the Location and Fuel Cost &
Gallons fields.

I don't understand why this is a separate table.
This is a lookup table since there 4 or so possible valid entries.


I don't understand why this is a separate table.


I don't understand how States and Odometers are related, and this table
looks like you can only have a state associated with one OdometerID.

The states table is also a lookup table. I am trying to
design this so the maximum amount of data entry will be
from selection of choices instead of typing for ease of
use and accuracy. When an odometer is entered as a state
line, this establishes the beginning mileage for that
state. When another state line is entered, this
establishes the ending mileage for the present state and
the beginning mileage for the new state. Therefore,
subtracting the beginning from the ending gives you the
mileage for this state which is one of the main purposes
for this project.

I hope I've addressed all of your questions and appreciate
your help.
Many thanks again,
Walter

I'm going to suggest that you revisit your table structure -- rather than
try to force the structure you have to work.


You left out "each Truck can have more than one Trip". You need a Truck and
a Trip table, related 1:M.

AND, each Trip can have more than one TripDetail (two tables, 1:M).


This implies that each TripDetail record (one-to-many, for each Trip) can
have an Odometer reading. I don't understand why a TripDetail record would
need many Odometer readings, if the TripDetail record only recorded facts
about one segment.


Given my previous comment, I don't see this at all.


I use my own, ideosyncratic, but fairly

common "prefixing" convention to