Selecting records between two numbers 1

[report2000]

I would like to select the following records using the formula section of Crystal Reports. (Already have tables that I imported using the wizard feature - Don't have SQL statement)
(if ... Else Formula)
if table1.numberOfStudents between 1-100
then
{@SchoolNamesWithStudents1-100Students}
---------------------------------------
if table1.numberOfStudents between 101-200
then
{@SchoolNamesWithStudents1-100Students}

 

[synapsevampire]

What is the "formula section of Crystal Reports"?

Crystal doesn't import data, it isn't a database so it can't do that. You probably meant that you used the wizard to connect to some datasource.

Referencing formulas within formulas, and posting nothing technical will result in questions, not solutions.

Try posting:

Crystal version
Database/connectivity used
Example data
Expected output

From what I can tell, your formula is fine if that field already has the number of students in it, however since each are providing the same result ({@SchoolNamesWithStudents1-100Students}), it seems redundant.

-k

 

[report2000]

Ok.

Crystal version - 10
Database/connectivity used ODBC (2 Access tables)

Example data:
StudentTable - School Population(students), schoolID
SchoolTable - SchoolID, SchoolName, Number of Schools (calculated field - Distinct count of schools)

Expected output - 3 schools have 1 to 100 students
1 Schools have 101-200 students
5 schools have 201-300 students

 

[synapsevampire]

In general, I would suggest creating a Query in MS Access to do this rather than doing it in Crystal.

In CR:

Group on the School.

Create formulas containing:

If {table.schoolpopulation} < 101 then
1
else
0

If {table.schoolpopulation} > 100
and
{table.schoolpopulation} < 201 then
1
else
0

If {table.schoolpopulation} > 200
and
{table.schoolpopulation} < 301 then
1
else
0

etc. as required

Now you can right click any of these formulas in the details and select insert->summary->sum, and use them however you like in other formulas.

The only table required is the studenttable given the output you require, assuming that the schoolids are listed only once (if not, you can use a Database->Select Distinct Records).

-k

 

[report2000]

Thank you.
I have one more question.
How I am going to group them in my report?(after I did the calculations)

I would like to group By:
1 to 100 students
101-200 students
201-300 students

I will then sum all the 1's mentioned earlier in this posting for each group.

 

[MrBillSC]

After you have created the 3 formulas above suggested by synapsevampire create a new formula to group on:

If {table.schoolpopulation} < 101 then
1 else
If {table.schoolpopulation} < 201 then
2 else
If {table.schoolpopulation} < 301 then
3

Group on the above formula.
MrBill