crosstab

[kimchavis]

Hello all:

Im new to Access and was hoping someone could help me with this. I need to do a % of column (as in an excel pivot table) in an Access Cross tab. Can this be done?

Thanks,
KIM

 

[GingerR]

Hi. Can you give more details? Do you just mean that you want to format data as a percentage?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[kimchavis]

Yes, I want to know what percentage this value is of the total. IN excel this is called % of column.

 

[GingerR]

I guess what I mean was could you give sample data, table structure, SQL statement etc. You'd get a better answer out of us if you provided more details.

Do you already have the TOTAL in the query?

Without knowing any more about what you are doing, try this and see if it's what you want:

In the crosstab query, make a new column. Bring down the field that is the same as your VALUE field. Change the GROUP BY to SUM and make it a row heading. That will be your TOTAL. You can rename the column by putting on the first line

Total: FieldName

with whatever field name that is that is the 'value'.

So now you have the total.

You can write another query based on this one that divides out the Value column by the Total.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[kimchavis]

IM sorry, I guess i didnt know what you were looking for.

I have a simple cross tab:
Q1 Q2 Q3 Total
3cmt 8 2 1 11
5cmt 2 2 3 7
7cmt 1 1 1 3


What I need it to look like is

Q1 Q2 Q3 Total
3cmt 73% 18% 9% 100%
5cmt 28.5% 28.5% 43% 100%
7cmt 33.3% 33.3% 33.3% 100%

It wont let me divide the field by the total in the designer.

I have one 'value' named "Loan #", and the total is named "Total Of Loan #: Loan #"

When I try a build and put in [Loan #] /[Total Of Loan #: Loan #] it gives me an error saying it does not recognize:
Total Of Loan #: Loan # as a field name.

I really appreciate all of your help.

 

[GingerR]

OK So, I'm guessing at field names. This is what I did:

Table Name: Table1
Field Name: Q
Field Name: CMT

Sample Data:

Q	CMT
1	3
1	3
1	3
1	3
1	3
1	3
1	3
1	3
2	3
2	3
3	3
1	5
1	5
2	5
2	5
3	5
3	5
3	5
1	7
2	7
3	7

Then I made a new query. Here is it's SQL code:

TRANSFORM Count(Table1.Q) AS CountOfQ
SELECT "CMT" & [CMT] AS Item, Count(Table1.Q) AS TotalQ
FROM Table1
GROUP BY "CMT" & [CMT]
PIVOT "Q" & [Q];

I named this query "Query2"

The results look like your first tabular output above, with numbers instead percentages.


Then I made a second query, with this code:

SELECT Query2.Item, Query2.TotalQ, Query2.Q1, Query2.Q2, Query2.Q3, Format([Q1]/[TotalQ],"0%") AS [Q1%], Format([Q2]/[TotalQ],"0%") AS [Q2%], Format([Q3]/[TotalQ],"0%") AS [Q3%]
FROM Query2;

This divides each item and makes it a percentage.

Hopefully you can fiddle around with this and create what you need.




Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html