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
|
|||
|
|||
How Do I Normalize This?
Hi everyone,
I'm learning Access by creating a schedule database for a public transit agency. I have a big ol' spreadsheet of trip data. To see the sample, go he http://pepandmax.googlepages.com/Sample.xls So, what do all of the fields mean? --TripID is a unique identifier for each trip. --Work Run is an assignment for a driver (the driver doesn't stay on the same route all day). --WROrder is used to sort the work run chronologically and other stuff (not related to database). --Day is the days that the trip occurs (weekdays, weekends, Friday only, etc.) --Block # is the bus that makes the trip. Sometime there is more than one block for a work run, for example, if the driver comes back to the garage for lunch and then leaves on a different bus. --Route # is self explanatory! --Trip # is related to the route. 1 is the first trip made on that route, etc. This has to be manually entered, because sometimes a trip with a lower number happens after a trip with a higher number (don't ask, it's complicated). --Stop01Time, Stop02Time, etc. are the scheduled stop times. There is another table that shows the stop name for Stop01, Stop02, etc. for each route. What is the best way to normalize this table into multiple tables? I hate the Stop01, Stop02, etc. set up, because it's so limited... like someone wise once said, "Columns are expensive; records are cheap." Currently I have normalized the tables like this: tblStopTimes: StopTimeID (PK) TripID StopSeq (the order of the stops, i.e. 01, 02, 03, etc) StopID (related to a table with stop names mentioned above) StopTime tblTrips: TripID (PK) Route# WorkRun# Block# DaysID I'm having difficulty making forms and reports with the table normalized like this, but I'm not sure what my other options are. Thanks in advance for reading and for any help you might be able to offer. Nicole |
Thread Tools | |
Display Modes | |
|
|