Crosstab percentages

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.
nfp30Asked:
Who is Participating?
 
BelieverCommented:
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
 
paaskyCommented:
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
 
paaskyCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
nfp30Author Commented:
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
 
paaskyCommented:
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
 
nfp30Author Commented:
Paasky,

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

nfp30
0
 
BelieverCommented:
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
 
paaskyCommented:
nfp30,

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

Paasky
0
 
nfp30Author Commented:
Believer,

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

nfp30
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.