[RESOLVED] VB6 (Access) SQL Question

[dglienna]

I have 2 SQL Queries that I'd like to combine.

First, I get BatchDate and Closed Totals.

SELECT DISTINCTROW Transactions.BatchDate, Count(Transactions.BatchDate) AS [OpenTotal]
FROM Transactions
WHERE (((Transactions.DeletedFlag)=0))
GROUP BY Transactions.BatchDate;

and this gets BatchDate and Open Totals

SELECT DISTINCTROW Transactions.BatchDate, Count(Transactions.BatchDate) AS [ClosedTotal]
FROM Transactions
WHERE (((Transactions.DeletedFlag)=1))
GROUP BY Transactions.BatchDate;

How could I combine this to get:

BatchDate, OpenTotal, ClosedTotal

instead of two different RecordSets?

Thanks.

[Sabin_33]

Group by 'DeletedFlag' and use HAVING DeletedFlag = 0 or deletedflag = 1.

SELECT DISTINCTROW Transactions.BatchDate, '0' as [OpenTotal] Count(Transactions.BatchDate) AS [ClosedTotal]
FROM Transactions
GROUP BY Transactions.BatchDate, Transactions.DeletedFlag
HAVING Transactions.DeletedFlag= 1 
OR Transactions.DeletedFlag = 0;

Or you can do a UNION Query if you only want one record

SELECT DISTINCTROW Transactions.BatchDate, '0' as [OpenTotal] Count(Transactions.BatchDate) AS [ClosedTotal]
FROM Transactions
WHERE (((Transactions.DeletedFlag)=1))
GROUP BY Transactions.BatchDate
UNION ALL
SELECT DISTINCTROW Transactions.BatchDate, Count(Transactions.BatchDate) as [OpenTotal], '0'  AS [ClosedTotal]
FROM Transactions
WHERE (((Transactions.DeletedFlag)=1))
GROUP BY Transactions.BatchDate

[dglienna]

Thanks for the help. Things are getting better, but there seems to be a problem.

The second OPEN column is all '0's, and the closed counts Open or Closed, apparently.

I'd like the second column to count the '0's (Open) and the third column to count the same column, except tallying the '1's.

Batch__:Open_:Closed
_123___:__34_:___4_
_124___:_536_:__24_

I haven't tried the UNION yet, as I want one record for each Batch number. There is also a TransNo that pertain to each Batch, and they are combined into a text key.

[hensa22]

try this, I hope it's useful

SELECT Transactions.BatchDate, sum(iif(Transactions.DeletedFlag=0,1,0)) AS ClosedTotal,
sum(iif(Transactions.DeletedFlag=1,1,0)) AS OpenTotal
FROM Transactions
WHERE Transactions.DeletedFlag in (0,1)GROUP BY Transactions.BatchDate;

[dglienna]

Thanks. It's working the way I want, this way.

SELECT DISTINCTROW Transactions.BatchDate, Sum(IIf([Transactions].[DeletedFlag]=1,0,1)) AS OpenTotal, Sum(IIf([Transactions].[DeletedFlag]=0,1,0)) AS ClosedTotal
FROM Transactions
WHERE (((Transactions.DeletedFlag) In (0,1)))
GROUP BY Transactions.BatchDate;

EDIT: Arrgh! Keep in mind that the IIF() statement seems to
work only if DeletedFlag is a String!!!