Solved

How to chart out results of a SQL Statement in VBscript

Posted on 2008-10-15
2
160 Views
Last Modified: 2010-04-21
I have a SQL Statement (thanks to a member here) that works quite nicely and returns the proper information I need but I do not know how to output it for users to see. I am using ASP and VBSCRIPT to create a webpage with a table. The table needs to contain the ifo from the SQL statement.

This SQL statement below returns the percentages of Invoices paid for each of our accounts by week (BDATE), when this is run I get a table with ACCO, PourcentageNet and BDATE for each account sorted by Account and BDATE, each account can have up to 52 BDATES (each BDATE is a weekending date).

I need to set up a table of 53 columns, column 1 is Account and column 2-53 are the BDATES.
Each row would have each account and the corresponding PercentValue for that BDATE as queried by the SQL below.

I don't know how to do this...

SELECT Payments.ACCO, Sum(IIf(((Payments.BSTA)="PAID"),Payments.Net,0))/Sum(Payments.Net) AS PourcentageNet, Payments.BDATE
FROM Payments
GROUP BY Payments.ACCO, Payments.BDATE
0
Comment
Question by:EGormly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 5

Accepted Solution

by:
PaulKeating earned 500 total points
ID: 22726010
What you want is a crosstab. SQL can't do crosstabs. A select statement can produce any number of rows from 0 up at runtime, but the number and identity and data source of the columns is fixed at parse time. You want the columns in the query to depend on what is in the data and that is something standard SQL just can't do.

There are nonstandard extensions to SQL that will do it, but that depends on the dialect of SQL. For example, if your underlying database is SQL 2005 you can use a pivot clause. But since you're using iif() I am guessing your underlying database is Access, so you're probably out of luck there.

But if I am right and it is Access, then you might be able to save the data from your query in a temporary table and get Access to do an Access pivot table using VB Script.

I'm not an Access expert: interactively you do it with View | Pivot Table View. You might want to repost this question as an Access problem.

Just don't expect to be able to do it in SQL.
0
 

Author Closing Comment

by:EGormly
ID: 31506368
thanks.. not what I wanted to hear.. but still :>
0

Featured Post

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

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

This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

695 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