Sum Problem

[boatguy]

Hello,

I am trying to sum up several columns to create a subtotal. When I execute my query I get an error that says: " is not a valid name. Make sure that it does not include invalid chareters or punctuation and that it is not too long.

Here's my query:

SELECT DISTINCTROW Rentals.Rental_ID, Charterers.Charterer_FName, Charterers.Charterer_Lname, Charterers.Charterer_MI, Charterers.Charterer_DOB, Charterers.Charterer_Age, Charterers.Charterer_Address, Charterers.Chartrerer_City, Charterers.Charterer_State, Charterers.Charterer_Country, Charterers.Charterer_Zip, Charterers.Charterer_Main_Tel, Charterers.Charterer_Local_Tel, Charterers.Charterer_DL_Number, Charterers.Charterer_DL_State, Charterers.Where_Hear_About_US, Rentalboats.Boat_number, Rentalboats.FL_number, Rentalboats.Persons_allowed, Rentals.AcceptWaiver, Rentals.Operator1, operator1.Charterer_FName AS Operator1_FName, operator1.Charterer_Lname AS Operator1_LName, operator1.Charterer_MI AS Operator1_MI, operator1.Charterer_DOB AS Operator1_DOB, operator1.Charterer_Age AS Operator1_AGE, operator1.Charterer_Address AS Operator1_Address, operator1.Chartrerer_City AS Operator1_City, operator1.Charterer_State AS Operator1_State, operator1.Charterer_Country AS Operator1_Country, operator1.Charterer_Zip AS Operator1_Zip, operator1.Charterer_Main_Tel AS Operator1_MainTelNumb, operator1.Charterer_Local_Tel AS Operator1_Local_TelNumb, operator1.Charterer_DL_Number AS Operator1_DL_Number, operator1.Charterer_DL_State AS Operator1_DLSTATE, operator1.Charterer_Email AS Operator1_Email, Rentals.DateIN, Rentals.DateOUT, Rentals.TimeIn, Rentals.TimeOut, Rentals.WaiverTodayOnly, Rentals.BoatRentalFee, Rentals.WaiverFee, Rentals.TowingFee, Sum(Rentals.[BoatRentalFee + Rentals.WaiverFee + Rentals.TowingFee]) AS SubTotal
FROM operator1 INNER JOIN (Rentalboats INNER JOIN (Charterers INNER JOIN Rentals ON Charterers.CustomerID = Rentals.ChartererID) ON Rentalboats.Boat_number = Rentals.BoatID) ON operator1.Operator1 = Rentals.Operator1
WHERE (((Rentals.Rental_ID)=([Enter Rental ID])));

Any help would be greatly appreciated.

 

[boatguy]

By the way, when I pull the some code out it runs fine.

Sum Code:

Sum(Rentals.[BoatRentalFee + Rentals.WaiverFee + Rentals.TowingFee]) AS SubTotal

 

[PHV]

Replace this:
Sum(Rentals.[BoatRentalFee + Rentals.WaiverFee + Rentals.TowingFee]) AS SubTotal
By this:
Sum(Rentals.BoatRentalFee + Rentals.WaiverFee + Rentals.TowingFee) AS SubTotal
Or (in case of null values) this:
Sum(Rentals.BoatRentalFee) + Sum(Rentals.WaiverFee) + Sum(Rentals.TowingFee) AS SubTotal

Anyway, remove the DISCTROW predicate and add a GROUP BY clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[boatguy]

Ok, now I get an error that states "You tried to execute a query that does not include the specified expression 'Rental_ID' and part of an aggregate function.

Any thoughts?

BTW - Thanks for your help so far.

 

[PHV]

and add a GROUP BY clause

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[boatguy]

After the Where Statement? Group By Rentals.Rental_ID?
When I do that I get the following error "You tried to execute a query that does not include the specified expression 'Charterer_FName' and part of an aggregate function.

 

[PHV]

Group By Rentals.Rental_ID?
GROUP BY all the fields in the SELECT clause that aren't part of an aggregate function (for instance not summed).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[boatguy]

I am sorry to be such an idiot about this, but I am still getting the error -" is not a valid name. Make sure that it does not include invalid chareters or punctuation and that it is not too long.-

SELECT Rentals.Rental_ID, Charterers.Charterer_FName, Charterers.Charterer_Lname, Charterers.Charterer_MI, Charterers.Charterer_DOB, Charterers.Charterer_Age, Charterers.Charterer_Address, Charterers.Chartrerer_City, Charterers.Charterer_State, Charterers.Charterer_Country, Charterers.Charterer_Zip, Charterers.Charterer_Main_Tel, Charterers.Charterer_Local_Tel, Charterers.Charterer_DL_Number, Charterers.Charterer_DL_State, Charterers.Where_Hear_About_US, Rentalboats.Boat_number, Rentalboats.FL_number, Rentalboats.Persons_allowed, Rentals.AcceptWaiver, Rentals.Operator1, operator1.Charterer_FName AS Operator1_FName, operator1.Charterer_Lname AS Operator1_LName, operator1.Charterer_MI AS Operator1_MI, operator1.Charterer_DOB AS Operator1_DOB, operator1.Charterer_Age AS Operator1_AGE, operator1.Charterer_Address AS Operator1_Address, operator1.Chartrerer_City AS Operator1_City, operator1.Charterer_State AS Operator1_State, operator1.Charterer_Country AS Operator1_Country, operator1.Charterer_Zip AS Operator1_Zip, operator1.Charterer_Main_Tel AS Operator1_MainTelNumb, operator1.Charterer_Local_Tel AS Operator1_Local_TelNumb, operator1.Charterer_DL_Number AS Operator1_DL_Number, operator1.Charterer_DL_State AS Operator1_DLSTATE, Rentals.DateIN, Rentals.DateOUT, Rentals.TimeIn, Rentals.TimeOut, Rentals.WaiverTodayOnly, Rentals.BoatRentalFee, Rentals.WaiverFee, Rentals.TowingFee, Sum(Sum(Rentals.[BoatRentalFee + Rentals.WaiverFee + Rentals.TowingFee])) AS SubTotal


FROM operator1 INNER JOIN (Rentalboats INNER JOIN (Charterers INNER JOIN Rentals ON Charterers.CustomerID = Rentals.ChartererID) ON Rentalboats.Boat_number = Rentals.BoatID) ON operator1.Operator1 = Rentals.Operator1

GROUP BY Rentals.Rental_ID, Charterers.Charterer_FName, Charterers.Charterer_Lname, Charterers.Charterer_MI, Charterers.Charterer_DOB, Charterers.Charterer_Age, Charterers.Charterer_Address, Charterers.Chartrerer_City, Charterers.Charterer_State, Charterers.Charterer_Country, Charterers.Charterer_Zip, Charterers.Charterer_Main_Tel, Charterers.Charterer_Local_Tel, Charterers.Charterer_DL_Number, Charterers.Charterer_DL_State, Charterers.Where_Hear_About_US, Rentalboats.Boat_number, Rentalboats.FL_number, Rentalboats.Persons_allowed, Rentals.AcceptWaiver, Rentals.Operator1, operator1.Charterer_FName, operator1.Charterer_Lname, operator1.Charterer_MI, operator1.Charterer_DOB, operator1.Charterer_Age, operator1.Charterer_Address, operator1.Chartrerer_City, operator1.Charterer_State, operator1.Charterer_Country, operator1.Charterer_Zip, operator1.Charterer_Main_Tel, operator1.Charterer_Local_Tel, operator1.Charterer_DL_Number, operator1.Charterer_DL_State, Rentals.DateIN, Rentals.DateOUT, Rentals.TimeIn, Rentals.TimeOut, Rentals.WaiverTodayOnly, Rentals.BoatRentalFee, Rentals.WaiverFee, Rentals.TowingFee
HAVING (((Rentals.Rental_ID)=([Enter Rental ID])));

 

[boatguy]

Sorry, ther query actually looks like this:

SELECT Rentals.Rental_ID, Charterers.Charterer_FName, Charterers.Charterer_Lname, Charterers.Charterer_MI, Charterers.Charterer_DOB, Charterers.Charterer_Age, Charterers.Charterer_Address, Charterers.Chartrerer_City, Charterers.Charterer_State, Charterers.Charterer_Country, Charterers.Charterer_Zip, Charterers.Charterer_Main_Tel, Charterers.Charterer_Local_Tel, Charterers.Charterer_DL_Number, Charterers.Charterer_DL_State, Charterers.Where_Hear_About_US, Rentalboats.Boat_number, Rentalboats.FL_number, Rentalboats.Persons_allowed, Rentals.AcceptWaiver, Rentals.Operator1, operator1.Charterer_FName AS Operator1_FName, operator1.Charterer_Lname AS Operator1_LName, operator1.Charterer_MI AS Operator1_MI, operator1.Charterer_DOB AS Operator1_DOB, operator1.Charterer_Age AS Operator1_AGE, operator1.Charterer_Address AS Operator1_Address, operator1.Chartrerer_City AS Operator1_City, operator1.Charterer_State AS Operator1_State, operator1.Charterer_Country AS Operator1_Country, operator1.Charterer_Zip AS Operator1_Zip, operator1.Charterer_Main_Tel AS Operator1_MainTelNumb, operator1.Charterer_Local_Tel AS Operator1_Local_TelNumb, operator1.Charterer_DL_Number AS Operator1_DL_Number, operator1.Charterer_DL_State AS Operator1_DLSTATE, Rentals.DateIN, Rentals.DateOUT, Rentals.TimeIn, Rentals.TimeOut, Rentals.WaiverTodayOnly, Rentals.BoatRentalFee, Rentals.WaiverFee, Rentals.TowingFee, Sum(Rentals.[BoatRentalFee + Rentals.WaiverFee + Rentals.TowingFee]) AS SubTotal


FROM operator1 INNER JOIN (Rentalboats INNER JOIN (Charterers INNER JOIN Rentals ON Charterers.CustomerID = Rentals.ChartererID) ON Rentalboats.Boat_number = Rentals.BoatID) ON operator1.Operator1 = Rentals.Operator1

GROUP BY Rentals.Rental_ID, Charterers.Charterer_FName, Charterers.Charterer_Lname, Charterers.Charterer_MI, Charterers.Charterer_DOB, Charterers.Charterer_Age, Charterers.Charterer_Address, Charterers.Chartrerer_City, Charterers.Charterer_State, Charterers.Charterer_Country, Charterers.Charterer_Zip, Charterers.Charterer_Main_Tel, Charterers.Charterer_Local_Tel, Charterers.Charterer_DL_Number, Charterers.Charterer_DL_State, Charterers.Where_Hear_About_US, Rentalboats.Boat_number, Rentalboats.FL_number, Rentalboats.Persons_allowed, Rentals.AcceptWaiver, Rentals.Operator1, operator1.Charterer_FName, operator1.Charterer_Lname, operator1.Charterer_MI, operator1.Charterer_DOB, operator1.Charterer_Age, operator1.Charterer_Address, operator1.Chartrerer_City, operator1.Charterer_State, operator1.Charterer_Country, operator1.Charterer_Zip, operator1.Charterer_Main_Tel, operator1.Charterer_Local_Tel, operator1.Charterer_DL_Number, operator1.Charterer_DL_State, Rentals.DateIN, Rentals.DateOUT, Rentals.TimeIn, Rentals.TimeOut, Rentals.WaiverTodayOnly, Rentals.BoatRentalFee, Rentals.WaiverFee, Rentals.TowingFee
HAVING (((Rentals.Rental_ID)=([Enter Rental ID])));

 

[PHV]

Replace this:
Sum(Sum(Rentals.[BoatRentalFee + Rentals.WaiverFee + Rentals.TowingFee])) AS SubTotal
By this (as already posted)
Sum(Rentals.BoatRentalFee) + Sum(Rentals.WaiverFee) + Sum(Rentals.TowingFee) AS SubTotal

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[boatguy]

Thank you, no errors. I greatly appreciate your time.