Solved

Crosstab percentages

Posted on 2000-03-28
9
503 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
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.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views 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 Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

831 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