Counting In A Report

[StacyStacy]

Help. I am doing a demographic report. I have to count how many males that are white, black, asian, etc.

Here's the code that did not work:

COUNT FOR(Demoact.sex="1" and demoact.white="1",1,0)

Would you help me write this correctly?
Thanks a million!
Stacy

 

[1oldfoxman]

Not sure that COUNT accepts paramters other than the filter conditions. Certainly

COUNT FOR Demoact.sex="1" .and. demoact.white="1"

will work. The additional parameters ',1,0' I have to believe are invalid on this command and would applied to a function, not a command.

hth
Dennis

 

[TheRambler]

Hi,

Use a SQL query to count, like:

SELECT sex, white, black, asian, count(*) ;
FROM demoact ;
GROUP BY sex, white, black, asian

 

[StacyStacy]

Thanks. I see the query would be great, but I am creating a report and I don't clearly understand how putting a query in a report, namely in the field that needs an answer would help. Please explain further. I have code setup in a program that will either give them a preview or a printed report that will also show the summary. This should be automated.

Thanks and so sorry for being a little confused.

 

[StacyStacy]

I tried the first suggestion and it did not work. Help .. ;\

 

[Stella740pl]

Stacy,

How you put a query in a report depends on how you organize your report and what is the record source for it. Don't you use a table or query as report data? What else is in your report besides these few numbers?

If the stats you need is the major info on your report, you can use TheRambler's query or a similar one as your main data source.

If the numbers that you are trying to count are not the major information on your report and you just need to insert them as separate variables, you still can use the query.
Say, like that (there are several other ways, which one is preferred, again, depends on the organization of your report):

SELECT SUM(IIF(sex="1" .and. white="1",1,0) AS whitemale, ;
SUM(IIF(sex="1" .and. black="1",1,0) AS blackmale, ;
SUM(IIF(sex="1" .and. asian="1",1,0) AS asianmale, ;
SUM(IIF(sex="2" .and. white="1",1,0) AS whitefem, ;
SUM(IIF(sex="2" .and. black="1",1,0) AS blackfem, ;
SUM(IIF(sex="2" .and. asian="1",1,0) AS asianfem ;
FROM demoact ;
INTO CURSOR stats

GO TOP
SCATTER MEMVAR

Now you can use memory variables m.whitemale, m.blackfem, etc. in your report fields.


What do you mean, the first suggestion didn't work?
Did you put the results into a variable or just inserted the COUNT statement into report field?
Try:

COUNT FOR Demoact.sex="1" .and. demoact.white="1" TO malewhite
COUNT FOR Demoact.sex="1" .and. demoact.black="1" TO maleblack

Then just use the variables in your report.
Though, if you will use separate COUNT for each single variable, I guess it may take longer than to use one SELECT for everything.

Stella.

 

[Stella740pl]

Stacy,

Sorry, there are not enough parenthesis in my SELECT statement. It should read like this:

SELECT SUM(IIF(sex="1" .and. white="1",1,0)) AS whitemale, ;
SUM(IIF(sex="1" .and. black="1",1,0)) AS blackmale, ;
SUM(IIF(sex="1" .and. asian="1",1,0)) AS asianmale, ;
SUM(IIF(sex="2" .and. white="1",1,0)) AS whitefem, ;
SUM(IIF(sex="2" .and. black="1",1,0)) AS blackfem, ;
SUM(IIF(sex="2" .and. asian="1",1,0)) AS asianfem ;
FROM demoact ;
INTO CURSOR stats

 

[1oldfoxman]

Sorry I didn't understand that you were using the ReportWriter. The COUNT FOR statement would indeed be useless there - assumed you were writing the report details from within a program.

Stella's suggestions are good - assign the individual counts to variables before calling the report using SQL query. It will be faster than individual COUNT FOR statements.

I don't have an old version of FoxPro in front of me - does the report writer not have the ability to create a GroupBy band, which you can break on Demoact.sex. Then set the Demoact.sex = "1" filter on the whole report. In the GroupBy band, the field values would be sum(white), sum(black), sum(asian).... etc. To create just summary numbers, eliminate the Detail report band.

This assumes your table demoact has fields named white, black, asian, etc and that your data looks something like:

Database : demoact
Fields : idno white black asian .......
Data : 101 1 0 0
102 1 0 0
103 0 1 0
104 0 0 1

 

[StacyStacy]

Thanks you guys! I did write the query and in my screen code, told it to run. In my report, I just added the fields based on the cursors I created.

I will keep the above for future reference!
Thanks, Stacy )

 

[TheRambler]

Good for you. Sorry I did not reply sooner, I was getting a message "Connection actively refused by the server", but fortunatelly you got other answers from Stella and 1oldfoxman.