[RBG7-L] - Re: Another Crosstab question

Kayza Kleinman Wed, 20 Oct 2004 13:36:44 -0700

I've thought of your approach. However, it's not just gender that I need to crosstab by, but other attributes in the master table, and those don't necessarily have a definite set of possible values (something along the lines of the zip code.)

Unless v 7.5 is due out SOON, I think I'll go with the approach outlined int helast conference (wasit you?)of creating images of what would be a sub report and using that.

David M. Blocker wrote:

Kayza

Ok, so I think what you're saying is you want these TWO crosstabs in ONE
report, one crosstabbing by gender, the other by Impairment. Which means
both must come off the same dataset.

A standard view won't work, yuou're right: combining the two tables to
include Gender with Impairment, you'll get these rows:

Child Table
CleintID Impairment Gender
Code
1           V            M
2           V            F
2           H            F
3           W           F
2          W            F
4           H            F
3           V            F
4           C            F
1           C            F

So a crosstab of Client by Gender would give false answers

    M   F
    1    7

instead of the correct

    M   F
    1    3

I think that a sub-report IS your best bet - wait for 7.5.  Until then, the
other choice is to

1. Base report on the view above
2. use crosstab to get your second crosstab by impairment
3. Create a view of totals by gender:

CReATE VIEW GenderTots (Gender, NumClient) as SELECT Gender, COUNT(*) FROM
MasterTable GROUP BY Gender

4. In the report, the gender crossta data is done by lookups to this table,
one for M and one for F!

David Blocker
[EMAIL PROTECTED]
781-784-1919
Fax: 781-784-1860
Cell: 339-206-0261
----- Original Message -----
From: "Kayza Kleinman" <[EMAIL PROTECTED]>
To: "RBG7-L Mailing List" <[EMAIL PROTECTED]>
Sent: Wednesday, October 20, 2004 10:34 AM
Subject: [RBG7-L] - Re: Another Crosstab question

This is obviously a sever subet of the data, but should give you an idea
of what I am trying to do:

Master Table
Client FullName Gender HousingType Ethnicity Address Telephone# ZipCode
1 Test Person M Rent B 1234 Test Drive 718 777-8888 11224
2 Another Person F Own W 456 Test Road 718 555-1414 11224
3 Test Client F Own A 125 Test St 718 555-7878 11225
4 Random Name F Lease W 555 Test Road 212 963-4587 11224


Child Table
CleintID Impairment
Code
1 V
2 V
2 H
3 W
2 W
4 H
3 V
4 C
1 C


Sample Report
M F
Count ClientID 2 2

V H W C
Count Clientid 2 2 3 2

David M. Blocker wrote:

Kayza
I'm sure this can be done with views combined, but I can't picture the layout of the report you want. Can you send a few sample rows from

Master

table, a few from Child and the layout of the report you want?

David Blocker
[EMAIL PROTECTED]
781-784-1919
Fax: 781-784-1860
Cell: 339-206-0261
----- Original Message -----
From: "Kayza Kleinman" <[EMAIL PROTECTED]>
To: "RBG7-L Mailing List" <[EMAIL PROTECTED]>
Sent: Wednesday, October 20, 2004 1:23 AM
Subject: [RBG7-L] - Another Crosstab question

As you can see, I'm happily exploring the capabilities of the crosstab
feature of the report generator. So, I have another question:

Say I have a table with clientID, category1, category2, category3. Now
each row is unique, but the combination of clientid and category1 may
not be. If I just want the count of clientid for each possible value of
category 1, that works. The question is, can I get a count of the number
of UNIQUE client ids for each possible value of category1.

What I actually need to accomplish is this:
I have a master table with clientid (unique), category1, category3.
There is a child table with clientid, category2. Neither column is
unique, but the combination is. I need one report that gives me a count
of the unique number ids of each possible value of category1, the same
for category3 and a count of the number of ids for category2. I can get
each one separately using the crosstab feature quite nicely. But, if I
want it altogether, I see no other way than a view, which will give me
multiple occurrences of clientid+category1 or clientid+category3
combinations. Perhaps I should try subreports?
--

Kayza Kleinman

Director, Nonprofit Helpdesk

Jewish Community Council of Greater Coney Island, Inc.

3001 West 37th Street

Brooklyn, NY 11224

Tel. 718 449-5000

Fax 718 946-8240

[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>

www.nphd.org <http://www.nonprofithelpdesk.org/>

“Putting Technology to Work For You”

--

Kayza Kleinman

Director, Nonprofit Helpdesk

Jewish Community Council of Greater Coney Island, Inc.

3001 West 37th Street

Brooklyn, NY 11224

Tel. 718 449-5000

Fax 718 946-8240

[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>

www.nphd.org <http://www.nonprofithelpdesk.org/>

“Putting Technology to Work For You”

--

Kayza Kleinman

Director, Nonprofit Helpdesk

Jewish Community Council of Greater Coney Island, Inc.

3001 West 37th Street

Brooklyn, NY 11224

Tel. 718 449-5000

Fax 718 946-8240

[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>

www.nphd.org <http://www.nonprofithelpdesk.org/>

“Putting Technology to Work For You”

[RBG7-L] - Re: Another Crosstab question

Reply via email to