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).
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).