Mike said:
I have a Bus Timetable Database in which i have 526 records relating to each
timepoint, day & time. I looks like this
Timpeoint Timepoint Name Day Service No Time On Off
1 Melbourne Sunday 27 9:00
2 Bendigo Sunday 27 10:30
3 Mildura Sunday 27 15:30
etc
The fields "On" & "Off" represent passengers getting obviously on and off
the bus. I am having real trouble setting up a form that will show timepoints
1 2 3 etc on the same page. Its basically to assist with data entry where a
person can quickly enter the on's & offs for the timepoints for a particular
service.
You Timepoint is one of those calculations everyone agrees should be
calculated as needed rather than stored, right? Guessing your structure
looks something like this:
CREATE TABLE Timepoints (
timepoint_name VARCHAR(20) NOT NULL PRIMARY KEY
)
;
CREATE TABLE Timetables (
timepoint_name VARCHAR(20) NOT NULL
REFERENCES Timepoints (timepoint_name),
day_name VARCHAR(9) NOT NULL,
CHECK (day_name IN ('Sunday',
'Monday', 'Tuesday', 'Wednesday',
'Thursday', 'Friday', 'Saturday')),
service_nbr CHAR(2) NOT NULL,
departure_time CHAR(5) NOT NULL,
CHECK (departure_time LIKE '[0-2][0-9]:[0-5][0-9]'),
PRIMARY KEY (departure_time,
day_name, service_nbr, timepoint_name)
)
;
CREATE TABLE Boardings (
timepoint_name VARCHAR(20) NOT NULL,
day_name VARCHAR(9) NOT NULL,
service_nbr CHAR(2) NOT NULL,
departure_time CHAR(5) NOT NULL,
FOREIGN KEY (departure_time,
day_name, service_nbr, timepoint_name)
REFERENCES Timetables (departure_time,
day_name, service_nbr, timepoint_name),
effective_date DATETIME DEFAULT DATE() NOT NULL,
CHECK (day_name = FORMAT(effective_date, 'dddd')),
PRIMARY KEY (departure_time,
day_name, service_nbr, timepoint_name, effective_date),
on_amount INTEGER NOT NULL,
CHECK (on_amount >= 0),
off_amount INTEGER NOT NULL,
CHECK (off_amount >= 0)
)
;
INSERT INTO Timepoints (timepoint_name) VALUES ('Melbourne')
;
INSERT INTO Timepoints (timepoint_name) VALUES ('Bendigo')
;
INSERT INTO Timepoints (timepoint_name) VALUES ('Mildura')
;
INSERT INTO Timetables (timepoint_name, day_name, service_nbr,
departure_time) VALUES ('Melbourne', 'Sunday', '27', '10:30')
;
INSERT INTO Timetables (timepoint_name, day_name, service_nbr,
departure_time) VALUES ('Melbourne', 'Sunday', '27', '09:00')
;
INSERT INTO Timetables (timepoint_name, day_name, service_nbr,
departure_time) VALUES ('Melbourne', 'Sunday', '27', '15:30')
;
INSERT INTO Boardings (timepoint_name, day_name, service_nbr,
departure_time, effective_date, on_amount, off_amount) VALUES
('Melbourne', 'Sunday', '27', '09:00', DATE() + 2, 5, 0)
;
SELECT (
SELECT COUNT(*)
FROM Timetables AS T2
WHERE T1.timepoint_name = T2.timepoint_name
AND T1.day_name = T2.day_name
AND T1.service_nbr = T2.service_nbr
AND T1.departure_time >= T2.departure_time
) AS timepoint,
T1.timepoint_name, T1.day_name, T1.service_nbr, T1.departure_time,
DT1.effective_date, DT1.on_amount, DT1.off_amount
FROM Timetables AS T1
LEFT JOIN (
SELECT B1.timepoint_name, B1.day_name, B1.service_nbr,
B1.departure_time, B1.effective_date, B1.on_amount, B1.off_amount
FROM Boardings AS B1
WHERE B1.effective_date = DATE() + 2
) AS DT1
ON T1.timepoint_name = DT1.timepoint_name
AND T1.day_name = DT1.day_name
AND T1.service_nbr = DT1.service_nbr
AND T1.departure_time = DT1.departure_time
;
(Cheating by adding 2 to make Sunday <g>).
The above query is cannot be updateable. Is this the problem?
Jamie.
--
