Creating a Pivot table or Crosstab query with specific data.

[Andy]

Hi, I am using Access 2003 on a XP machine.

I have a table with 73,000 records with 4 columns: ID (data type =
Autonumber), BIXS_Row_Code (data type = text), BIXS_Column_Code (data type =
text), Coefficient (data type = number).

Below is an example of the the data in the 73,000 records. I would like to
have the coefficients, per BIXS_Row_Code, totaled, and then I want only the
BICS_Row_CODE, BICS_Column_Code, and coefficients that total 50% or greater
returned into my dataset to then be used in a crosstab query / pivot table.

Thanks.

Andrew

Begginning data:
BICS_Row_Code BICS_Column_Code Coefficient
1126AX3 3191 .02347
1126AX3 3251 .01687
1126AX3 3250 .2457
1126AX3 3250 .58344
1126AX3 3254 .13052
1584FE7 111B .26951
1584FE7 1112 .35749
1584FE7 1119 .2373
1584FE7 1100 .1357

Resulting Data:
BICS_Row_Code BICS_Column_Code Coefficient
1126AX3 3250 0.58344
1584FE7 1112 0.35749
1584FE7 111B 0.26951

Final Pivot Table/Crosstab Query:
BICS_Column_Code 3250 1112 111B
BICS_Row_Code
1126AX3 0.58344 0 0
1584FE7 0 0.35749 0
1584FE7 0 0 0.26951

 

[David S via AccessMonster.com]

Hello Andrew,

I'm using Access 2000, but the steps should be the same for Access 2003.
Really, you just need to follow the steps as you've described them:

I would like to have the coefficients, per BIXS_Row_Code, totaled

CoefficientPerRow:
SELECT BIXS_Table.BIXS_Row_Code, Sum(BIXS_Table.Coefficient) AS
SumOfCoefficient
FROM BIXS_Table
GROUP BY BIXS_Table.BIXS_Row_Code;

and then I want only the BICS_Row_CODE, BICS_Column_Code, and coefficients
that total 50% or greater returned into my dataset

The 50% criteria would be most effective added to the previous query, as it
would reduce the number of records we have to subsequently link to:
CoefficientPerRow:
SELECT BIXS_Table.BIXS_Row_Code, Sum(BIXS_Table.Coefficient) AS
SumOfCoefficient
FROM BIXS_Table
GROUP BY BIXS_Table.BIXS_Row_Code
HAVING (((Sum(BIXS_Table.Coefficient))>0.5));

Then we join this to our main table to get BIXS_Query:
SELECT BIXS_Table.ID, BIXS_Table.BIXS_Row_Code, BIXS_Table.BIXS_Column_Code,
BIXS_Table.Coefficient
FROM CoefficientPerRow INNER JOIN BIXS_Table ON CoefficientPerRow.
BIXS_Row_Code = BIXS_Table.BIXS_Row_Code;

And then we can use that in the pivot query - I always use the wizard for
this, but here's the SQL for BIXS_Query_Crosstab anyway:
TRANSFORM Sum([Coefficient])
SELECT [BIXS_Row_Code], Sum([Coefficient]) AS [Total Of Coefficient]
FROM BIXS_Query
GROUP BY [BIXS_Row_Code]
PIVOT [BIXS_Column_Code];





to then be used in a crosstab query / pivot table.