Hi,
I have the following tables set up in MS Acess97(i will be porting to Mysql when i can get it working)
A bus timetable system
-----------------------
arrivetime(ROUTEID,STOPID,ATIME,CODE)
this table says when a certain bus(ROUTEID) will be at a certain stop(STOPID) at a certain time(ATIME).IF CODE=NULL then there is no condition on that stop. IF CODE='1' then this entry will not run on sundays.
eg.
ROUTEID STOPID ATIME CODE
24 2 6:30 1
24 2 6:41
24 3 6:35 1
24 3 6:47
52 1 6:45
52 1 6:47
17 1 6:30
17 2 6:43
17 3 6:51
code(CODEID,CODEDESC)
this says what the code(CODEID) does(CODEDESC).
eg.
CODEID CODEDESC
1 NOT SUNDAY
stop(STOPID,STOPDESC)
this says what the stops(STOPID) are called (STOPDESC).
STOPID STOPDESC
1 x lane
2 y lane
3 z lane
4 a lane
torder(ROUTEID,STOPID,ORDER)
this tells you which stops a bus visits and the order in which it visits them.
eg.
ROUTEID STOPID ORDER
24 2 1
24 5 2
24 7 3
52 1 1
52 3 2
52 7 3
I have the following query
SELECT ROUTEID, ATIME
FROM arrivetime, stop
WHERE arrivetime.STOPID=stop.STOPID
AND STOPDESC='x lane'
AND arrivetime.ATIME>= #6:30#;
This gives the answer to "Give me the name and times of buses that run from xlane on or after ztime."
Problem with this is that you know this bus will come to the stop but you DON'T know where it goes to whic isn't much use.
It would be great if I could do the following
"Give me the name and times of buses that go from x lane to ylane on or after z time."
Any beautiful ideas?
Thanks for your time and help....
Much appreciated,
Laura.
I have the following tables set up in MS Acess97(i will be porting to Mysql when i can get it working)
A bus timetable system
-----------------------
arrivetime(ROUTEID,STOPID,ATIME,CODE)
this table says when a certain bus(ROUTEID) will be at a certain stop(STOPID) at a certain time(ATIME).IF CODE=NULL then there is no condition on that stop. IF CODE='1' then this entry will not run on sundays.
eg.
ROUTEID STOPID ATIME CODE
24 2 6:30 1
24 2 6:41
24 3 6:35 1
24 3 6:47
52 1 6:45
52 1 6:47
17 1 6:30
17 2 6:43
17 3 6:51
code(CODEID,CODEDESC)
this says what the code(CODEID) does(CODEDESC).
eg.
CODEID CODEDESC
1 NOT SUNDAY
stop(STOPID,STOPDESC)
this says what the stops(STOPID) are called (STOPDESC).
STOPID STOPDESC
1 x lane
2 y lane
3 z lane
4 a lane
torder(ROUTEID,STOPID,ORDER)
this tells you which stops a bus visits and the order in which it visits them.
eg.
ROUTEID STOPID ORDER
24 2 1
24 5 2
24 7 3
52 1 1
52 3 2
52 7 3
I have the following query
SELECT ROUTEID, ATIME
FROM arrivetime, stop
WHERE arrivetime.STOPID=stop.STOPID
AND STOPDESC='x lane'
AND arrivetime.ATIME>= #6:30#;
This gives the answer to "Give me the name and times of buses that run from xlane on or after ztime."
Problem with this is that you know this bus will come to the stop but you DON'T know where it goes to whic isn't much use.
It would be great if I could do the following
"Give me the name and times of buses that go from x lane to ylane on or after z time."
Any beautiful ideas?
Thanks for your time and help....
Much appreciated,
Laura.