[RESOLVED] Access 2000 (Report): Result Orientation

[sevenhalo]

The following is the result set that I'm working with:

C6N	2.3875	2.3200	-4.0000	2.3300
C6U	2.5000	2.4375	-4.5000	2.4450
C6Z	2.6400	2.5775	-3.7500	2.5850
C7H	2.7450	2.6850	-4.2500	2.6925
C7K	2.8100	2.7625	-4.0000	2.7650
C7N	2.8850	2.8275	-3.7500	2.8350
C7Z	2.9600	2.9000	-3.2500	2.9075
C8H	3.0200	3.0000	-1.5000	3.0000
C8Z	3.1125	3.1125	.7500	3.1125

The first letter of the symbol is the commodity, the second is the year and the third is the month (H = March, Z = December, ect). The other fields are the high, lows, change and settlements.

For the report I'm making, I need the symbols in the first field to repeat horizontally on the report instead of vertically. Under each one, will be the details about that month.

The question I have is, is there a way (or a control) in access that I can use to change the orientation to display the results horizontally instead of vertically? The result I would like to see on the report is:

C6N	C6U	C6Z	C7H      ...
2.3875	2.5000	2.6400	2.6400   ...
2.3200	2.4375	2.5775	2.6850   ...
-4.0000	-4.5000	-3.7500	-4.2500  ...
2.3300  2.4450  2.5850  2.6925   ...

I plan on parsing the symbols so they're easier to read, but first I need to get over this hurdle.

Thanks in advance.

[RobDog888]

Yes, your query or report source will be a pivot query.

[sevenhalo]

Ok, I found PivotTable View in the designer; is that the same thing?

[sevenhalo]

Ahh, ok (tables not the same thing ). I found this link:
http://www.blueclaw-db.com/accessque...ivot_query.htm

I'll try and work with it, see if I can figure it out. Thanks

[DKenny]

For what you are looking for you will need 4 seperate pivot queries with the following synthax.

TRANSFORM Sum(YourTable.High) AS High
SELECT 'High' AS Measure
FROM YourTable
GROUP BY 'High'
PIVOT YourTable.ThreeLetterCode

Then

TRANSFORM Sum(YourTable.Low) AS Low
SELECT 'Low' AS Measure
FROM YourTable
GROUP BY 'Low'
PIVOT YourTable.ThreeLetterCode

Etc.

Once you have created a query for each measure, you can then union those resluting queries together in a fifth query.

[sevenhalo]

Thanks a ton Kenny. I'll work with it this afternoon. That looks exactly like what I need.

[sevenhalo]

Ok, back to being a little lost. Here's the first query I ended up using:

TRANSFORM Sum(tblBoardPrices.curHigh) AS High
SELECT tblBoardPrices.curHigh
FROM tblBoardPrices
WHERE tblBoardPrices.strBoardSymbol LIKE "C*"
GROUP BY tblBoardPrices.curHigh
PIVOT tblBoardPrices.strBoardSymbol

Which produced:

curHigh	C6N	C6U	C6Z	C7H	C7K	C7N	C7Z	C8H	C8Z
$2.37	$2.37								
$2.48		$2.48							
$2.63			$2.63						
$2.73				$2.73					
$2.80					$2.80				
$2.88						$2.88			
$2.95							$2.95		
$3.02								$3.02	
$3.15									$3.15

Is this correct so far? When I make all of them and union, is the format going to be right?

I've never messed with pivots before, so I'm kinda in a paniced daze right now.
(Note: I'm not worried about the rounding right now, I can take care of that later.)

[DKenny]

No, youare using the values as lables which is why you are getting the step effect.
Change your code to

TRANSFORM Sum(tblBoardPrices.curHigh) AS High
SELECT 'High' As Measure 
FROM tblBoardPrices
WHERE tblBoardPrices.strBoardSymbol LIKE "C*"
GROUP BY 'High' As Measure
PIVOT tblBoardPrices.strBoardSymbol

I've just created a 'dummy' field called "Measure" to act as the row label. This should now give you a single row.

[DKenny]

Slight edit to the above

TRANSFORM Sum(tblBoardPrices.curHigh) AS High
SELECT 'High' As Measure 
FROM tblBoardPrices
WHERE tblBoardPrices.strBoardSymbol LIKE "C*"
GROUP BY 'High'
PIVOT tblBoardPrices.strBoardSymbol

[sevenhalo]

Ahh, ok. This works if I import the table into access. Unfortunately (and I shouldn't have left this out), I'm using ODBC connections to SQL. The table isn't local.

If I use the same code for my ODBC connection, I get a "GROUP BY must be in SELECT" error. Is there any other way around this rather then adding a 'dummy' field in SQL?

[RobDog888]

you can always link to your SQL tables in Access so you have a linked Access table which will for the most part, act like a local access table.

[sevenhalo]

It is linked (using an ODBC connection), unless you're reffering to something else?

For some reason (and I think it has to do with the dummy field), it doesn't work with an ODBC connection. If I import the table from SQL, it ran perfectly. They mentioned something (I think it's what they're talking about) about this in that link i posted:

Warning about upsize to SQL Server - Access pivot query is not support. There are workarounds which are similar to the functioning of the Histogram Example (#16).

Should I be looking into this histogram or is there something I'm missing? I'm deffinitely a little dense when it comes to access.

[RobDog888]

Yes, thats what I was referring to but how many records are in this table? Is it possible to import the records to Access to isolate it from SQL?

[sevenhalo]

I think that would work.

I'm just a little nervous that if they see the new layout, they're going to want the live form to look the same way as this report. If they do... Then I'm going to have to look into another solution because these numbers change every second or two. They're populated by a DTS package I have running and that thing never stops .

Thanks for all the help Rob and Kenny! Deffinitely got me through this.