Additions in SQL

[051119]

Hi all,

 

I've got 2 tables as below:

 

stops

Stop_name

RouteNumb

Order

Min_from_start

 

routes

RouteNumb

Start_time

 

In essence, Start_time contains the time the bus sets out on its journey, and Min_from_start is how many minutes from the first stop the bus is (eg. so if Start_time is 09:00 and Min_from_start is 14, then I'm looking for 09:14).

 

Can anyone please help me find an SQL query to calculate this?

 

So far I've got

SELECT stops.Stop_name, stops.Min_from_start, routes.Start_time
FROM routes INNER JOIN stops ON Routes.RouteNumb = stops.route
WHERE (((stops.route)="12"));

Which shows the info but not the times.

 

Thank you so much!

[Mchl]

Assuming you store Start_time as TIME

 

SELECT stops.Stop_name, stops.Min_from_start, routes.Start_time, routes.Start_time + INTERVAL stops.Min_from_start MINUTE AS stopTime
FROM routes INNER JOIN stops ON Routes.RouteNumb = stops.route
WHERE (((stops.route)="12"));

 

 

[051119]

Thank you ever so much for your reply!

 

Access throws up this error though

  Quote

Syntax error (missing operator) in query expression 'routes.Start_time + INTERVAL stops.Min_from_start MINUTE'[/Quote]

 

I've tried playing around with it a bit but can't work out what it's after. Would be grateful for any help!

 

Regards

[Mchl]

If you want Access help, don't ask in MySQL forum. Sorry.

 

Moving to MSSQL.

[051119]

Sorry, didn't realise there was a difference! If anyone can help I would be very grateful, I've been racking my brains for hours over this!

[051119]

Nevermind, I've got it thanks! I used DateAdd in Access!