Converting access query to Mssql

[Charlie2]

Hi, I have built a query which works fine in access. The thing is I now want to use the same query in MSSQL 2000 and the synatx is wrong.
I have tried altering the syntax myself but i am unable to get it to work.

Can anyone show me how?

Access Syntax:

SELECT DISTINCT Holiday_Bookings.ClientID,
Holiday_Bookings.Booking_Cost,
Room_Facilities.FacilityCost,
Rooms.CostPerNight,
Rooms!CostPerNight*Nights_Stayed+Holiday_Bookings!Booking_Cost+Room_Facilities!FacilityCost AS TotalCost,
[TotalCost]*17.5/100+[TotalCost] AS [Total+VAT]
FROM Room_Facilities INNER JOIN (Hotels INNER JOIN (Holiday_Bookings RIGHT JOIN Rooms ON Holiday_Bookings.ClientID = Rooms.ClientID) ON Hotels.HotelID = Rooms.HotelID) ON Room_Facilities.FacilityID = Rooms.FacilityID;

My Effort:

SELECT DISTINCT Holiday_Bookings.ClientID,
Holiday_Bookings.Booking_Cost,
Room_Facilities.FacilityCost,
Rooms.CostPerNight,
Rooms.CostPerNight*Nights_Stayed+Holiday_Bookings.Booking_Cost+Room_Facilities.FacilityCost AS TotalCost,
TotalCost *17.5/100 + TotalCost AS TotalVAT
FROM Room_Facilities INNER JOIN (Hotels INNER JOIN (Holiday_Bookings RIGHT JOIN Rooms ON Holiday_Bookings.ClientID = Rooms.ClientID) ON Hotels.HotelID = Rooms.HotelID) ON Room_Facilities.FacilityID = Rooms.FacilityID;

I get the error Invalid column name 'TotalCost'. when I try to run this code

Am I anywhere near close to getting this to work?

 

[SQL_Hell]

Hi

I think it is a problem the alias, try this



SELECT DISTINCT Holiday_Bookings.ClientID,
Holiday_Bookings.Booking_Cost,
Room_Facilities.FacilityCost,
Rooms.CostPerNight,
Rooms.CostPerNight*Nights_Stayed+Holiday_Bookings.Booking_Cost+Room_Facilities.FacilityCost AS TotalCost,
(Rooms.CostPerNight*((Nights_Stayed+Holiday_Bookings.Booking_Cost+Room_Facilities.FacilityCost)) *17.5/100) + (Rooms.CostPerNight*(Nights_Stayed+Holiday_Bookings.Booking_Cost+Room_Facilities.FacilityCost)) AS TotalVAT
FROM Room_Facilities INNER JOIN (Hotels INNER JOIN (Holiday_Bookings RIGHT JOIN Rooms ON Holiday_Bookings.ClientID = Rooms.ClientID) ON Hotels.HotelID = Rooms.HotelID) ON Room_Facilities.FacilityID = Rooms.FacilityID;


If you dont like this method then use more than 1 query

 

[Charlie2]

Gracias

Thanks man

 

[SQL_Hell]

no probs,

there is probably a much better way of doing this, I just cant think of it at the moment