Days of the week

[spazzer]

Hi,
Does anyone know how I can record something as being used Mon-Fri without having to record the item 5 separate times in a table, i.e. if I created a table 1=Mon,2=Tues,3=Wed etc could I record the item in a table i.e. DaysUsedTable and then put 1,2,3,4,5 etc in the field column once or is this not possible, can anyone help ??

Cheers, Nick C

 

[Galaxiom]

What you suggest can be done.

However, days of the week are already encoded in vb as constants. They are integers pretty much as you describe and can be referred to as such. A date can be converted to Day of the Week with a function.

http://www.techonthenet.com/access/functions/date/weekday.php

Perhaps you can use this.

 

[spazzer]

Hi,
Thanks for reply, I was wondering if I could say put a DaysRun Field and then insert 1,2,3,4,5 to show it runs 5 days then if it only ran Sat and Sun enter 6,7 in field or would I have to create separate tables for each day run ?

Cheers, Nick C

 

[RuralGuy]

You may want to revisit your table design. Putting more than one piece of information in a field is a violation of normalization rules. If you will tell us what business rule you are trying to implement, someone here might have some other ideas for you.

 

[jibbajabba]

This is about trains running isn't? (Saw your other post)

My idea would be to create a table called ServiceProfile (Or something) and give it an id, a text field called ServiceProfileName and 7 bit (true or false) fields for days of the week .

Then you create some records in it...

ServiceProfileName = "Mon to Fri" and set the relevant days of the week to true
ServiceProfileName = "Everyday" and set all days of the week to true
ServiceProfileName = "Saturday's Only" and set Saturday... well you get the idea!

Then for each train service you have you can link it to a ServiceProfile - thus allowing you to know which services run on which day.

 

[spazzer]

Hi Thanks for reply,
Yes thats it, could you explain a little further how to do this ,I am new to all this, I have alot of trains that run almost the same everyday ie 1F10 will run Mon-Fri and depart everyday at 1000 but not run Sat or Sun, I just dont see the need to fill out a table for everyday of the week with the same train departing on five days exactly the same except day is different.
Would it be difficult to put a boundary around it i.e. runs Mon- Fri between 20th June and 25th Dec ? not sure how that could be done but thanks for help !!
Cheers, Nick C

 

[jibbajabba]

Hi Thanks for reply,
Yes thats it, could you explain a little further how to do this ,I am new to all this, I have alot of trains that run almost the same everyday ie 1F10 will run Mon-Fri and depart everyday at 1000 but not run Sat or Sun, I just dont see the need to fill out a table for everyday of the week with the same train departing on five days exactly the same except day is different.
Would it be difficult to put a boundary around it i.e. runs Mon- Fri between 20th June and 25th Dec ? not sure how that could be done but thanks for help !!
Cheers, Nick C

Hi Nick,

There's quite a few ways to do that, what you really should be looking at is producing a structure that can periodically build you a dataset that is in effect your entire timetable with the ability to enter actual times in it for comparison against the scheduled times - To do that you need to think about what info you want in that dataset, i'm guessing - Service Name, Day, Scheduled Depart time, Scheduled Arrival time - All that data will all ready be inserted and there will be spaces for the actual time?

So you'd need a service table including:

ServiceName, Start time, Finish time, Service profile (linked to table discussed before), Dates (linked to a table with start and end date in) - Possibly you may want to link to a table of stations if you want to capture the stations, and you might want a bit field called IsSuspended (So you can ignore a service it if's suspended for a week)

Using all that you could (via SQL) make your dataset - it could be one big flat table (even an excel file) that came with your entire timetable for a (specified period) pre-filled. Then some poor sod could sit there all day with the exciting job of inputting the actual arrival and departure times - you may want a button that automatically makes all services arrive ten minutes late

Just a tip, but it sounds to me like you're attempting to build a system that needs to have a really robust design, and if you don't get it right from the start could cause you a lot of hassle down the line (no pun intended!). And being pretty new to all this have you considered getting someone in to consult with?