Desperate help needed with cross-tabs & different tables

[EnigmaticMan]

I am a technology coordinator who doesn’t know much about Crystal Reports 10. We just bought a new Student Management program that has crystal reports. We purchased Crystal Reports 10, so that I can create custom reports using the pre-existing Access database from our Student Management program.

I need to make a monthly attendance report that shows the number of females and the number of males absent for the month. Also, it needs to show the number of males and the number of females enrolled for the start and end of the month. Lastly, it has to have the number of withdraws and the number of new enrollments.

I have two tables : table 1 Attendance, table 2 Demographics. The attendance has the following fields: Attendance code (which are A for full day, T for tardy, H for half day), ID_Number (Student ID which is linked to the Demographics table), Absence_Date, Sex, and Duration_Total (a number of either 1 for full day, 0.5 for half day, or 0).
The Demographics table has these fields: ID_Number (Student ID which is linked to the Attendance table), Sex, Entry_Date (the date the student was enrolled), and Exit_Date (the date the student withdrew).

I created a cross-tab that has a column with the following formula:
If {Attendance.Sex}="F" then "Female" else "Male".

The row is Attendance.Absence_Date.

The summarized field is the Sum of @GenderCount (formula:
If {Attendance.ID_Number} = {Demographics.ID_Number} and
{Attendance.Sex}="F"
Then ({Attendance.Duration_Total})
Else If {Attendance.ID_Number} = {Demographics.ID_Number} and
{Attendance.Sex}="M"
Then ({Attendance.Duration_Total})
Else
0
)


There are two Select Expert formulas:
{Attendance.Absence_Date} = {?DateRange} and
{Attendance.Attendance_Code} in "A" to "H"


I get a nice table showing the females males and totals absent for each school day for the given dates.

But, here is my problem. I tried to get a small report next to the cross-tab. I have to use the Demographics table because it has all students. I tried to create a formula that would see if the student’s enrollment date was before the first date in the date parameter and if the student had a withdraw date between the parameter dates, but the numbers never came out correctly. I cannot get the correct start and end total enrollments for either gender. I did a simple Count ({Demographics.Sex}), and the end number came up 109 when it should be 794. I know what it is doing it is still only counting from the Attendance table. In fact, the 109 is the number of students with all day, half day, and tardies for the month of August (only 7 school days in August).

So, how do I get the report to have the above cross-tab, that works, and create a small table that, using the Demographics table, will count the students at the beginning of the month and do a count for the end of the month?

Is it possible?


I hope this isn’t too confusing. Thanks for any useful help.

 

[rosemaryl]

Dear EnigmaticMan,

Sounds to me like you have an equal join between the attendance and demographics table.

If the attendance table only includes students who have absences then that is the issue.

Change the join from the Demographics table to the attendance table to a Left Outer Join. This will return all records in the Demographics table and any records for attendance.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762