Viewing set information in MSAccess2000

[jac_pol]

Hello all, I'm a complete novice as a database developer and I'm seeking some guidance. I work for a Rail Freight Company in Australia and I'm setting up a database to assist in yard operations. Here's the scenario. We have scheduled services which arrive on a given day at a given time each week. What I need to do is set up a form which will display all the daily scheduled trains and their arrival/departure times (usually 8 trains per day) at which point the users will enter additional data such as actual arrival, if it was early/late and why etc. I have a table with scheduled arrival/departure times, a table each for arrivals and departures. I set up queries to display each days schedule but when I combine the query with the arrivals table in a form the sheduled trains disappear. I don't know where to go. My boss wants to implement this DB pretty soon and he's got me under the pump a bit. I'd be greatful for any help. ray:

 

[SydneyGeek]

Hi, sounds like you might need to change the data structure around a bit. Access likes things ordered a particular way. If you are interested in Movements (in or out), that's what you need for your data table. So... this could be an example o fthe fields you need.
Movement ID (key field, Autonumber)
Movement Date (Date/Time)
Train ID (number, use the Lookup wizard to look up the Train ID field in Trains table)
Due In (Date/Time)
Due Out (Date/Time)
Actual In (Date/Time)
Actual Out (Date/Time)

The Trains table will have:
Train ID (key field, Autonumber)
Train Name (Text)
and other fields for Destination, Freight Type, and whatever else you need

The calculations for on-time or late running, tonnages, and other stuff are handled by queries -- they should not be stored, because they will change, and Access tables should store only the raw data.

The main part is the data entry form. If you build a query that does all the calculations on early/lete running, and includes the data from both the Movements and Trains tables, you will be able to put everything on a single screen for data input. And once the data entrry query is working, reporting becomes pretty straightforward too.

Regards
Denis

 

[jac_pol]

Thanks mate, I'll have a go at it.

 

[mdmilner]

Gee, I can't understand why you would even need a field for actual arrival - we both know that trains always arrive "on time".

Call that a little Railroad humor - I work for what's termed a 'Class 1 Railroad' in North America. Wish I could say I've done something similar previously but that kind of information has never been part of my job function and I'm not really (technically) doing any IT based tasks.

Based on my own experience though, with multiple yards all over our network...unless you're doing something for a single yard only, train schedules exist as a series of estimated Arrivals and Departures at multiple mileposts over the course of a single train trip.

My thought is that your train schedule table should have a 2nd related table that has the detail of the schedule.

Aka, the lookup table that has the train names is itself related to a detail table that has fields like.

keyfield (autonumber)
train_name (same train name)
schedule_seq (order of events, which is 1st/ 2nd/ 3rd)
schedule_event (type of event like ARR/DEP/etc etc)
schedule_dtStamp (date/time stamp of event)

Again, this would only be if you're tracking for multiple yards.

Mike