What is the name for a Side-by-Side query based on column value?

[Rx_]

Is Pivot the name for a Side-by-Side query based on column value?

Is there a name and/or example for the type of data transformation to create a side-by-side report or query based on a field value?

I was able to build some custom reports by using multiple queries. But, there may to be a better way to accomplish this task.
The problem is that a Cross-Tab query uses "totals" or "sums" for the data detail. I don't want totals, I want the data details.

The data transformation needed for many reports is more of a Side-By-Side data detail based on a type value found in a field.

While the result can be accomplished using about five queries in series, my worry is that everyting looks like a nail when all I have is a hammer.

Example of data:
Type can be Reg (regular), OT (overtime), WK (weekend or vacation)
On one day, an employee can have 5 hr of regular and 3 hr of OT and 1 WK
Mood is just some number between 1-10 (just to add details that cant be "aggrivated" .. or summed up) as is the "Starttime". And, there can be a number of hours for any one of three, any two of three, none, or all three Types.

Table Headers: ID,...EmpName,...Type...Hrs...Date...StartTime...Mood


The data transformed output of the data should not have totals.
It needs a grouping, then repeating details columns for each Reg, OT, WK. The sorting is by ID, empName, and Date. The side-by-side data is next.

Data Transformation: ID,...EmpName, ...Date...Reg...Hrs...Starttime...Mood...OT...Hrs...Starttime...Mood...WK...Hrs...Starttime...Mood

The transformation needs to key in on the Type and create the side-by-side columns with details.

This can be accomplished by creating a query for each TYPE and then joining the three together based on the ID & Date. That is about 4 queries. I have also written VBA code to accomplish the same thing. Both require time and maintenance. If anyone has a better mousetrap, please share.

Did some searching - and suspect Pivot is what is needed.
Any good (or great) tutorials on Pivots for Access?

Col 1                 Col2        DATA
----------------------------------
Vista                 2005        4
Distinguish         2005        5
ColonialVoice        2006      6
Vista                 2006        7
Vista                 2007        8
SuperiorCard         2007      7
Distinguish         2007        6
 
I would like to get the output in the following format using PIVOT function
 
 
2005                 2006                 2007
---------------------------------------------------------
Vista                 ColonialVoice       Vista
Distinguish          Vista                 SuperiorCard
                                              Distinguish
 
or 
 
[FONT=Calibri][FONT=Calibri][COLOR=#000000]col1             2005 2006 2007[/COLOR][/FONT]
[FONT=Calibri][COLOR=#000000]ColonialVoice  6 [/COLOR][/FONT]
[FONT=Calibri][COLOR=#000000]Distinguish     5       6[/COLOR][/FONT]
[FONT=Calibri][COLOR=#000000]SuperiorCard   7[/COLOR][/FONT]
[FONT=Calibri][COLOR=#000000]Vista                4        7      8[/COLOR][/FONT]
[/FONT]

 

[Rx_]

TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]

It doesn't appear that Access 2007 has a Crosstab Wizard for this.
What a bonus that would be for Access 2010.

Table Name: Rx_testPivot - see text table above for data you can paste into a table.

TRANSFORM First(Rx_testPivot.data) AS FirstOfdata
SELECT Rx_testPivot.col1
FROM Rx_testPivot
GROUP BY Rx_testPivot.col1
PIVOT Rx_testPivot.col2;

The SELECT Rx_testPivot.col1 can be replaced with another complex select statement. My search thus far has not found any tutorals or example sites.