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.
And this is the original SQL for my queries if needed:
qry_BLT_RCCounts1
RCCounts2
and finally, the Join
My original thread
http://www.access-programmers.co.uk/forums/showthread.php?p=1330184#post1330184
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