Switch Formula in Query

[LadyDi]

I am trying to use a Switch formula in a query so that I can list results in the query horizontally by month. However I am having a lot of trouble with it. The only way I can get the query to run is to enter the formula like this:

Mar_Totals: IIf(Count(Switch([WIP_MONTH_NUM]='03',1))=1,Sum([WIP_AMOUNT]),' ')

Unfortunately, when I do that for each month (changing the Month_Num for each month), the query gives me the same total for every month. I tried entering this formula

IIf(Switch(CInt([WIP_MONTH_NUM])=3,1)=1,Sum([WIP_AMOUNT]),' ')

, but then I get an error message stating that "you tried to execute a query that does not include the specified expression IIf(Switch(CInt([WIP_MONTH_NUM])=1,2)=2,Sum([WIP_AMOUNT]),' ') as part of an aggregate function." I also tried just using a straight IIF statement, but received the same error message. In the Totals row, I have this column listed as an expression. The Group By is on the Part Number.

How can I get this query to work? I got it in a pivot table, but I'm going to want to export the results of the query into an Excel sheet, and I'm not sure that the pivot table will work for that. Any advice you can provide would be greatly appreciated.

 

[namliam]

How about creating a crosstab query? It can be exported to an Excel sheet quite easily.

 

[namliam]

This should work if you want to "not use a crosstab"

Mar_Totals: sum(IIf([WIP_MONTH_NUM]='03',[WIP_AMOUNT],0))

 

[LadyDi]

The formula you provided works great. Is there a way (without re-ordering the query every month) to get the columns to go in sequential order? For example, I would want this month to go May 2013 - April 2014. As it stands right now, my query always shows Jan to December no matter the year.

I tried the crosstab query, and am having difficulty getting that to work. I included the month and year in the query and sorted by the year. When I do that, it shows me all the results for 2013 and then it repeats all the part numbers for the months that have 2014 data. If I add the month in to get it in the proper order then it repeats the part numbers for every month. I also tried using my part numbers as the column headings instead of the Month Name. Then I got an error that there were too many columns (I have 415 parts that I am reporting on).

What do you recommend?

 

[namliam]

For a crosstab use Year month format instead, which will order it properly always

For "doing it manually" it should be relatively simple to make some VBA to adjust the query on the fly and enter the columns as you need them
The code would look something like:

Sub x()
    Dim sql As String
    Dim i As Integer
    
    sql = "select ...., .... "
    For i = 1 To 12
        sql = sql & ", sum(IIf([WIP_MONTH_NUM]='" & Format(i, "00") & "',[WIP_AMOUNT],0)) as " & Format(DateAdd("M", i - 1, Date), "MMM YYYY") & " _Totals "
    Next i
    sql = sql & "from ...."
    sql = sql & "group by ...."
    
    CurrentDb.QueryDefs("YourQuery").sql = sql
    DoCmd.OpenQuery "YourQuery"
End Sub

 

[LadyDi]

The Crosstab worked perfectly when I set the date up the way you suggested. Thank you very much for your assistance. I appreciate it.