Using dates !

[spazzer]

Hi all,
I am fairly new to Forum so sorry if I've posted this in wrong place !
I need some help please, I am trying to create a database with all the trains that leave and arrive at my station each day, I would like to insert information about booked arrival times and departure times and who the booked driver is and guard etc, the information on arrivals and departures is usually the same from Mon-Fri each week but weekends are different, can anyone tell how I can make sure that each day the correct trains are shown ?, I am thinking that it is all to do with how I put the information into the tables, do I need to create a M-F column then Sat and Sun or will Access not recognise this ?
If I can do this I would also like to be able to input information each day into the database about late running trains and the reason for late running etc and save this info each night then start again the next day, is this possible and if so can anyone advise how to do it ?
Sorry to be so vague as have only just started using Access !

Many Thanks,
Nick Carter

 

[jzwp22]

The most important part of setting up a database is the table structure, so it is best to thoroughly map out your process and then determine the corresponding table structure. Only after your table structure is set up, then you can start worrying about forms (and entering real data), reports etc.

From the information you posted, you have actual arrivals/departures and scheduled arrivals/departures.

You can set up tables to hold the scheduled stuff. Since a train can have many scheduled days for arrival/departures then that describes a one to many relationship. Since the days also correspond to other trains that makes a many-to-many relationship for the train schedule.

tblTrains
-pkTrainID primary key, autonumber
-txtTrainNumber

tblDays (7 records, 1 for each day of the week)
-pkDayID primary key, autonumber
-longDayOfWeek (number field 1=Sunday, 2=Monday etc.)

tblTrainSchedule
-pkTrainScheduleID primary key, autonumber
-fkTrainID foreign key to tblTrains
-fkDayID foreign key to tblDays

Some people would put the arrival and departure times in the tbTrainSchedule as two separate fields, but if you want to be completely normalized a day/train combination has 2 times associated with it, so it describes a 1 to many relationship

tblTrainScheduleTimes
-pkTrainSchTimesID primary key, autonumber
-fkTrainScheduleID foreign key to tblTrainSchedule
-tmTrain (time field)
-fkEventTypeID foreign key to tblEventType

tblEventType (2 records in this table Arrival and Departure)
-pkEventTypeID primary key, autonumber
-txtEventName

You said that the trains run differently on weekends, but are they still running to a schedule? I assume yes, and the structure above should handle it.

Now, as to capturing the actual departure/arrival times, you would need some other tables that somewhat mimic tblTrainSchedule and tblTrainScheduleTimes


tblActualTrains
-pkActualTrainID primary key autonumber
-fkTrainID foreign key to tblTrains
-dteTrain (the date of arrival/departure)

tblActualTrainTimes
-pkActTrainTImesID primary key, autonumber
-fkActualTrainID foreign key to tblActualTrains
-tmeTrain
-fkEventID foreign key to tblEvent

Now I made the assumption that the train arrives and departs on the same day. Is that true in your case or can a train arrive at 11:55PM on Tuesday and depart at 12:15AM on Wednesday for example?

 

[spazzer]

Hi Thanks For Your Reply,
Yes some trains do arrive just before midnight and depart after that as you have said !
I had made up some tables but have probably not done it correctly !
I have different tables for Sat Arrivals,Sat Departures,Sun Arrivals,Sunday Departures,Driver Contacts,Guard Contacts,Depots,Stations etc,I was going to create one to show what the arrival train should form when it departs !, am not sure if im doing this all right, would welcome your ideas and thanks again for your thorough reply !

Thanks Again,
Nick C

 

[jzwp22]

Based on your earlier statement below, I thought you were only concerned with your station?

...database with all the trains that leave and arrive at my station each day

But based on the following statement in your most recent post, it implies that you are dealing with more than your station. Could you explain further?

I have different tables for Sat Arrivals,Sat Departures,Sun Arrivals,Sunday Departures,Driver Contacts,Guard Contacts,Depots,Stations etc

Regarding the tables you mention above, you have similar data spread across multiple tables Saturday arrivals, Sunday arrivals etc. Similar data should be in the same table.


To address the trains that arrive one day but depart the next, the structure I proposed earlier will have to be reworked. I'm going to call anytime a train runs as a scheduled event. Each event will have an arrival day & time and a departure day and time. So the reworked structure for the schedule will look like this. Basically all I have done is to move the reference to the day to the table that holds the departure/arrival time.

tblTrainSchedule
-pkTrainScheduleID primary key, autonumber
-fkTrainID foreign key to tblTrains
-txtEvent


tblTrainScheduleTimes
-pkTrainSchTimesID primary key, autonumber
-fkTrainScheduleID foreign key to tblTrainSchedule
-fkDayID foreign key to tblDays
-tmTrain (time field)
-fkEventTypeID foreign key to tblEventType

tblEventType (2 records in this table Arrival and Departure)
-pkEventTypeID primary key, autonumber
-txtEventName

Similarly, you would have to move the date field in the table holding the actual arrival/departure info. I also think we can simplify to 1 table to hold the actuals and link back to the schedule event.

tblActualTrainTimes
-pkActTrainTImesID primary key, autonumber
-fkTrainScheduleID foreign key to tblTrainSchedule
-dteTrain (the date of arrival/departure)
-tmeTrain
-fkEventID foreign key to tblEvent

As to Driver Contacts and Guard Contacts, these are people, so have a table to hold all people. You can add a field to this table to identify the person's role as either a driver or a guard

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
-fkRoleID foreign key to tblRoles


tblRoles (right now you will have two records, Driver and Guard)
-pkRoleID primary key, autonumber
-txtRoleName

Can a person who is a driver one day, be a guard the next or are their roles set?

Now the question is how do we tie these people to the trains or to the schedule. Let me try to ask my question with an example. Let's say train 1 is scheduled to arrive M,T,W,Th and F at 10am at your station. Bob and Joe are the scheduled driver and guard respectively for train 1 on each of those days all of the time. Is that what you want to record in the database? Can you have Bob and Joe only on M,W,F and then Sam and Shaun on T and Th? I guess I need to understand more about what you are trying to model.

 

[spazzer]

Thanks Again for all your help,
Could you explain foreign key and what that means please ?
Can I explain further what I,m trying to do !
I have approximately 25 trains per hour arrive and 25 depart my station on weekdays,these trains are scheduled and in a timetable there are however a couple of additional trains depart on a friday and they only run on that day !
Drivers only Drive and Guards only do Guard the trains there role doesn't change, however the trains they work can vary from day to day depending on which turn of duty they have been rostered on !, (I hope this is making some sort of sense so far)
Weekends and Bank Holidays are the same as above but these can vary each week due to engineering work taking place so scheduled trains may only travel so far before terminating short of there destination, (this is why I have a table with various stations in it to use for destination points for trains and to use in the contacts table to show where drivers and guards are based).
I normally know about a week in advance about what trains will run the weekend and the following week although there is a base timetable this is sometimes amended for different departure or arrival times etc on a weekly basis.
It would be good if I could also enter data about problems or late arrivals and departures and save it on daily basis and then start afresh the next day with the scheduled timetable again.
Also if when I get the timetable for the following week have a form where I could enter altered workings if any and use for those days then revert if neccesary back to the normal timetable the week after that !!!
Sorry if this sounds confusing but am trying to explain more about what I,m trying to do, I did start using an excel spreadsheet to record info on and can post this if you would like to see what sort of info I'm trying to record.

Thanks Again,
Nick C

 

[The_Doc_Man]

I'll take a shot at foreign keys. They are incredibly important to the topic of normalization because they are part of the mechanism of linking related tables to each other in some organized way.

In table X, let's say you have primary key XID that is the unique record identifier.

Let's say that table Y needs to refer to a record in table X because one of the tables contains data dependent on or required by the other. The simplest way to refer to the related record is to have a field in Y that contains one of the values of XID that appears in table X. Though it is not required, we might also call it XID in table Y.

In table X, field XID is the primary key. In table Y, field XID would be a foreign key. That is, it IS a key - but not for the table in which it resides.

If you created the corresponding relationship between tables X and Y, it would be said to be "x-to-x on field XID" and x-to-x would be either "one to one" or "one to many" or "many to one" (depending on what the two tables represented.)

Typically, we recommend something like an autonumber for a primary key unless there is a very strong reason to pick a meaningful key. Autonumber keys are merely guaranteed to be unique but have no other particular meaning. If you used XID as an autonumber in X, it would be a LONG in Y. If XID is not autonumber, then at least the XID field types in X and Y should match. And strictly speaking, the field names don't have to match, it is just more convenient and easier to understand if they do. In any case, once you pick the primary key, you have specified the format of any foreign keys that refer back to the primary key.

You can look up more on the subject using the forum SEARCH feature on the topic of "Meaningful" or "Meaningless" keys. Be warned that purists almost always want to have meaningless keys. I'm a pragmatist, so I'll allow my keys to have some meaning in some limited cases.

 

[jzwp22]

The primary key of one table links to the foreign key of a related table. The table with the primary key is the one side of a one-to-many relationship while the table with the foreign key is the many side of the relationship. The key fields show you how to link the two tables in a relationship.

Do you know the driver/guards in advance or only when the train arrives? If only when the train arrives then it should be recorded in the actuals area.

What you are trying to model sounds to be 3 fold

1. The normal schedule (this will remain constant once set up)
2. The real schedule (built from the normal with modifications included)
3. The actuals

I'm thinking that you can use an append query based on the normal schedule to create the records for the real schedule & then modify those records with any necessary changes. The actual details can then be linked back to the real schedule.

If you could provide that spreadsheet, it might help clarify some things.

 

[spazzer]

Hi,
ok will post the spreadsheet as an attachment to show data etc, this may help to explain info I've compiled.

Many Thanks,
Nick C

 

[jzwp22]

Pardon my ignorance, but I don't follow your spreadsheet. I assume the head code is your train number. I took a look at a couple and saw the arrival times but I didn't see corresponding departure times.

With respect to the driver and guard, those get recorded upon arrival, so they should be tied to the actuals table I proposed earlier

tblActualTrainTimes
-pkActTrainTImesID primary key, autonumber
-fkTrainScheduleID foreign key to tblTrainSchedule
-dteTrain (the date of arrival/departure)
-tmeTrain
-fkEventID foreign key to tblEvent

tblActualTrainPeople
-pkActTrainPeopleID primary key, autonumber
-fkActTrainTimesID foreign key to tblActualTrainTimes
-fkPeopleID foreign key to tblPeople

With respect to the origin and destination, they should be tied to normal schedule

tblTrainScheduleTimes
-pkTrainSchTimesID primary key, autonumber
-fkTrainScheduleID foreign key to tblTrainSchedule
-fkDayID foreign key to tblDays
-tmTrain (time field)
-fkDepotID foreign key to tblDepot
-fkEventTypeID foreign key to tblEventType

when the event type=arrival, the fkDepotID refers to the depot from which the train came; when the event type=departure, fkDepotID refers to the destination depot

tblDepot
-pkDepotID primary key, autonumber
-txtDepotName

 

[spazzer]

Hi Again,
I have filled in a couple of entries to maybe explain further on spreadsheet attached, the drivers and guards are allocated normally the night before so we know who is working which train !
The left side is for arrivals and the right is departures, the headcode is the trains unique ID number, there are columns for scheduled arrival times and then a column that allows us to input the actual time the train arrived i.e 3 mins late or 6 minutes early etc, this also happens on the right side of the spreadsheet if you scroll to the right !
The set swaps mean when a train was booked to work a particular service but was either late or had maintenance issues so had to work a differnt train than what it was scheduled to do !,I have filled this in on the spreadsheet !
How can this information be saved for all users to access and alter if need be ??
I realise this is quite difficult to explain but am happy try and show what I'm trying to do !
Thanks for all your help !

Nick C

 

[jzwp22]

I don't think Access will have a problem storing the data and having it available for editing, but the logic behind Access is completely different than Excel, so that is something that you should keep in mind.

I thought of an alternate way of handling the data. You might consider the routes themselves. You said that you have many incoming and outgoing trains in a day. All of those trains are following routes. For each route, there is a starting point (a departure from a location), a stop at your station (an arrival at a location), a departure from your station and an arrival at the destination. This describes a one to many relationship once again. Therefore each route has at least 4 events on the route of which there are only two types arrival and departure

tblRoutes
-pkRouteID primary key, autonumber
-txtRouteName

tblRouteEvents
-pkRouteEventID primary key, autonumber
-fkRouteID foriegn key to tblRoutes
-fkLocationID foreign key to tblLocations
-fkEventNameID foreign key to tblEvents
-eventdatetime
-fkTrainID foreign key to tblTrains (each route event has a train associated with it and the actual train can vary during the course of the route as you explained in your most recent post)

tblLocations
-pkLocationID primary key, autonumber
-txtLocationName

tblEventNames (arrive, depart)
-pkEventID primary key, autonumber
-txtEventName

You technically do not need the tblEventNames, but it would save time to just select arrive or depart from a combo box (based on this table) so you don't have to type the words arrive or depart over and over again.

I have given a general framework for your table structure using a couple different approaches. Do you understand the structures and how they can relate to your application? I do not know which one will work best for you; that is really something for you to decide since you know the details of your application.

 

[spazzer]

Thanks for all your help,
I am a little unclear on how everything relates ,could you explain it a little further ?, I understand the reasoning behind the Tables but how does everything relate to each other ? Also I still a little unclear as to how I can save the inputted data each day and start again the next !,Sorry to sound so vague !!!!!

Thanks Again,
Nick C

 

[jzwp22]

To explain how tables can be related to one another lets consider an example of a common process: checking books out of a library.

First of all, you have the books, so a table to hold the books

tblBooks
-pkBookID primary key, autonumber
-txtTitle
-txtAuthorFirstName
-txtAuthorLastName

Here are a couple example records
pkBookID|txtTitle|fkAuthorID
1|The Adventures of Sherlock Holmes|Arthur|Doyle
2|Harry Potter and the Goblet of Fire|J|Rowling

I use naming conventions for fields in my tables that help me identify the fields more efficiently. I always have an autonumber primary key field. I always use the prefix pk and the suffix ID so that I know it is a key field and it is primary. I use the key fields to establish relationships. The primary key field basically gives the record a unique identifier.

A txt prefix designates that the field is a text field. I usually use dte as the prefix for a date field.

In tblBooks you see the two author fields. Since authors can write several books (one author-many books), it would be a time savings not to have to type the same author's name for all of the books they have written. There is also the problem of spelling errors. It would be time consuming to go through each record and find if an author's name is spelled correctly or not. To handle this more efficiently, I would rather type the author's name only once and relate it to the books they have written. So to do that I would create a table with just the author names

tblAuthors
-pkAuthorID primary key, autonumber
-txtAuthorFirstName
-txtAuthorLastName

Let's show some example author records in the tblAuthors
pkAuthorID|txtAuthorFirstName|txtAuthorLastName
1|J|Rowling
2|Arthur|Doyle
3|William|Shakespeare

You can also include other details about the author in this table if you wanted to i.e. date of birth, date of death etc.

Now going back to our tblBooks, I want to replace the two author fields with a reference (or a relation) to the tblAuthors I just created. To do this, I use a foreign key field that relates back to the primary key field of the author table. A foreign key field must be the same data type as the primary key field it relates back to. An autonumber field is equivalent to a long number field. So the tblBooks, now looks like this

tblBooks
-pkBookID primary key, autonumber
-txtTitle
-fkAuthorID foreign key to tblAuthors


Here are a couple example records
pkBookID|txtTitle|fkAuthorID
1|The Adventures of Sherlock Holmes|2
2|Harry Potter and the Goblet of Fire|1

As you can see, the first record in the tblBooks (pkBookID=1) is entitled The Adventures of Sherlock Holmes and it was written by the author whose whose foreign key=2 which corresponds to the primary key=2 in the author table or Arthur Doyle. Having this foreign key relating back to another record in another table establishes the relationship between the records in the two tables.

Now continuing our example, the library want to track any books its patron checks out, so we need a patron table

tblPatron
-pkPatronID primary key, autonumber
-txtPatronFirstName
-txtPatronLastName
-longPatronCardNo (long prefix designates a long number data type for the field)

Example records
pkPatronID|txtPatronFirstName|txtPatronLastName|longPatronCardNo
1|Joe|Public|39549943
2|Jane|Doe|94934040


Now a patron will check out any number of books (one patron-many books)

tblPatronBooks
-pkPatronBookID primary key, autonumber
-fkPatronID (foreign key relating back to tblPatron)
-fkBookID (foreing key relating back to tblBooks)
-dteCheckOut

Let's say Jane Doe checks out the Harry Potter book today, the record would look like this:

pkPatronBookID|fkPatronID|fkBookID|dteCheckOut
1|2|2|06/09/2009

Here is a link to at tutorial that goes through the basics of Access hopefully it will explain things better than I.

Also I still a little unclear as to how I can save the inputted data each day and start again the next !,Sorry to sound so vague !!!!!

You would use forms to enter your data. When you enter a record it is stored in the database until you delete it. In simple terms, you would enter a record for the train leaving its desitination, another record for when it arrives at your station, another record when it departs your station and a record for when it arrives at its final destination (if you want to track a train through its entire route). These records would be related to the route in the structure I presented yesterday.

 

[spazzer]

Hi,
Thanks for all your help and detailed responses ,I will make astart and see how it goes,many thanks again.

Nick C

 

[jzwp22]

You're welcome and good luck with the project as you move forward. It will definitely be a learning experience. I have been using Access for 12 years and still continue to learn new things!