Solved

Crosstab percentages

Posted on 2000-03-28
9
501 Views
Last Modified: 2008-03-04
Background: Visual Basic Front-end with Access Database.

I need to create a crosstab table that looks like the following:

Field  Resp1  Resp2 Resp3  Total
A      15     30    5      50
       30%    60%   10%    100%
B      10     20    20     50
       20%    40%   40%    100%
C      5      3     2      10
       50%    30%   20%    100%

This is probably worth more points than I have assigned, but as a new member I don't have any more than this to offer.  I'm looking for the SQL statement or series of statements that I can execute from VB.  Thank you.
0
Comment
Question by:nfp30
  • 4
  • 3
  • 2
9 Comments
 
LVL 7

Accepted Solution

by:
Believer earned 50 total points
ID: 2665140
It would be helpful to see the format of your original data.  At a glance, you may need to create two separate crosstab queries and then join them.

Or, better yet, create a single crosstab query and calculate the percentages off the accumulated numbers returned by the crosstab.

Suggest you mess with the crosstab wizard in Access and maybe even a simple report to get the layout and calculations flowing.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2665198
Hello nfp30,

Here's my suggestion. First make a query which calculates the percentage values.

Qry_Calculate_Percentage:
SELECT Field,
              Value,
              Resp,
              Value/DSum("Value","SomeTable","Field='" & Field & "'")*100 AS Percentage
FROM SomeTable;

This will give you the resultset like this:

Field      Value      Resp      Percentage
A      2      Resp1      1,78571428571429
A      22      Resp2      19,6428571428571
A      55      Resp3      49,1071428571429
A      33      Resp4      29,4642857142857
B      22      Resp1      25
B      33      Resp2      37,5
B      22      Resp3      25
B      11      Resp4      12,5
C      222      Resp1      35,126582278481
C      333      Resp2      52,6898734177215
C      44      Resp3      6,9620253164557
C      33      Resp4      5,22151898734177

Then make a crosstable query - this is better do in SQL View mode:

TRANSFORM CStr(SUM(Value)) & Chr(13) & Chr(10) & CStr(CInt(SUM(Percentage))) & "%" AS Amount
SELECT Field, CStr(SUM(Value)) & Chr(13) & Chr(10) & CStr(CInt(SUM(Percentage))) & "%" AS Total
FROM Qry_Calculate_Percentage
GROUP BY Field
PIVOT Resp;

Got this result (this might be really messed up after copy-pasting, but it looks fine in datasheet mode):

Field      Resp1      Resp2      Resp3      Resp4      Total
A      "2
2%"      "22
20%"      "55
49%"      "33
29%"      "112
100%"
B      "22
25%"      "33
38%"      "22
25%"      "11
12%"      "88
100%"
C      "222
35%"      "333
53%"      "44
7%"      "33
5%"      "632
100%"

Note that if you want the data to seen in two rows, you have to use Chr(13) & Chr(10) to make linefeed.

Regards,
paasky
0
 
LVL 10

Expert Comment

by:paasky
ID: 2665205
You could made this all in one query, but I splitted it into two queries to make it look simpler.

Here's an example how to put all-in-one:

TRANSFORM CStr(SUM(Value)) & Chr(13) & Chr(10) & CStr(CInt(SUM(Value/DSum("Value","SomeTable","Field='" & Field & "'")*100))) & "%" AS Amount
SELECT Field, CStr(SUM(Value)) & Chr(13) & Chr(10) & CStr(CInt(SUM(Value/DSum("Value","SomeTable","Field='" & Field & "'")*100))) & "%" AS Total
FROM Qry_Calculate_Percentage
GROUP BY Field
PIVOT Resp;

Paasky
0
 

Author Comment

by:nfp30
ID: 2673337
Paasky,

I apologize for taking so long to respond.  I think that your solution may work, however, I receive an error when I try to apply it.  It says I can't do a subquery in a crosstab query.

My setup is a little different than the one you proposed.  Instead of using the sum function, mine uses the count function.

Below is an sql that gets the percentages (but the counts are in a different query).  How can I modify this to include your suggestion, without getting the subquery error?  Thanks.

Transform Count(c27)/[Row Count] as [The Value]
     Select c28,Count(c27) AS [Row Count]
     from data1
     group by c28
     order by c28
     pivot c27


nfp30
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 10

Expert Comment

by:paasky
ID: 2673509
I changed my source query like this and my crosstab is now showing counts and percents:

SELECT Field, COUNT(*) AS [Value], Resp, COUNT(*)/DCount("*","SomeTable","Field='" & Field & "'")*100 AS Percentage
FROM SomeTable
GROUP BY Field, Resp;

1st crosstable version output (didn't changed anything):

Field      Resp1      Resp2      Resp3      Resp4      Total
A      "1 25%"      "1 25%"      "1 25%"      "1 25%"      "4 100%"
B      "2 29%"      "2 29%"      "2 29%"      "1 14%"      "7 100%"
C      "1 17%"      "1 17%"      "2 33%"      "2 33%"      "6 100%"

If you're doing this with VB - not Access - and can't make this work I think it would be wise thing to solve this with little code like Believer suggested.

Paasky
0
 

Author Comment

by:nfp30
ID: 2673694
Paasky,

Thanks for the suggestions.  I can't seem to make it work with VB.  I'll find another way.

nfp30
0
 
LVL 7

Expert Comment

by:Believer
ID: 2673809
Just curious... since paasky pointed you back to my suggestion, will that happen to work?  (I don't think there's any subqueries involved.)
0
 
LVL 10

Expert Comment

by:paasky
ID: 2673856
nfp30,

I'm sorry I can't help with VB - the last version I have used was 3.0 - long time ago. :-(

Paasky
0
 

Author Comment

by:nfp30
ID: 2676685
Believer,

That will work.  I'll give you the points.  Also, paasky thanks for all the time you spent on this.

nfp30
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now