Help With Getting Counts using a querry 1

[egstatus]

I am trying to get counts from a table for the current month and whether the Bates are T or M's.

The field Bates contains a "T" or "M" on the 6th position. I want to get counts for both T(Trade) and M(Magazine) in that position and the total of both.
This is what my query looks like right now [note I am only getting the T counts, if I try to incorporate the "M" count the querry does not work]

SELECT CDNUMBER, Add_Date, Count(Bates) AS Trade
FROM edtest
WHERE (((Mid([bates],6,1))="T") AND ((Month([Add_date]))=[Enter Month]))
GROUP BY CDNUMBER, Add_Date;

This is what I'm getting right now:

CDNUMBER	Add_Date	Trade
CD0936	     5/15/2014	     3
CD0937	     5/14/2014	     4

This is what I would like to get

CDNUMBER	Add_Date	Trade  Magazine   Total
CD0936	     5/15/2014	     3    5    8
CD0937	     5/14/2014	     4    2    6

This is a snap shot of my data:
"CALLLETTER","DATE","INVOICE","AMOUNT","BATES","CDNUMBER","MOS","ADD_DATE"
"2984","030514","16570270","00000417400","AE513M1001","CD0936","04/14",5/15/2014 0:00:00
"6593","041414","66327","00000688500","AE513M1002","CD0936","04/14",5/15/2014 0:00:00
"5758","050114","7744","00000499500","AE513T1001","CD0937","05/14",5/14/2014 0:00:00
"7285","050714","MD144303","00000107501","AE513T1002","CD0936","05/14",5/15/2014 0:00:00
"7878","041514","MD1301919","00000700000","AE513T1003","CD0937","04/14",5/14/2014 0:00:00
"7100,01","050114","6250","00002000000","CV513M1001","CD0936","05/14",5/15/2014 0:00:00
"9289","032814","800057164","00001984700","CV513M1002","CD0937","05/14",5/14/2014 0:00:00
"2313","042514","9848","00000390925","CV513T1001","CD0936","04/14",5/15/2014 0:00:00
"7314","050514","73900661","00000549500","CV513T1002","CD0937","05/14",5/14/2014 0:00:00
"2878","051314","382014051300620","00005275900","HH513M1001","CD0936","06/14",5/15/2014 0:00:00
"2878","051314","382014051300610","00005275900","HH513M1002","CD0936","06/14",5/15/2014 0:00:00
"5332","041614","12528","00000055000","HH513M1003","CD0937","05/14",5/14/2014 0:00:00
"3452","010614","SALE0000451","00000800000","HH513T1001","CD0936","04/14",5/15/2014 0:00:00
"5115","050814","4375911","00000883575","HH513T1002","CD0937","05/14",5/14/2014 0:00:00

Thanks in advance.
Ed

 

[PHV]

SELECT CDNUMBER, Add_Date, Sum(IIf(Mid(Bates,6,1)='T',1,0)) AS Trade, Sum(IIf(Mid(Bates,6,1)='M',1,0)) AS Magazine, Count(*) As Total
FROM edtest
WHERE Mid(Bates,6,1) IN ('T','M') AND Month(Add_date)=[Enter Month]
GROUP BY CDNUMBER, Add_Date

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[egstatus]

Thanks very much.
Ed