Linking one field to multiple fields

[spazzer]

Hi,
I would like to create a table with separate train ID numbers and then create a separate table with days of the week (i.e. Mon,Tues,Wed etc),
I would then like to be able to link a train ID with which days of the week it runs on (i.e. Train ID 1H12 runs at 10:00 Mon,Tues,Wed,Thurs,Fri) !
Can I link one field from one table to multiple fields in another depending on which day it runs.
For example if I asked the database whether 1H12 runs on a Saturday it would not show it but if I asked if it ran on a Fri it would ?

Can anyone help and advise ?

Many Thanks Nick C

 

[Galaxiom]

You should structure your database differently. Do not use the Days of the week as fields.

You second table should be something like Services. It should contain records for each service with fields for Day,Time and TrainID.

Your query will join the tables on TrainID. It can be used to find services on TrainID, Time or Day as required.

 

[spazzer]

Thanks For Reply,
Are you saying that basically I should have a table with fields for train id,time and date for each day of the week,with all trains listed each day ?
i.e. Table for Monday departures 1F10 10:00 departure
then a Table with Tuesday departures with 1F10 10:00
I would have 7 days of train departures somes repeated everyday Mon-Friday !
Or should I list all departures and have a field for Mon then one for Tues and Wed etc Is this the best way !

Many Thanks,
Nick C

 

[Mr. B]

spazzer,

You need a table for all departures. Include enough fields to track each activity, including the TrainID. Then you will just link this table to the Train table.

HTH

 

[spazzer]

Hi Again,
Thanks for replies all, I just need to clarify then that I should create a table for all departures and list each one individually for each day of the week it runs ??

Thanks for help,
Nick C

 

[Atomic Shrimp]

That sounds like what they're saying. The day names themselves could be stored in a table to create a lookup/dropdown field in your form, populating the departures table with just the reference to the name of the day.

But yes, storing the first departure on Monday in a separate row from the second departure on Monday, in a separate row from the first departure on Tuesday is the right way to do it, because it allows you to flexibly calculate, for example, the differences between departure times on different days, or the gap between departure times on the same day.

There is considerable repeating of data though, so I wouldn't store departure times for trains on Monday 15th June distinctly from departure times for trains on Monday 22nd June - you probably just want to create a table called 'timetable', listing all possible trains for a Monday, then store 'effective from' and 'expiry' dates for those timetable records - or something like that.