select sum question Version: 2002 (10.0) XP

Joined
Aug 12, 2007
Messages
2
Reaction score
0
Hi, I'm a newbie in Access and SQL. I am trying to write a query in Access that returns the sum of values on a column. The query works fine, the only problem is that if there are no values it return empty. I would like to return value zero in this situation.

I am working on an app that is linked to an Access db. I have three tables:

1. TableExpenses1 with a field DateExpenses1, CostExpenses1, ...
2. TableExpenses2 with a field DateExpenses2, CostExpenses2, ...
3. TableExpenses3 with a field DateExpenses3, CostExpenses3, ...

I need to generate a report in Access like this:


Between Starting Date X and Ending Date Y there are the following Expenses:
for Expenses 1 we have E1 dollars
for Expenses 2 we have E2 dollars
for Expenses 3 we have E3 dollars

Total: E1+E2+E3 dollars


My query looks like this:

Code:
 SELECT TotalSumForExpenses1, TotalSumForExpenses2, TotalSumForExpenses3, TotalSumForExpenses1+TotalSumForExpenses2+TotalSumForExpenses3 AS TotalSumExpenses, StartingDate, EndingDate
 
 FROM
 (
 SELECT TotalSumForExpenses1
 FROM 
 (
 	SELECT Sum(IntermediateSums1)  AS TotalSumForExpenses1
 	FROM
 	(
 		SELECT TableExpenses1.Price AS IntermediateSums1
 			FROM TableExpenses1
 			WHERE 
 			(
 				(
 					(TableExpenses1.Price)=
 					IIf(
 						IsNull(TableExpenses1.Price),
 						0,	
 						TableExpenses1.Price
 					)
 				)
 				AND
 				(TableExpenses1.MyDate>=StartingDate) 
 				AND
 				(TableExpenses1.MyDate<=EndingDate)
 			)
 	)
 )
 WHERE
 (TotalSumForExpenses1=IIf(IsNull(TotalSumForExpenses1),0,TotalSumForExpenses1))
 ),
 
 
 (
 SELECT TotalSumForExpenses2
 FROM 
 (
 	SELECT Sum(IntermediateSums2)  AS TotalSumForExpenses2
 	FROM
 	(
 		SELECT TableExpenses2.Price AS IntermediateSums2
 			FROM TableExpenses2
 			WHERE 
 			(
 				(
 					(TableExpenses2.Price)=
 					IIf(
 						IsNull(TableExpenses2.Price),
 						0,	
 						TableExpenses2.Price
 					)
 				)
 				AND
 				(TableExpenses2.MyDate>=StartingDate) 
 				AND
 				(TableExpenses2.MyDate<=EndingDate)
 			)
 	)
 )
 WHERE
 (TotalSumForExpenses2=IIf(IsNull(TotalSumForExpenses2),0,TotalSumForExpenses2))
 ),
 
 
 (
 SELECT TotalSumForExpenses3
 FROM 
 (
 	SELECT Sum(IntermediateSums3)  AS TotalSumForExpenses3
 	FROM
 	(
 		SELECT TableExpenses3.Price AS IntermediateSums3
 			FROM TableExpenses3
 			WHERE 
 			(
 				(
 					(TableExpenses3.Price)=
 					IIf(
 						IsNull(TableExpenses3.Price),
 						0,	
 						TableExpenses3.Price
 					)
 				)
 				AND
 				(TableExpenses3.MyDate>=StartingDate) 
 				AND
 				(TableExpenses3.MyDate<=EndingDate)
 			)
 	)
 )
 WHERE
 (TotalSumForExpenses3=IIf(IsNull(TotalSumForExpenses3),0,TotalSumForExpenses3))
 )
 ;

Any help is very appreciated. Unfortunately I've spent already 2 days struggling and destroying my brains using with only sql statements to solve the problem.

Regards,
Cristian
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top