Show what's not there! 2

[AyJayEL]

I want to create report that is a letter to a school where they have not sent someone on a course.

I have two tables Schools, Courses. How do I get it to show which schools haven't attended?

I also only want to produce a letter for particular types of schools. Learn something new every day *:->*
AyJayEl

 

[Naith]

Using a left outer join from Schools to Courses, select fields where school NOT IN courses.

 

[dwelch35]

Naith has the correct method for obtaining the blank values in the Courses table.

To elaborate on Naith's response, I'd create a report that generates a letter to all schools of the type you're interested in then supress the letter where "Not IsNull({Courses.Field})"

dw

 

[Naith]

That would work, but I was not thinking of bringing back all the excess records as you would have to do for a suppression.

Simply do a left join, and use IsNull({Course.Field}) in your selection criteria.

Naith

 

[AyJayEL]

Cheers, that certainly shows me where a school has not sent anyone to any courses but what I really need to show is which schools haven't sent anyone on a particular course.

Thanks for all your help so far chaps! Learn something new every day *:->*
AyJayEl

 

[lbass]

Try:

1-Use a left join from SchoolsTable -> CoursesTable

2-Create a course parameter {?CourseID}

3-Create the following formula {@CourseAttendance}:
if (isnull({CourseID}) or {CourseID} <> {?CourseID}) then &quot;No Attendance&quot; else
&quot;Attendance Confirmed&quot;

4-Add detail fields from School({SchoolID}, {SchoolName}, etc.) and {@CourseAttendance}

5-Add whatever selects you wish for Schools to select statement.

-LB

 

[AyJayEL]

I have swapped the tables around so that I start with Courses with an outer join to to Bases. Despite the outer link I an only getting schools that have attended the course. I am going madly and badly wrong somewhere. (It's Friday and I'm feeling thick). Learn something new every day *:->*
AyJayEl

 

[AyJayEL]

Whoops we must have been posting at the same time. Will try your other suggestions LB. Learn something new every day *:->*
AyJayEl

 

[lbass]

I think our submissions were almost simultaneous--did you try my method above?

-LB

 

[lbass]

On second thought, just use:

isnull({Course.CourseID}) or {Course.CourseID} <> {?CourseID}

in your select statement, where ?CourseID is based on the courses table.

-LB

 

[kenhamady]

You can't do this by looking at an individual records. This is going to require a group selection formula, because you are looking for a group of records that doesn't have a characteristic. Try this:

1) Link from Schools to Courses with an Outer Join.
(Don't add any selection criteria using fields from the Courses table)

2) Add a Group by School
3) Create a course parameter
4) Create a formula that says:

If {?CourseParameter} = {CourseField}
then 1
else 0

5) Create a subtotal that sums this formula for each school
6) Add a criteria in your select expert where this subtotal is equal to zero. Or use a selection formula like this:

Sum ( {@Formula},{SchoolGroupField} ) = 0 Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[lbass]

Ken-

Since AyJayEl referred to the Schools table, it was not clear that there would be more than one record per school per course, in which case my method works (I tested it). You are assuming that the Schools table includes individual student records, perhaps rightly, and then yes, a group select would be necessary. Otherwise, I think our approaches are in effect the same.

-LB

 

[kenhamady]

This comment:

&quot;that certainly shows me where a school has not sent anyone to any courses but what I really need to show is which schools haven't sent anyone on a particular course&quot;

Tells me that a school can send people to multiple courses, which means multipe records per school. You then have to look at the entire group for that school to confirm that none of the records for that school has the course in question. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[lbass]

To clarify further, I agree that a school could be represented at multiple courses. My point was that, depending on the overall purpose of the database, it could be set up to detect representation by a school or not for each course, without tracking individual student participation in the database. Thus, for each course, a school could be represented or not represented, resulting in one record per course or a null. This would result in a set of course records representing participation per school, e.g., School1 is represented in CourseA, CourseC, CourseF, from a possible set of Courses A to F. Using isnull({CourseID}) or {CourseID <> {?Course} would pull School1 three times if {?Course} = CourseB, so duplicates would have to be suppressed, but each instance is still true. I'm just defending my logic here--I agree it is likely that the database is not this simple, that it does probably look at student participation, and in that case, your method would be the appropriate one.

-LB

 

[kenhamady]

Let me tweak your scenario just a bit and see if this makes sense.

Say the school has 4 records: CourseA, CourseB, CourseC and CourseF. This school shouldn't show up at all because it has CourseB. But it will still show up three times, because 3 out of the 4 records qualify at the record level. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[lbass]

Ken and all,

My apologies for being so persistent though wrong--you are clearly right.

-LB

 

[AyJayEL]

Just when you thought it was safe to get back in the water! Still got some problems I'm afraid.

This is my group (BASE_NAME)

This is my formula {@Counting)(put into details)
if {?CourseID} = {EV_OCCURRS.COURSE_ID}
then 1
else 0

This is my selection
Sum ({@Counting}, {BASES.BASE_NAME}) = 0.00

I actually have 3 tables linked like this.

Bases (Schools) linked with an outer join to In_Places (Billing and invoicing details )which is also linked with an outer join to Ev_Occurrs (The courses).

So BASE_ID ol to CHARGE_ID (BASES to IN_PLACES)
COURSE_ID ol to COURSE_ID (IN_PLACES to EV_OCCURRS)

It seems to be only bringing out schools that have ever applied for ANY course so a school that has never attended a course does not come up in the report.

Any ideas my friends of the ether?

Learn something new every day *:->*
AyJayEl

 

[kenhamady]

Post both your complete record selection formula and your complete group selection formula Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[AyJayEL]

The only selection I have on this report at the moment Ken, is the following Group selection

Sum ({@Counting}, {BASES.BASE_NAME}) = 0.00

Andrea Learn something new every day *:->*
AyJayEl

 

[kenhamady]

What happens if you take this criteria out?
Do you get every school and every course?
Put the subtotal on the GF and see if any schools that attended other courses have a zero subtotal. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com