Solved

Crosstab percentages

Posted on 2000-03-28
9
500 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

743 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

10 Experts available now in Live!

Get 1:1 Help Now