Conversion of crazy access query to mssql-help!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Conversion of crazy access query to mssql-help!

Author

Topic

mr.modus
Starting Member

27 Posts

Posted - 2007-08-29 : 13:51:07

I am rebuilding an app using mssql 2k. They have an existing access db that needs to be changed over. There is the main query and it looks like it uses another saved query inside it.

Before I post the access sql I will explain exactly what must be done. I have three tables: tbl_Flights, tbl_Balloons, tblPassengers.

A customer will have a ticket for a flight. The flight is either for a weekday or weekend day or night for a specific city. I need to get all flights that are on or after today (getDate()), that are the proper type (weekday AM/weekday PM/weekend AMorPM), that are in the proper city and that have room on board. tbl_Balloons stores the passenger capacity of each balloon type. tblPassengers stores the bookings for all flights.

So as an example:
A person calls in with 2 tickets for a Weekday AM flight in London.
I need to find all flights on or after today that are on weekday mornings and have 2 spaces available.

Here's the msaccess sql:

SELECT DISTINCTROW tblFlights.CityName, tblFlights.FlightDate, tblFlights.FlightTime, tblFlights.FlightNumber, tblBalloons.Registration, tblBalloons.CapacityPass, IIf(IsNull([Booked Passengers]),0,[Booked Passengers]) AS [Passengers Booked], [CapacityPass]-IIf(IsNull([Booked Passengers]),0,[Booked Passengers]) AS [Spaces Available], tblFlights.FlightStatus
FROM tblBalloons INNER JOIN (tblFlights LEFT JOIN LookupPassengerTotalByFlight ON tblFlights.FlightNumber = LookupPassengerTotalByFlight.FlightNumber) ON tblBalloons.BalloonID = tblFlights.BalloonID
WHERE (((tblFlights.FlightDate)>=Now()) AND ((tblFlights.FlightStatus)="Scheduled"))
ORDER BY tblFlights.CityName, tblFlights.FlightDate, tblFlights.FlightTime;

And the saved query (LookupPassengerTotalByFlight) that it seems to access inside itself to get the "Booked Passengers" values is this:

SELECT DISTINCTROW tblPassengers.FlightNumber, Count(tblPassengers.PassengerID) AS [Booked Passengers]
FROM tblPassengers
GROUP BY tblPassengers.FlightNumber;

If anyone has any help for me I'd greatly appreciate it. I'm bruising my forehead and damaging my keyboard.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 14:05:55

[code]SELECT DISTINCT tblFlights.CityName,
tblFlights.FlightDate,
tblFlights.FlightTime,
tblFlights.FlightNumber,
tblBalloons.Registration,
tblBalloons.CapacityPass,
ISNULL([Booked Passengers], 0) AS [Passengers Booked],
tblBalloons.CapacityPass - ISNULL([Booked Passengers], 0) AS [Spaces Available],
tblFlights.FlightStatus
FROM tblBalloons
INNER JOIN tblFlights ON tblFlights.BalloonID = tblBalloons.BalloonID
LEFT JOIN LookupPassengerTotalByFlight ON LookupPassengerTotalByFlight.FlightNumber = tblFlights.FlightNumber
WHERE tblFlights.FlightDate >= CURRENT_TIMESTAMP
AND tblFlights.FlightStatus = 'Scheduled'
ORDER BY tblFlights.CityName,
tblFlights.FlightDate,
tblFlights.FlightTime[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 14:06:41

[code]SELECT FlightNumber,
COUNT(*) AS [Booked Passengers]
FROM tblPassengers
GROUP BY FlightNumber[/code]

E 12°55'05.25"
N 56°04'39.16"

mr.modus
Starting Member

27 Posts

Posted - 2007-08-29 : 14:34:03

Thanks for the rapid response Peso. I'm a little confused as I'm not a realy SQL expert, but I know in access you can save queries and reference them in other queries (such as the LookupPassengerTotalByFlight query) however I don't know how you do this in MSSQL 2000. How do I save the smaller query as LookupPassengerTotalByFlight so that I can reference it in the larger query?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-29 : 14:38:09

You might find this helpful in your project:

http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/Quick-Access-JET-SQL-to-T-SQL-Cheatsheet.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

mr.modus
Starting Member

27 Posts

Posted - 2007-08-29 : 14:41:34

Oh I see, I use a view. Thanks so much for your help Peso.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources