Transform, crosstabs via Visual Basic Code

  • Thread starter Thread starter richmarin
  • Start date Start date
R

richmarin

I am using VB 6 and Access 97.

I need create crosstabs on the fly. It will have the following job
criteria :

1.) I don't know going in how many columns headers the cross tab will
have.

2.) I need percentages along side the columns.

Example below.

Temp table

Temp.Client Temp.Rate Temp.Bucket
Goldman Sach $2 Bucket 2
Goldman Sach $1 Bucket 1
Lehman $1 Bucket 1

Crosstab will look

Client Bucket 1 Percent Bucket 2 Percent Total
Goldman $1 50% $2 50% $3
Lehman $1 100% $0 0% $1

How can I do the above via Visual Basic. I am using DAO.

...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can't easily put 2 columns in a crosstab query. Try using a report
to produce the percentage. Here's the crosstab query:

TRANSFORM Sum(Rate) As DaValue
SELECT Client, Sum(Rate) As Total
FROM Temp
GROUP BY Client
PIVOT Bucket;
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlC0boechKqOuFEgEQLxyACeL7jtyWcm+wdUd+h+r0Xr8AyUmJQAn2Vi
Z/z/mESl6XRJTMWffMT4eqic
=LRXH
-----END PGP SIGNATURE-----
 
Back
Top