A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How Do I Normalize This?



 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old August 19th, 2007, 06:06 AM posted to microsoft.public.access.tablesdbdesign
[email protected]
external usenet poster
 
Posts: 7
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:43 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.