Cross Tab Query...Help

[Guest]

I am trying to create a cross tab query with 2 value fields, but it seems
Access does not allow this. I need to count patient id field and sum
outstanding balance by quarter.

I am trying to create a report that will show number of patients and total
dollar amt for each quarter. I have 5 columns in my query, and I would like
to display my results
Region
Facility
PatientID
CurrentBal
Thrudate

Qtr 1
Qtr 2
Region Count Sum Count
Sum
Facility
Unit 25 5000
30 7500
Qtr 1
Qtr 2
Region Count Sum Count
Sum
Facility
Unit 15 4000
17 7000

In the cross tab query I set ThruDate filed and my column heading facilty
and Units are my row. I would like to do a count on PatientID and sum
currentbal, as my value fields, but I can only have 1value field.

How can I setup my crosstab query to give me both count and sum in one query?

 

[Guest]

Try this --
TRANSFORM Count([PatientID]) & " - " & Sum([CurrentBal]) AS Expr1
SELECT Natalie.Region, Natalie.Facility
FROM Natalie
GROUP BY Natalie.Region, Natalie.Facility
PIVOT "Qtr " & Format([Thrudate],"q") & " Patient Count and Sum of Balance";

 

[Guest]

Thanks so much for your help.
Forgive my daftness, but how can I do a sum by region on the Expr1 field.
Both my count and sum comes out in one field. So I do a substring???

Try this --
TRANSFORM Count([PatientID]) & " - " & Sum([CurrentBal]) AS Expr1
SELECT Natalie.Region, Natalie.Facility
FROM Natalie
GROUP BY Natalie.Region, Natalie.Facility
PIVOT "Qtr " & Format([Thrudate],"q") & " Patient Count and Sum of Balance";

--
KARL DEWEY
Build a little - Test a little

I am trying to create a cross tab query with 2 value fields, but it seems
Access does not allow this. I need to count patient id field and sum
outstanding balance by quarter.

I am trying to create a report that will show number of patients and total
dollar amt for each quarter. I have 5 columns in my query, and I would like
to display my results
Region
Facility
PatientID
CurrentBal
Thrudate

Qtr 1
Qtr 2
Region Count Sum Count
Sum
Facility
Unit 25 5000
30 7500
Qtr 1
Qtr 2
Region Count Sum Count
Sum
Facility
Unit 15 4000
17 7000

In the cross tab query I set ThruDate filed and my column heading facilty
and Units are my row. I would like to do a count on PatientID and sum
currentbal, as my value fields, but I can only have 1value field.

How can I setup my crosstab query to give me both count and sum in one query?

 

[Guest]

You said "I would like to do a count on PatientID and sum currentbal, as my
value fields" and I gave you a crosstab query that did that.
What is the query not doing that you want?
--
KARL DEWEY
Build a little - Test a little

Thanks so much for your help.
Forgive my daftness, but how can I do a sum by region on the Expr1 field.
Both my count and sum comes out in one field. So I do a substring???

Try this --
TRANSFORM Count([PatientID]) & " - " & Sum([CurrentBal]) AS Expr1
SELECT Natalie.Region, Natalie.Facility
FROM Natalie
GROUP BY Natalie.Region, Natalie.Facility
PIVOT "Qtr " & Format([Thrudate],"q") & " Patient Count and Sum of Balance";

--
KARL DEWEY
Build a little - Test a little

I am trying to create a cross tab query with 2 value fields, but it seems
Access does not allow this. I need to count patient id field and sum
outstanding balance by quarter.

I am trying to create a report that will show number of patients and total
dollar amt for each quarter. I have 5 columns in my query, and I would like
to display my results
Region
Facility
PatientID
CurrentBal
Thrudate

Qtr 1
Qtr 2
Region Count Sum Count
Sum
Facility
Unit 25 5000
30 7500
Qtr 1
Qtr 2
Region Count Sum Count
Sum
Facility
Unit 15 4000
17 7000

In the cross tab query I set ThruDate filed and my column heading facilty
and Units are my row. I would like to do a count on PatientID and sum
currentbal, as my value fields, but I can only have 1value field.

How can I setup my crosstab query to give me both count and sum in one query?

 

[Gary Walter]

:

I am trying to create a cross tab query with 2 value fields, but it seems
Access does not allow this. I need to count patient id field and sum
outstanding balance by quarter.

I am trying to create a report that will show number of patients and total
dollar amt for each quarter. I have 5 columns in my query, and I would like
to display my results


Region
Facility
PatientID
CurrentBal
Thrudate

Qtr 1 Qtr 2
Region Count Sum Count Sum
Facility
Unit 25 5000 30 7500

Qtr 1 Qtr 2
Region Count Sum Count Sum
Facility
Unit 15 4000 17 7000

In the cross tab query I set ThruDate filed and my column heading facilty
and Units are my row. I would like to do a count on PatientID and sum
currentbal, as my value fields, but I can only have 1value field.

How can I setup my crosstab query to give me both count and sum in one
query?

---- possible solution ----

In addition to Karl's sage advice, another method
to get 2 values for each pivot column is to use the
xtab method that Duane has referenced earlier

http://www.tek-tips.com/faqs.cfm?fid=4524.

1) Create a table "tblXtabColumns" with one text field "FldName"

Enter 2 records for "FldName"

PatCnt
CurBal

then add this table to your query w/o any joining
(Cartesian join). When we add [FldName] to PIVOT
clause, two strings will be created for each original
PIVOT column.

We then use IIF to sort out which we have, i.e.,
PIVOT string might be

PatCntQ1 or CurBalQ1

to determine which aggregate to use, i.e.,

if its "PatCntQ1" where [FldName]="PatCnt," we want Count([PatientID])

if its "CurBalQ1" where [FldName]="CurBal," we want Sum([CurrentBal])

==>

IIF([FldName]='PatCnt',Count([PatientID]),Sum([CurrentBal]))

I did not test this, but I believe you will also need to wrap
this IIF in an additional (meaningless) aggregate (say First)
in order to use in TRANSFORM clause (I could be wrong
that you need this).

Something like:

TRANSFORM
First(IIF([FldName]='PatCnt',Count([PatientID]),Sum([CurrentBal])))
SELECT
Natalie.Region,
Natalie.Facility
FROM tblXtabColumns, Natalie
GROUP BY
Natalie.Region,
Natalie.Facility
PIVOT [FldName] & "Q" & Format([Thrudate],"q")
IN
('PatCntQ1',
'CurBalQ1'
'PatCntQ2',
'CurBalQ2');

I added the IN clause so your report will always
get the exact same 4 fields for each record...
even if say Q1 data did not exist over your data.

If that is possible, you could also wrap entire
Transform clause in NZ(....., 0)

good luck,

gary