Build report in Access

I have a query that lists the weekly totals for 3 offices. It is grouped by the week number Week: Format([Bookings.DateRecorded],"ww".

The resulting query looks something like this:

Week           Office            Office Weekly Total
5                  Office A               500
5                  Office B                750
5                  Office C                 600
6                  Office A               100
6                  Office B                300
6                  Office C                 400

The end result I would like to be a report with Week number in Column 1. Office A week total is Column 2. Office B week total in Column 3.

Something like this:

Week           Office A Week Total               Office B Week Total              Office C Week Total
5                             500                                                  750                                         600
6                              100                                                 300                                          400

Now that I write this, I think I have to change the query, not the report. The query is a 3 column query. Week, Office (which is grouped) then Total (which is sum). How do I add more columns to the query to it only adds Office A, then column for Office B.

Hope I am making sense. THanks!
Who is Participating?
Rey Obrero (Capricorn1)Commented:
do you mean like this

SELECT yourQuery.Week, Sum(IIf([Office]="Office A",[Office Weekly Total],0)) AS [Office A Week Total], Sum(IIf([Office]="Office B",[Office Weekly Total],0)) AS [Office B Week Total], Sum(IIf([Office]="Office C",[Office Weekly Total],0)) AS [Office C Week Total]
FROM yourQuery
GROUP BY yourQuery.Week
Rey Obrero (Capricorn1)Commented:
try using the Crosstab Query wizard

create > query wizard > Crosstab Query wizard
cansevinAuthor Commented:
I think I need help with a sumif column in my query. Basically Column 3 would be Sumif the fieild Office.[Customer Info Table] is "Office A".

Just not sure how to write that Sumif. The field is Office from Customer Info Table. The sum field is Exteneded Price from Booking Details Extended.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.