Equation help

[Thompyt]

SELECT Chng_ReqQry.Change_Type, Sum(IIf(([Date_Closed]=11/6/14),1,0)) AS CMB01, Sum(IIf([Date_Closed]=11/13/14,1,0)) AS CMB02
FROM Chng_ReqQry
GROUP BY Chng_ReqQry.Change_Type;

CMB01: Sum(IIf(([Date_Closed]=11/6/14),1,0))

I am getting a date as the output. What I am needing is the count of records where the date = 11/6/14 cna anyone provide an assist?



I have 12 records on that date

Output should be (example)

Change Type CMB01
Add......................1
Admin..................6
Delete..................4
Modify..................1

I am trying this way as I want to use another column using the same type of code

CMB02: Sum(IIf(([Date_Closed]=11/13/14),1,0)) and so on until I complete the date ranges I need.

Thanks

[June7]

That is SQL from query builder?

I don't see any problems. Although might use 4-digit year.

Show the SQL of Chng_ReqQry.

[Thompyt]

The above is SQL from query I am building now. n I am trying to avoid making 14 million queries with only the date range/date is different.


Chng_ReqQry SQL

SELECT Format(([Change Request].[CR_No]+([Sub_No]*0.01)),"Fixed") AS CR_Number, IIf([Sub_No]=0,Format([Change Request].[CR_No],"#"),Format([Sub_No]*0.01,"    " & " .00")) AS CR_Numbers, [Change Request].Change_Type, [Level] & Chr(13) & Chr(10) & [Soft Level] AS Levels, [Soft Level] & " " & [Level] AS Levelz, [Unit] & Chr(13) & Chr(10) & [Section] AS Units, [HB_Version] & Chr(13) & Chr(10) & [Approx_Page] AS [HB Vers], [MTOE_Para] & Chr(13) & Chr(10) & [Bumper_Number] AS [MTOE Paras], [Change Request].[Change Requested], [Change Request].Rationale, [Requestor] & Chr(13) & Chr(10) & [Sponsor] AS People, [Change Request].AO_Vote, [Change Request].O6_Vote, [Change Request].GO_Vote, [Change Request].Final_Vote, IIf(Not IsNull([Final_Vote]),([Final_Vote]),(IIf(Not IsNull([GO_Vote]),([GO_Vote]),(IIf(Not IsNull([O6_Vote]),([O6_Vote]),([AO_Vote])))))) AS Votes, Switch.Status, [Change Request].NOTES, [Change Request].Action_Items, [Change Request].Action_Complete, [Change Request].Date_Input, [Change Request].Date_Closed, [Change Request].Priority, [Change Request].Hr, [Change Request].NIE, [Change Request].Level, [Change Request].Date_ID, [Change Request].CR_ID, [Change Request].Sub_No, [Change Request].CR_No, [Change Request].[Soft Level], [Change Request].Requestor, [Change Request].Sponsor, (IIf(Not IsNull([Final_Vote]),([Final_Vote]),(IIf(Not IsNull([GO_Vote]),([GO_Vote]),(IIf(Not IsNull([O6_Vote]),([O6_Vote]),(Switch([AO_Vote]="Approve","Approved",[AO_Vote]="Withdraw","Withdrawn",[AO_Vote]="Deny","Denied",[AO_Vote]="Deferred","Deferred")))))))) AS Votes1, [Change Request].MTOE_Para, [Change Request].Section, [Change Request].Bumper_Number
FROM Switch INNER JOIN [Change Request] ON Switch.CR_ID = [Change Request].CR_ID
GROUP BY Format(([Change Request].[CR_No]+([Sub_No]*0.01)),"Fixed"), IIf([Sub_No]=0,Format([Change Request].[CR_No],"#"),Format([Sub_No]*0.01,"    " & " .00")), [Change Request].Change_Type, [Level] & Chr(13) & Chr(10) & [Soft Level], [Soft Level] & " " & [Level], [Unit] & Chr(13) & Chr(10) & [Section], [Change Request].[Change Requested], [Change Request].Rationale, [Change Request].AO_Vote, [Change Request].O6_Vote, [Change Request].GO_Vote, [Change Request].Final_Vote, Switch.Status, [Change Request].NOTES, [Change Request].Action_Items, [Change Request].Action_Complete, [Change Request].Date_Input, [Change Request].Date_Closed, [Change Request].Priority, [Change Request].Hr, [Change Request].NIE, [Change Request].Level, [Change Request].Date_ID, [Change Request].CR_ID, [Change Request].Sub_No, [Change Request].CR_No, [Change Request].[Soft Level], [Change Request].Requestor, [Change Request].Sponsor, [Change Request].MTOE_Para, [Change Request].Section, [Change Request].Bumper_Number, [Change Request].Approx_Page, [Change Request].Unit, [Change Request].HB_Version, [Change Request].MTOE_Para, [Change Request].Section, [Change Request].Bumper_Number
ORDER BY [Change Request].CR_ID;

[June7]

Switch is name of a table? Switch is also an intrinsic function and therefore a reserved word. Should avoid reserved words as names. However, I don't think that would be an issue in this case.

I don't see any reason for the grouping query to not work.

[Thompyt]

I must be doing something wrong as I get a date output. I'll fix the Switch table to another name.

What I get out:

Change Type CMB01
Add....................6/16/1900
Admin.................4/12/1900
Delete.................4/3/1900........and so on

[pbaldy]

You need # for starters:

CMB01: Sum(IIf(([Date_Closed]=#11/6/14#),1,0))

[June7]

Oooops. Should have seen that.

[Thompyt]

I tried it at work with the # and got the above. It works fine at the house now. I even threw in a between in the equation and it worked fine. I'll play with it at work tomorrow. It'll keep me occupied.

Thanks gents, much appreciated.