Sum not working! (URGENT HELP NEEDED)

[Bee]

Hi,

I have used sum() function in a query where I have got other attributes. However, the sum() doesn't work for some reason.

I could not attach my database as its size is too big. i have uploaded it to this server where you can download it.
aimreda.co.uk/db

Any help will be very much appreciated.

Regards,
B

 

[ByteMyzer]

In the SQL of your query, try changing:

Sum(tblVariation_Order.VO_Price) AS SumOfVO_Price

...to:
Sum(Nz(tblVariation_Order.VO_Price,0)) AS SumOfVO_Price

 

[Bee]

In the SQL of your query, try changing:

Sum(tblVariation_Order.VO_Price) AS SumOfVO_Price

...to:
Sum(Nz(tblVariation_Order.VO_Price,0)) AS SumOfVO_Price

Will do. Thank you.

 

[Bee]

In the SQL of your query, try changing:

Sum(tblVariation_Order.VO_Price) AS SumOfVO_Price

...to:
Sum(Nz(tblVariation_Order.VO_Price,0)) AS SumOfVO_Price

That did not work. it only took away the decimal point of the 'VO_Price'. Did it work for you?

I want it to display the total of 2000.00 + 656.00. = 2656.00

 

[ByteMyzer]

I have used sum() function in a query where I have got other attributes. However, the sum() doesn't work for some reason.

You did not specify in what way that it was not working for you. I ran the query and saw that there were nulls where there should have been values, and so I posted my solution.

If that is not what you meant, then please elaborate. Tell us, specifically, how the Sum() function is not working for you.


Favorite Tech Support dialogue: "Help, I got an error message!" "What did the message say?" "Oh, I didn't read it..."

 

[Bee]

You did not specify in what way that it was not working for you. I ran the query and saw that there were nulls where there should have been values, and so I posted my solution.

If that is not what you meant, then please elaborate. Tell us, specifically, how the Sum() function is not working for you.


Favorite Tech Support dialogue: "Help, I got an error message!" "What did the message say?" "Oh, I didn't read it..."

Hehe, Sorry.

Ok, basically, I want to sum the values of the attribute VO_Price. The current values the database is returning are 656 and 2000. Both these values belong to customer number 2 and therefore they should be sumed up. Some customers don't have VOs. Hence the nulls.

Do you think nulls can create problems during the sum?

The outcome I am expecting in the query I posted is that 2656, which is the total of the VOs cust 2 has.

I hope this clarifies my question. If not let me know and I will do my best again to explain it differently

 

[pdx_man]

Then you have to get rid of the VO & VO_Signed fields in your query. They have unique values that is throwing off the Group By:

SELECT tblPhase.Phase_No, tblPhase.Roads_Bond_Received, tblPhase.Roads_Bond_Ref_No, tblPhase.Roads_Adopted, tblSite.Site_No, tblSite.Department_Number, tblSite.Name, tblSite.Designer, tblSite.Agent, tblHouse.House_No, tblHouse.House_Address, tblHouse.Town, tblHouse.House_P_Code, tblHouse.House_Type, tblHouse.Gross_Floor_Area, tblHouse.Nf_Apartments, tblHouse.Number_Of_Bedrooms, tblHouse.Garage, tblHouse.Agreed_Price, tblHouse.House_Price, tblHouse.Deposit_Received, tblHouse.Deposit_Amount, tblHouse.Remaining_Balance_Paid, tblHouse.House_Stage, tblHouse.Electricity_Meter_Reading, tblHouse.Gas_Meter_Reading, tblHouse.Water_Meter_Reading, tblHouse.Plot_Depth, tblHouse.Date_Of_Entry, tblHouse.House_Handed_Over, tblHouse.Building_Warrant_Ref, tblHouse.Date_Building_Warrant_Received, tblHouse.Full_Completion_Certificate, tblHouse.NHBC_Registration_No, tblHouse.NHBC_Registration_Date, tblHouse.[%Complete], tblServices.Date_Made, tblServices.BT_Reimbursement_Claimed, tblServices.Sewer_Reimbursement_Value, tblServices.First_Drain_Test, tblServices.Sewer_Drawing_Ref, tblServices.Sewer_Design_Approved, tblServices.Sewer_Adopted, tblServices.Sewer_Reimbursement_Agreement, tblServices.Reimbursement_Recieved, tblServices.Service_Ref, tblServices.Phone_Reimbursement_Amount, Sum(tblVariation_Order.VO_Price) AS SumOfVO_Price, tblVariation_Order.Date_VO_Paid, tblCustomer.Title, tblCustomer.First_Name, tblCustomer.Surname, tblSolicitor.Forename, tblSolicitor.Surname, tblServices.Service_Type
FROM (tblSite INNER JOIN (tblPhase INNER JOIN tblServices ON tblPhase.Phase_No = tblServices.Phase_no) ON tblSite.Site_No = tblPhase.Site_No) INNER JOIN (tblSolicitor RIGHT JOIN ((tblCustomer INNER JOIN tblHouse ON tblCustomer.Customer_No = tblHouse.Customer_No) LEFT JOIN tblVariation_Order ON tblCustomer.Customer_No = tblVariation_Order.Customer_No) ON tblSolicitor.Solicitor_ID = tblCustomer.Solicitor_ID) ON tblPhase.Phase_No = tblHouse.Phase_No
GROUP BY tblPhase.Phase_No, tblPhase.Roads_Bond_Received, tblPhase.Roads_Bond_Ref_No, tblPhase.Roads_Adopted, tblSite.Site_No, tblSite.Department_Number, tblSite.Name, tblSite.Designer, tblSite.Agent, tblHouse.House_No, tblHouse.House_Address, tblHouse.Town, tblHouse.House_P_Code, tblHouse.House_Type, tblHouse.Gross_Floor_Area, tblHouse.Nf_Apartments, tblHouse.Number_Of_Bedrooms, tblHouse.Garage, tblHouse.Agreed_Price, tblHouse.House_Price, tblHouse.Deposit_Received, tblHouse.Deposit_Amount, tblHouse.Remaining_Balance_Paid, tblHouse.House_Stage, tblHouse.Electricity_Meter_Reading, tblHouse.Gas_Meter_Reading, tblHouse.Water_Meter_Reading, tblHouse.Plot_Depth, tblHouse.Date_Of_Entry, tblHouse.House_Handed_Over, tblHouse.Building_Warrant_Ref, tblHouse.Date_Building_Warrant_Received, tblHouse.Full_Completion_Certificate, tblHouse.NHBC_Registration_No, tblHouse.NHBC_Registration_Date, tblHouse.[%Complete], tblServices.Date_Made, tblServices.BT_Reimbursement_Claimed, tblServices.Sewer_Reimbursement_Value, tblServices.First_Drain_Test, tblServices.Sewer_Drawing_Ref, tblServices.Sewer_Design_Approved, tblServices.Sewer_Adopted, tblServices.Sewer_Reimbursement_Agreement, tblServices.Reimbursement_Recieved, tblServices.Service_Ref, tblServices.Phone_Reimbursement_Amount, tblVariation_Order.Date_VO_Paid, tblCustomer.Title, tblCustomer.First_Name, tblCustomer.Surname, tblSolicitor.Forename, tblSolicitor.Surname, tblServices.Service_Type
HAVING (((tblServices.Service_Type) Like "Phone" Or (tblServices.Service_Type) Like "Sewer"));

 

[Bee]

Then you have to get rid of the VO & VO_Signed fields in your query. They have unique values that is throwing off the Group By:

I need these two attributes. Is there any way of keeping them and getting the calculation right?

 

[gemma-the-husky]

No, thats the way the group by works. Think of it this way. If you want a sum grouped by a particular attribute, then you will get a subtotal for each attribute. If you are not bothered about the attribute, you will get an overall total. How can you have both?

 

[pdx_man]

Think about it for a second. You have $656.00 associated to Additional Living Room signed 06/20/2006 and $2,000.00 associated to Bathroom in the garden signed 06/22/2006

VO                         VO_Signed	SumOfVO_Price
Additional Living Room	06/20/2006	$656.00
Bathroom in the garden	06/22/2006	$2,000.00

You want them summed up, but you also want the detail. Hmmm ... can't have both.

If you need this detail for one purpose, but the summed figure for another, you can always create another query with the source being this query and group by the customer summing the SumOfVO_Price.

SELECT rqryFinancial.Phase_No, Sum(rqryFinancial.SumOfVO_Price) AS SumOfSumOfVO_Price
FROM rqryFinancial
GROUP BY rqryFinancial.Phase_No;