If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
table relationships
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
-----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 a 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" wrote in message ... 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 . |
#4
|
|||
|
|||
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: "Walter" wrote in message ... -----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 a 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. I appreciate you taking the time to look at this. Walter -- Good luck Jeff Boyce Access MVP |
#5
|
|||
|
|||
-----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: "Walter" wrote in message ... -----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 a 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"? 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. tblOdometer: OdometerID (autonumber) Odometer O/DDescriptionID I don't understand why this is a separate table. I'm now thinking that this would be the "details" table for the trip and include the Location and Fuel Cost & Gallons fields. tblOdometerDescription: O/DDescriptionID (autoNumber) O/DDescription (state line, fuel, trip end, etc) OdometerID I don't understand why this is a separate table. This is a lookup table since there 4 or so possible valid entries. 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. 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 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. I appreciate you taking the time to look at this. Walter -- Good luck Jeff Boyce Access MVP . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Autonumber | Ally H. | General Discussion | 7 | August 27th, 2004 04:51 PM |
Semicolon delimited text query help | Al Guerra | Running & Setting Up Queries | 3 | August 12th, 2004 11:50 AM |
Complicated Databse w/many relationships | Søren | Database Design | 7 | July 13th, 2004 05:41 AM |
Help with table design and relationships | Richard Wright | Database Design | 3 | June 20th, 2004 03:49 PM |