• ### Question

• Table: sell

invoiceNo      cash       card        sellDate

1                 2            0          29/6/2011

2                 0            10        29/6/2011

3                 0            10        29/6/2011

3                 6            0          29/6/2011

Table: void

invoiceNo        total           voidDate

3                10             29/6/2011

3                 6               29/6/2011

after that, i come out with this query and result:

SELECT sellDate, Sum(card) as cardSum, Sum(cash) as cashSum FROM (SELECT invoiceNo, cash, card, sellDate FROM sell) GROUP BY sellDate

sellDate           cardSum          cashSum

29/6/2011           20                   8

but i wanted this result:

sellDate           cardSum          cashSum   voidTotal

29/6/2011           20                   8              16

Please help, i tried for more than 3 hours, and still not able to solve this :(

Tuesday, June 28, 2011 6:10 PM

• Try the following select statement:

```select S.SellDate, S.CardSum, S.CashSum, V.VoidTotal from (select SellDate, sum(Card) as CardSum, sum(Cash) as CashSum from Sell GROUP BY SellDate) S

LEFT JOIN (select voidDate, SUM(Total) as voidTotal from Void GROUP BY VoidDate) V on S.SellDate = V.VoidDate
```

I assume you don't store time portion with your dates.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Marked as answer by Tuesday, June 28, 2011 6:52 PM
Tuesday, June 28, 2011 6:40 PM
• I see. This forum is for MS SQL Server. In Access you can use

IIF(V.VoidTotal IS NULL, 0, V.VoidTotal) as VoidTotal

or alternatively you can use NZ function http://www.techonthenet.com/access/functions/advanced/nz.php

NZ(V.VoidTotal, 0) as VoidTotal

For every expert, there is an equal and opposite expert. - Becker's Law

My blog
• Marked as answer by Tuesday, June 28, 2011 7:20 PM
Tuesday, June 28, 2011 7:13 PM

### All replies

• Try the following select statement:

```select S.SellDate, S.CardSum, S.CashSum, V.VoidTotal from (select SellDate, sum(Card) as CardSum, sum(Cash) as CashSum from Sell GROUP BY SellDate) S

LEFT JOIN (select voidDate, SUM(Total) as voidTotal from Void GROUP BY VoidDate) V on S.SellDate = V.VoidDate
```

I assume you don't store time portion with your dates.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Marked as answer by Tuesday, June 28, 2011 6:52 PM
Tuesday, June 28, 2011 6:40 PM
• Thanks a lot! it helped!!
Tuesday, June 28, 2011 6:52 PM
• another question, how to verify the the voidTotal as 0 if no record found?
Tuesday, June 28, 2011 7:01 PM
• Add ISNULL or COALESCE for VoidTotal to convert NULL to 0
```select S.SellDate, S.CardSum, S.CashSum, COALESCE(V.VoidTotal,0) as VoidTotal from (select SellDate, sum(Card) as CardSum, sum(Cash) as CashSum from Sell GROUP BY SellDate) S

LEFT JOIN (select voidDate, SUM(Total) as voidTotal from Void GROUP BY VoidDate) V on S.SellDate = V.VoidDate

```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog
Tuesday, June 28, 2011 7:03 PM
• if i added ISNULL, error msg :

Wrong number of arguments used with function in query expression 'ISNULL(V.VoidTotal,0)'.

if COALESCE:

Undefined function 'COALESCE' in expression.

am using MS access

Tuesday, June 28, 2011 7:09 PM
• I see. This forum is for MS SQL Server. In Access you can use

IIF(V.VoidTotal IS NULL, 0, V.VoidTotal) as VoidTotal

or alternatively you can use NZ function http://www.techonthenet.com/access/functions/advanced/nz.php

NZ(V.VoidTotal, 0) as VoidTotal

For every expert, there is an equal and opposite expert. - Becker's Law

My blog
• Marked as answer by Tuesday, June 28, 2011 7:20 PM
Tuesday, June 28, 2011 7:13 PM
• Thank again =) you are so good ~
Tuesday, June 28, 2011 7:20 PM