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.
| Author |
Topic |
|
mr.modus
Starting Member
27 Posts |
 Posted - 2007-11-05 : 15:15:55
|
| Hello there,I've been pulling my hair out with this and any help would be fantastic.I have a query where I'm trying to get info out of three tables and I'm not sure how to Join and how to Group it.My tables (with only the relevant fields) are:tblPassengers- PassengerID (Number)- BookingID (text)- FlightNumber (text)- FlightCompleted (Yes/No)tblBookings- BookingDate (Date/Time)- BookingID (text)tblFlights- FlightNumber (text)- FlightDate (Date/Time)- CityName (text)Relations are:tblPassengers.BookingID = tblBookings.BookingIDtblPassengers.FlightNumber = tblFlights.FlightNumberWhat I want to do is get a count of all passengers (Count(tblPassengers.PassengerID)that have a completed flight (tblPassengers.FlightCompleted = yes) and that have flown within 30 months of their purchase (tblFlights.FlightDate < DateAdd("m",30,tblBookings.BookingDate)) within the last five years (tblFlights.FlightDate > 1/1/2003) grouped by CityName and Year.So the result I would want would be something like:Count Year City453 2003 London285 2003 Hockley Valley138 2003 Another City658 2004 London260 2004 Hockley ValleyAny help would be awesome! |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-06 : 19:39:16
|
| http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92126answered there. |
 |
|
|
|
|
|
Please help with this query. Not so good at access