Re: table relationships

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Walter (anonymous_at_discussions.microsoft.com)
Date: 01/12/05 

Date: Wed, 12 Jan 2005 09:54:22 -0800

>-----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

>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" <anonymous@discussions.microsoft.com> wrote in
message
>news:06c101c4f4dd$bf0da9f0$a501280a@phx.gbl...
>> 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 *** 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
>
>.
>

Quantcast