Switching Rows and Columns

[proballin]

I have a query that has three columns: months, weights and locations. In a report I want the format to be where the months are the column header across the top, the locations are the row headers on the side and the data filled in is the weight.

However when I do this I get constant repeats of the location name as opposed to 1 line with all the weights for each month. Any suggestion on how to get it where the weights for each month go across 1 row for each location, similar to how it would look in a table?

 

[boblarson]

Are you using a Crosstab query?

 

[proballin]

I tried using the crosstab query but the issue is when I select the value portion of the query, I dont want to do any aggregate operations on it...I just want the value itself. Even when I go ahead and just say "sum" I get an error that says "overflow"

 

[proballin]

I tried using both First and Last however I still get an overflow error. When I tried with less numbers it worked though. What I dont understand is that the amount of records I am working on is pretty small...so why the overflow error?

 

[proballin]

I figured out why the error was occuring. In my calculation one of the weights was divided by zero giving me an #Error in the query. Apparently a crosstab query won't work with any errors, so it gave me the overflow (most likely due to the divide by zero). Either way it works now.

 

[proballin]

Man who are you telling!? Lol...I had to find out the hard way about the parameters issue you mentioned when I created a report that linked to the crosstab query. I had to also put in explicit information into the columns headings section of properties. Crosstab queries are great and horrible all at the same time!