Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-26-2014, 11:43 AM   #1
helmerr
Newly Registered User
 
Join Date: Nov 2013
Posts: 140
Thanks: 33
Thanked 0 Times in 0 Posts
helmerr is on a distinguished road
Sum option group fields in query

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/...84#post1330184

helmerr is offline   Reply With Quote
Old 03-26-2014, 11:55 AM   #2
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,419
Thanks: 11
Thanked 2,285 Times in 2,237 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Sum option group fields in query

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.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
helmerr (03-26-2014)
Old 03-26-2014, 12:15 PM   #3
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,273
Thanks: 96
Thanked 2,030 Times in 1,977 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Sum option group fields in query

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.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 03-26-2014, 12:40 PM   #4
helmerr
Newly Registered User
 
Join Date: Nov 2013
Posts: 140
Thanks: 33
Thanked 0 Times in 0 Posts
helmerr is on a distinguished road
Smile Re: Sum option group fields in query

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.
Attached Files
File Type: accdb RCCounts.accdb (1.43 MB, 71 views)
helmerr is offline   Reply With Quote
Old 03-26-2014, 12:55 PM   #5
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,419
Thanks: 11
Thanked 2,285 Times in 2,237 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Sum option group fields in query

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.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
helmerr (03-26-2014)
Old 03-26-2014, 01:02 PM   #6
helmerr
Newly Registered User
 
Join Date: Nov 2013
Posts: 140
Thanks: 33
Thanked 0 Times in 0 Posts
helmerr is on a distinguished road
Re: Sum option group fields in query

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!

helmerr is offline   Reply With Quote
Reply

Tags
count , option group , query , sum , sumif

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Option Group - warn user when neither of 2 in option group not ticked? Mitchell199 Forms 1 07-25-2013 12:11 PM
Query Subform with Option Group in alpertunga65 Forms 1 03-15-2013 05:35 PM
Using an Option Group for Query Criteria Nasapat Queries 4 12-09-2010 01:28 PM
Option group visible and invisible numerical fields xaysana Modules & VBA 4 11-22-2007 06:02 PM
Using Option Group for Yes/No fields PaulA Forms 1 10-13-2005 12:37 PM




All times are GMT -8. The time now is 01:40 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World