Conditional use of data.

J

James O

Hello all,

I asked a question about this afew days ago and recieved an answer that
worked like a charm. However today I found the report changed and my numbers
askew. So to start off here is my code. Basically. I have Different Agency
Codes Different MIO's and different Hcount on a table. I currently Count all
the times an agency code appears under a given MIO and sum of the Hcount for
that agency code. However with NACW I currently do not count the times an
Agency Code appears under NACW only NACC and I add the sum of Hcount for NACW
Agengcy Codes to the NACC corresponding Agency Codes.



SELECT TblR2080.AgencyCode, Sum(IIf([MIO]="NACW",0,1)) AS [Count of
Vendors], Sum(TblR2080.HCount) AS SumOfVolume, IIf([MIO]="NACW","NACC",[MIO])
AS MIOs, TblR2080.Date
FROM TblR2080
GROUP BY TblR2080.AgencyCode, IIf([MIO]="NACW","NACC",[MIO]), TblR2080.Date
HAVING (((TblR2080.AgencyCode)<>"2240"));

So I have

VendorCode Volume MIO
2515 2 NBCC
2515 2 NBCC
2516 2 NACC
2516 2 NACW

And get this in the query

VendorCode CountOfVendor Volume MIO
2515 2 4 NBCC
2516 1 4 NACC

This has been working great... however... somthing new has developed. I need
to add an if to my sql. If there are no Vendor Codes for NACC then I will
need to count the Vendor Codes for NACW. Thanks very much for any help that
you can offer an dlet know if you have any questions or concerns thanks!

James O'Donnell
 
Top