Cross tab query with counts

  • Thread starter Thread starter Heather
  • Start date Start date
H

Heather

Good evening all:

I wanted to see if you could help me with the following
issue:

I have a tablet that has individual member IDs, as well
as claims counts and claim dates.

I want to creat a query that has Member ID as my row and
my columns to be the following
1. Claim Count for month a
2. Claim Count for month b. etc.

My problem is that I want to use multiple months which
are not quantified in any particular order. Does anyone
have any other solutions that maybe I've missed?
 
Heather said:
Good evening all:

I wanted to see if you could help me with the following
issue:

I have a tablet that has individual member IDs, as well
as claims counts and claim dates.

I want to creat a query that has Member ID as my row and
my columns to be the following
1. Claim Count for month a
2. Claim Count for month b. etc.

My problem is that I want to use multiple months which
are not quantified in any particular order. Does anyone
have any other solutions that maybe I've missed?
Click to expand...

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


You can use the PIVOT statement at the end of the query to get the
months (and years) columns. E.g.:

PARAMETERS [Begin Date?] Date, [End Date?] Date;
TRANSFORM Count(Claim) As DaValue
SELECT MemberID, Count(*) As Total
FROM Claims
WHERE ClaimDate Between [Begin Date?] And [End Date?]
GROUP BY MemberID
PIVOT Format(ClaimDate, "YYYY/MM")

The PIVOT statement creates the "DaValue" column names. Since the
criteria can cross year boundaries it is necessary to show both the year
and the month as the column names. Since the column names are sorted
ascending (placed left to right) putting the year before the month will
sort the dates correctly.

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

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

iQA/AwUBQHwT/4echKqOuFEgEQLHIQCg943wVl+WxShtwIizNYQD9xzaFtIAoPov
UVZt6snk8zJ8ezSGexXgMGe4
=biZD
-----END PGP SIGNATURE-----
 
Back
Top