- 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:
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
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