Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Crosstab percentages

Posted on 2000-03-28
9
Medium Priority
?
517 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 150 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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
 
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

916 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