# txNotes

Notes:
Basic CrossTab query
Cross tab on student table- one row and one colum. You can do this with the Wizard


Notes:
Creating a CrossTab that appears to have "multiple" Pivot columns
This is done by concatenating table fields into a single Pivot column.


Notes:
Creating a CrossTab that appears to have "multiple"Values displayed
This is done by concatenating expressin that calcualte the aggregates  into a single "value" expression.
You need to make the rows taller to see the seond value
Chr(13) & chr(10) produces a "new line" 

You can use a similar technique on the column headers- which is preserved if you copy the result into an Excel spreadsheet.


Notes:
Alternate way to do a CrossTab.
The Transform and Pivot are not standrad SQL keywords.
This is a work-around that crreates a correlated subquery for each column that would have been a pivot column. The initial table has to be read many time to do this query.


Notes:
Alternate way to do a CrossTab.
This query goes through the tables on time only
the expression:     StGender = "F"   has a True or False value
True also has a numeric value -1 and False the numeric value 0
The function ABS returns the positive value of any number; so ABS(StGender ="F")
returns either 1 for True  or 0 for False
If you add up all of the values returned, you will add in a 1 each time the original test StGender = "F" was True- this gives you the number of Females.

Based on a query described by David Rozenshtein and Anatoly Abramovich


Notes:
There are two backup tables bkSchoolData and bkStudents


