cross tab queries with sql server

E

eddiec

Urghhh!

Yes I have just spent all day trying to do the following:

does anyone know of a relatively straightforward way of creating a report in
an access project that runs off a sql server backend where the record source
is a cross tab query AND WHERE the selection critieria for the report can be
changed by the user?

TIA

eddiec :)
 
G

G.C.Mandrake

Well you can check out the RAC utility for S2k.
It's similar to Access crosstab but much more
powerful with many features/options.It ovecomes
all of the Access crosstab limitations,ie:
multipleTRANSFORM expressions,column totals,etc,etc:).
Since RAC is a system of server stored procedures
and functions you can embed a RAC execute in your own sp
and call it with parameters.You can easily embed
the parameters in RAC as it supports macro substitution
directly in its own parameters (no need to form strings
in variables for the RAC parameters).

www.rac4sql.net
 
M

MGFoster

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

If the columns are always going to be the same you can use CASE
statements. E.g. of quarterly report w/ sales summary under columns
named Q1, Q2, Q3, Q4:

SELECT C.ClientName,

SUM( CASE WHEN DatePart(Month,S.SalesDate) BETWEEN 1 and 3
THEN S.Sales ELSE 0 END ) As Q1
SUM( CASE WHEN DatePart(Month,S.SalesDate) BETWEEN 4 and 6
THEN S.Sales ELSE 0 END ) As Q2
SUM( CASE WHEN DatePart(Month,S.SalesDate) BETWEEN 7 and 9
THEN S.Sales ELSE 0 END ) As Q3
SUM( CASE WHEN DatePart(Month,S.SalesDate) BETWEEN 10 and 12
THEN S.Sales ELSE 0 END ) As Q4

FROM Sales As S INNER JOIN Clients As C ON
S.ClientID = C.ClientID

WHERE DatePart(Year, S.SalesDate) = 2003

GROUP BY C.ClientName

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQRPLTIechKqOuFEgEQLjeQCfbUV2b8N51NUWDL2CUdoClPskaL0An2kE
LIa1GicIHIhaycrtgkCgRJEn
=9zNl
-----END PGP SIGNATURE-----
 
E

eddiec

In my case I simply wanted the count of items with a specific defect:

I have modified your code slightly:

SELECT DamageTypeName,

SUM( CASE WHEN damagelevel = 0
THEN 1 ELSE 0 END ) As [0],

SUM( CASE WHEN damagelevel = 1
THEN 1 ELSE 0 END ) As [1],

SUM( CASE WHEN damagelevel = 2
THEN 1 ELSE 0 END ) As [2],

SUM( CASE WHEN damagelevel = 3
THEN 1 ELSE 0 END ) As [3],

SUM( CASE WHEN damagelevel = 4
THEN 1 ELSE 0 END ) As [4]

FROM


vwRpt_Damages_Detailed

where SupplierName = 'Acme Corp'
group by DamageTypeName

BEAUTIFUL!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Thank you

Eddie

G.C.Mandrake said:
Brilliant :)
Click to expand...
 
G

Gary Walter

Thanks for this MG.

I have been trying to think of a way
to mentally "slow down in steps" how
a crosstab works...and this was a giant
leap forward in my understanding.

Thanks,

Gary Walter
 
Top