Sum option group fields in query

[foshizzle]

I had to modify how I was storing data on one of my tables. I changed checkboxes to an option group with the values ranging from 1 to 4.

I need to count each of these entries for a report which will run them into another formula.

I am trying to use the below - obv I'm doing something wrong.

Police: Sum(IIf([OType],1,0))

And this is the original SQL for my queries if needed:

qry_BLT_RCCounts1

SELECT Year([tblBlotter].[EntryDate]) AS [Year], Month([tblBlotter].[EntryDate]) AS [Month], Sum(IIf([OType],1,0)) AS Police, Sum(IIf([OType],3,0)) AS Ramp, Sum(IIf([OType],2,0)) AS Terminal, Sum(IIf([OType],4,0)) AS AOA, ([Terminal]+[Ramp]+[AOA]) AS TRATotal, ([Police]+[Terminal]+[Ramp]+[AOA]) AS TotalChecks, Year(DateAdd("m",-1,[tblBlotter].[EntryDate])) AS PrevYear, Month(DateAdd("m",-1,[tblBlotter].[EntryDate])) AS PrevMonth
FROM tblBlotter
GROUP BY Year([tblBlotter].[EntryDate]), Month([tblBlotter].[EntryDate]), Year(DateAdd("m",-1,[tblBlotter].[EntryDate])), Month(DateAdd("m",-1,[tblBlotter].[EntryDate])), Year([tblBlotter].[EntryDate])*12+DatePart('m',[tblBlotter].[EntryDate])-1, Year([tblBlotter].[EntryTime])*12+DatePart('m',[tblBlotter].[EntryTime])-1;

RCCounts2

SELECT Year([tblBlotter].[EntryDate]) AS [Year], Month([tblBlotter].[EntryDate]) AS [Month], Sum(IIf([OType],1,0)) AS Police, Sum(IIf([OType],3,0)) AS Ramp, Sum(IIf([OType],2,0)) AS Terminal, Sum(IIf([OType],4,0)) AS AOA, ([Terminal]+[Ramp]+[AOA]) AS TRATotal, ([Police]+[Terminal]+[Ramp]+[AOA]) AS TotalChecks, Year(DateAdd("m",-1,[tblBlotter].[EntryDate])) AS PrevYear, Month(DateAdd("m",-1,[tblBlotter].[EntryDate])) AS PrevMonth
FROM tblBlotter
GROUP BY Year([tblBlotter].[EntryDate]), Month([tblBlotter].[EntryDate]), Year(DateAdd("m",-1,[tblBlotter].[EntryDate])), Month(DateAdd("m",-1,[tblBlotter].[EntryDate])), Year([tblBlotter].[EntryDate])*12+DatePart('m',[tblBlotter].[EntryDate])-1, Year([tblBlotter].[EntryTime])*12+DatePart('m',[tblBlotter].[EntryTime])-1;

and finally, the Join

SELECT Year([tblBlotter].[EntryDate]) AS [Year], Month([tblBlotter].[EntryDate]) AS [Month], Sum(IIf([OType],1,0)) AS Police, Sum(IIf([OType],3,0)) AS Ramp, Sum(IIf([OType],2,0)) AS Terminal, Sum(IIf([OType],4,0)) AS AOA, ([Terminal]+[Ramp]+[AOA]) AS TRATotal, ([Police]+[Terminal]+[Ramp]+[AOA]) AS TotalChecks, Year(DateAdd("m",-1,[tblBlotter].[EntryDate])) AS PrevYear, Month(DateAdd("m",-1,[tblBlotter].[EntryDate])) AS PrevMonth
FROM tblBlotter
GROUP BY Year([tblBlotter].[EntryDate]), Month([tblBlotter].[EntryDate]), Year(DateAdd("m",-1,[tblBlotter].[EntryDate])), Month(DateAdd("m",-1,[tblBlotter].[EntryDate])), Year([tblBlotter].[EntryDate])*12+DatePart('m',[tblBlotter].[EntryDate])-1, Year([tblBlotter].[EntryTime])*12+DatePart('m',[tblBlotter].[EntryTime])-1;

My original thread
http://www.access-programmers.co.uk/forums/showthread.php?p=1330184#post1330184

 

[plog]

I don't think you are setting up your if statements correctly:

IIf([OType],1,0)

That means if [OType] is true the result will be 1, if not true the result will be 0. The value you are checking for is true/false, not a numeric value. The same goes for this:

IIf([OType],3,0)

That means if [OType] is true the result will be 3. I believe you want to check to see if OType is equal to 3, not if Otype is true. Is that correct? If so, this should be the Iif statement:

IIf([OType]=3,1,0)

With your logic the value for Ramp will always be 3 times Police, the value for Terminal will be 2 times Police, the value of AOA will be 4 times Police. Then expanding further, the value for TRATotal will always be 9 times Police.

 

[jdraw]

It might be easier for readers if you could give us the overview of what your database is about in 5-6 lines. Keep it in plain English, no jargon until we understand WHAT you are dealing with.

 

[foshizzle]

That was it.
You wouldn't have any idea why I am getting two separate line items on March when I run this query for year 2014 would you? I attached a sample.

 

[plog]

You have 2 records for March because this calculated field in both of your sub queries has 2 different values in march:

Year([tblBlotter].[EntryTime])*12+DatePart('m',[tblBlotter].[EntryTime])-1

Why is that field in your queries? Actually why are the last 2 fields in both of the queries? Neither of them are shown in the data, you are just using them to Group BY.

 

[foshizzle]

Im really not sure. Im was afraid to touch the SQL code, someone helped me on it from another thread. Or it could have been me trying to get it working with the new control type or during a copy paste got carried over. I saw that the checkbox wasnt enabled on the query, but still thought it was doing something..

That was it though. Thank you very much!