Crosstab help

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

Hiya, this thing never seems to show up on screen -
annoying!

Anyway, this is my problem..

I generate a grouped select query as follows:

refkey referral clinic admitted follow-up
6526 accepted accepted
9745 accepted accepted accepted accepted
10760 accepted closed
10764 accepted accepted accepted
10786 accepted open
etc...

What I need from this is:

Type Accepted Closed Open
Referral 4739 592 116
Clinic 2710 1705 324
Admitted 2285
Follow-Up 1025

This is needed for a chart, but I can't seem to do it.
Could be the other way round (swap x and y axes so to
speak).

Any ideas?? This is what I have managed so far (group
select query):

Referral Clinic Admitted Follow-Up CountofRefkey
Accepted Accepted 425
Accepted Accepted Accepted 1257
Accepted Accepted Accepted Accepted 1028
Accepted Closed 1725
Accepted Open 304
Closed 592
Open 116

This isn't suitable for my graph. Any help would really be
appreciated, including if I am wasting my time.

Thanks loads,

Karen
 
Hi,


You have to normalize the whole stuff, first, since field NAME is not DATA. Field name has to be
put "as" data:

SELECT refkey, 'referral' As State, referral As CaseOf
FROM myTable WHERE Not (referral Is NULL)

UNION ALL

SELECT refkey, 'clinic', clinic
FROM myTable WHERE Not (clinic Is NULL)

UNION ALL

SELECT refkey, 'admitted' , admitted
FROM myTable WHERE Not (admitted Is NULL)

UNION ALL

SELECT refkey, 'follow-up', [follow-up]
FROM myTable WHERE Not ([follow-up] Is NULL)



Save it as q1.


Next, you can de-normalized it again, with a crosstab on q1 (the procedure should be now evident,
using the wizard, GroupBy CaseOf, Pivot on State, aggregate with LAST on refkey ).



Hoping it may help,
Vanderghast, Access MVP
 
Karen,

Your example is ambiguous and what you want does not correlate with
the data you presented.

Your example shows 6 records where you have a Referral type and
Accepted as status, but your output only shows one row/column with
this combination of values. Which value of refKey would you put in
this cell?

--

Dale Fye


Hiya, this thing never seems to show up on screen -
annoying!

Anyway, this is my problem..

I generate a grouped select query as follows:

refkey referral clinic admitted follow-up
6526 accepted accepted
9745 accepted accepted accepted accepted
10760 accepted closed
10764 accepted accepted accepted
10786 accepted open
etc...

What I need from this is:

Type Accepted Closed Open
Referral 4739 592 116
Clinic 2710 1705 324
Admitted 2285
Follow-Up 1025

This is needed for a chart, but I can't seem to do it.
Could be the other way round (swap x and y axes so to
speak).

Any ideas?? This is what I have managed so far (group
select query):

Referral Clinic Admitted Follow-Up CountofRefkey
Accepted Accepted 425
Accepted Accepted Accepted 1257
Accepted Accepted Accepted Accepted 1028
Accepted Closed 1725
Accepted Open 304
Closed 592
Open 116

This isn't suitable for my graph. Any help would really be
appreciated, including if I am wasting my time.

Thanks loads,

Karen
 
You alright Dale?

Mate, I wish I could see the response from Duane
yesterday - but I can't see the post anywhere! I put it on
the queries bit - but I can't find it. Thanks to Duane,
and sorry for wasting time - I didn't intend to.

As for Michel (sorry for mis-spelling name earlier) - what
you suggested worked 100% perfectly - as I said, I really
do appreciate all your time and help.

Dale, no need to get annoyed - some of us do actually slog
our guts out for days before putting things on the board,
and do appreciate the hard and valuable work done by all
the peeps out there.

Thanks again Michel,

Baz

-----Original Message-----
You know Baz, Duane answered this question last night, with the exact
same response that Michel gave. Did you even bother to check to see
whether anyone had answered that post?
--
HTH

Dale Fye


Yeah, it's Baz... I thought I'd been banned for asking too
many questions so I lied... got a pretty damn quick
response being a girl too (I'll remember that!).

Michael, thank you very much for your response... I don't
understand it yet but you have opened a new avenue of
possibility that I will try to learn. Thanks for the
time, patience and help.

Basil
-----Original Message-----
Hi,


You have to normalize the whole stuff, first, since field
Click to expand...
NAME is not DATA. Field name has to be
put "as" data:

SELECT refkey, 'referral' As State, referral As CaseOf
FROM myTable WHERE Not (referral Is NULL)

UNION ALL

SELECT refkey, 'clinic', clinic
FROM myTable WHERE Not (clinic Is NULL)

UNION ALL

SELECT refkey, 'admitted' , admitted
FROM myTable WHERE Not (admitted Is NULL)

UNION ALL

SELECT refkey, 'follow-up', [follow-up]
FROM myTable WHERE Not ([follow-up] Is NULL)



Save it as q1.


Next, you can de-normalized it again, with a crosstab on
Click to expand...
q1 (the procedure should be now evident,
using the wizard, GroupBy CaseOf, Pivot on State,
Click to expand...
aggregate with LAST on refkey ).
Hoping it may help,
Vanderghast, Access MVP





.
Click to expand...


.
Click to expand...
 
Back
Top