Sum option group fields in query (1 Viewer)

foshizzle

Registered User.
Local time
Today, 16:46
Joined
Nov 27, 2013
Messages
277
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.

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

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

qry_BLT_RCCounts1
Code:
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
Code:
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
Code:
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

Banishment Pending
Local time
Today, 15:46
Joined
May 11, 2011
Messages
11,611
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

Super Moderator
Staff member
Local time
Today, 16:46
Joined
Jan 23, 2006
Messages
15,364
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

Registered User.
Local time
Today, 16:46
Joined
Nov 27, 2013
Messages
277
:) 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.
 

Attachments

  • RCCounts.accdb
    1.4 MB · Views: 160

plog

Banishment Pending
Local time
Today, 15:46
Joined
May 11, 2011
Messages
11,611
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

Registered User.
Local time
Today, 16:46
Joined
Nov 27, 2013
Messages
277
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!
 

Users who are viewing this thread

Top Bottom