Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2006
    Posts
    4

    Unanswered: Getting total from row

    I have built a query in Access that calculates the total to be charged to clients based on 3 cost columns.

    I have tried to run the query in MSSQL but it will not run, so could someone help me out with the syntax in my query.

    I have done about half of it myself but I am stuck on the calculation and I havent a clue of the syntax to be used.

    This is my Access Query;
    Code:
    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;
    and this is what I have managed to salvage in MSSQL format:
    Code:
    SELECT 
      Holiday_Bookings.ClientID,
      Holiday_Bookings.Booking_Cost,
      Rooms.CostPerNight,
      Room_Facilities.FacilityCost
    FROM
      Rooms
      INNER JOIN Holiday_Bookings ON (Rooms.Clients_ID = Holiday_Bookings.ClientID)
      INNER JOIN Room_Facilities ON (Rooms.FacilityID = Room_Facilities.FacilityID)
    Any help would be great as I am completely lost.

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by Steve_Ignorant
    I have built a query in Access that calculates the total to be charged to clients based on 3 cost columns.

    I have tried to run the query in MSSQL but it will not run, so could someone help me out with the syntax in my query.

    I have done about half of it myself but I am stuck on the calculation and I havent a clue of the syntax to be used.

    This is my Access Query;
    Code:
    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;
    and this is what I have managed to salvage in MSSQL format:
    Code:
    SELECT 
      Holiday_Bookings.ClientID,
      Holiday_Bookings.Booking_Cost,
      Rooms.CostPerNight,
      Room_Facilities.FacilityCost
    FROM
      Rooms
      INNER JOIN Holiday_Bookings ON (Rooms.Clients_ID = Holiday_Bookings.ClientID)
      INNER JOIN Room_Facilities ON (Rooms.FacilityID = Room_Facilities.FacilityID)
    Any help would be great as I am completely lost.
    if you provide the table structure,it would be easier to check it.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    check this-
    But I am not sure whether it would work or not,bcoz of the table structure...I haven't checked it..
    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
    If it doesn't work,plz provide table structure..
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your Access query is referencing values that exist within controls on your form (Rooms!CostPerNight). SQL Server hasn't a clue about these controls. Are you going to pass these values as parameters to a stored procedure?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Mar 2006
    Posts
    4

    No

    I am going to create a view, all I am looking for is the relevant equivelant code for MSSQL.

    The half I have done in the MSSQL format works fine and the query done in access works in access but will not work in MSSQL, I am aware that there are differences in the syntax used.

    All I need is the calculation Booking_Cost + Basic_CostPerNight*Nights_Stayed +Facility_Cost and tax @17.5%

    If I can get the correct syntax for this calculation I am home free.


    JoyDeep thanks but you are using Access syntax in your example,
    my access version works perfectly I dont need help with access.

  6. #6
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by Steve_Ignorant
    I am going to create a view, all I am looking for is the relevant equivelant code for MSSQL.

    The half I have done in the MSSQL format works fine and the query done in access works in access but will not work in MSSQL, I am aware that there are differences in the syntax used.

    All I need is the calculation Booking_Cost + Basic_CostPerNight*Nights_Stayed +Facility_Cost and tax @17.5%

    If I can get the correct syntax for this calculation I am home free.


    JoyDeep thanks but you are using Access syntax in your example,
    my access version works perfectly I dont need help with access.
    Please state the problem you are facing while executing the query in mssql.
    It would be better for you if you use stored procedure and pass parameter to it as Blindman suggested.The above query that I gave you is parseing correctly in mssql.So if you are having problem with it,provide the DDLs.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  7. #7
    Join Date
    Mar 2006
    Posts
    4
    Quote Originally Posted by rudra
    Please state the problem you are facing while executing the query in mssql.
    It would be better for you if you use stored procedure and pass parameter to it as Blindman suggested.The above query that I gave you is parseing correctly in mssql.So if you are having problem with it,provide the DDLs.

    Relevant tables attached.
    Attached Files Attached Files

  8. #8
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by Steve_Ignorant
    Relevant tables attached.
    There is one table missing in your script i.e dbo.hotels,plz check and send the complete dlls.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is a more standard SQL syntax for Joydeep's example:
    Code:
    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 Rooms ON Room_Facilities.FacilityID = Rooms.FacilityID
    	INNER JOIN Holiday_Bookings on Rooms.ClientID = Holiday_Bookings.ClientID
    	INNER JOIN Hotels on Rooms.HotelID = Hotels.HotelID
    But there seems to be nothing wrong with the formulas, which is the area you had concern with.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Mar 2006
    Posts
    4

    Wait it's fixed now.

    Code:
    SELECT Holiday_Bookings.ClientID,Rooms.CostPerNight,    Room_Facilities.FacilityCost,Holiday_Bookings.Booking_Cost,
    (Holiday_Bookings.Booking_Cost+Rooms.CostPerNight*Nights_Stayed)+(Room_Facilities.FacilityCost) AS Total
    
    FROM  Rooms 
    INNER JOIN Holiday_Bookings  ON Rooms.Clients_ID = Holiday_Bookings.ClientID    
    INNER JOIN Room_Facilities  ON Rooms.FacilityID = Room_Facilities.FacilityID
    Last edited by Steve_Ignorant; 03-16-06 at 21:35.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •