SQL Aggregate function 1

[zimmer9]

I executed the following query:

SELECT tblCustomers.CustomerNumber, tblCustomers.DateLost, , tblProducts.MarketValue, tblProducts.CashBalance
FROM tblCustomers INNER JOIN tblProducts ON tblCustomers.CustomerNumber = tblProducts.CustomerNumber
GROUP BY tblCustomers.CustomerNumber, tblCustomers.DateLost, tblProducts.MarketValue, tblProducts.CashBalance
HAVING (((tblCustomers.DateLost) Between #8/1/2004# And #1/31/2005#));

Which gave me the following results:

CustNum Date Lost Market Value CashBalance
023414 10/01/2004 0.00 500.00
023414 10/01/2004 1454.00 0.00
023414 10/01/2004 3027.44
036983 12/01/2004 3098.94
047370 11/01/2004 598.76
062992 11/01/2004 43.21
066173 11/01/2004 17570.00
079580 11/01/2004 16173.51
138967 08/01/2004 29581.57

My problem is that I want to combine the values from the 2 fields Market Vale and Cash Balance into 1 field. Then I need to get 1 summary record per Customer Number.

For example, taking a look at customer number 023414 above, I want to run a SQL statement to yield a result set of 1 record per Customer Number (there can be more than 1 record per customer in the database) comprised of the following:

Check/Cert Amount
CustNum Date Lost (Market Value + Cash)
023414 10/1/2004 4981.44 ( = 500 + 1454.00 + 3027.44)

I tried to place the following statement into 1 field of the QBE grid in an attempt to add the values of these 2 fields into 1:

Check/Cert Amount: Format(Nz([CashBalance],Nz([MarketValue],0)),"#.00")

Then I tried to use Group By on this field and the SQL statement would not run successfully.

Is there a way to combine the values of 2 dollar fields and execute a Group Aggregate Function on this combined field ?

If not, then would you know how I can get 1 record per Customer Number when the dollar values that I am totalling on are in 2 different fields ?

Each record will have a dollar amount in 1 field or the other field (not in both fields on the same record).

 

[formerTexan]

Zimmer9

Unless I am misreading your posting, you should be OK by doing it in two steps (two queries).

First create a query with the CustNum/MarketValue/CashBalance fields and use the aggregate function (SUM) to get the sums for the MarketValue and CashBalance fields. This will return one record or row per customer.

Then write a second query (based on the first) that adds the fields (MarketValue + CashBalance). And don't forget the NZ function.

Cheers, Bill

 

[PHV]

What about this ?
SELECT C.CustomerNumber, C.DateLost, Sum(Nz(P.MarketValue,0)+Nz(P.CashBalance,0)) AS Total
FROM tblCustomers C INNER JOIN tblProducts P ON C.CustomerNumber = P.CustomerNumber
WHERE C.DateLost Between #8/1/2004# And #1/31/2005#
GROUP BY C.CustomerNumber, C.DateLost;


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

 

[zimmer9]

PHV you receive an honorary knighthood from me.
Thanks Sir PHV for the suggested SQL. It was right on the money.