Calculation Help Needed

[CobyUSA]

I need data from aonther table that if the course number is 2903 then, I need the word "Completed" to be filled into a field. I tried this:

Case(

HCOURSE1::CRSNUM = "2903"; "Completed";

"")

It worked but I tried to use the calculation for other fields and nothing happens. I tried If instead of case but nothing happens.

Thank you for your time.

[Lee Smith]

Hi CobyUSA, and welcome to the Forum.

The Case Statement, and If Statement, should provide the same results, however they are different to write. IMO, the Case is easier to Read, and Write.

The results that you want are not clear.

use the calculation for other fields and nothing happens

Can you provide a little more information on what is not working.

Lee

Edited August 3, 2008 by Guest

[CobyUSA]

Hi,

There are several fields that will need to use the same calculation but, different course codes in the formula. The result will be the same for all the fields ("Completed"). As students complete courses, I need the fields to read completed. So I need a calcualtion for all the courses based on their course number and the result to be "Completed". I used the same calcuation as I posted aboove but the data in the fields is not being record...nothing is happening.

[aldipalo]

When you say different fields can you be more specific or show us what you tried to do? Are you looking to enter this data in multiple fields or multiple records? In other words the field is

HCOURSE1::CRSNUM =, but, the data is different for each student or

HCOURSE1::CRSNUM2 =

HCOURSE1::CRSNUM3 =

where you are looking to enter this calculation in many different fields for the same student or the same field for many different students?

How are you writing it?

Case(

HCOURSE1::CRSNUM = "2903"; "Completed";

HCOURSE1::CRSNUM = "2901"; "Completed";

HCOURSE1::CRSNUM = "2906"; "Completed";

"")

OR

Case(

HCOURSE1::CRSNUM = "2901"; "Completed";

HCOURSE1::CRSNUM2 = "2901"; "Completed";

HCOURSE1::CRSNUM3 = "2901"; "Completed";

"")

Please explain further.

[Lee Smith]

Why not attach a copy of your file so that we can check the relationships, and names fields, etc., and maybe understand better what you are trying to accomplish.

Lee

[Lee Smith]

Hi Al,

You may be right in your response, but a peek at the file will nail it down.

Lee

Edited August 3, 2008 by Guest

[aldipalo]

Hi Lee:

I agree, that would be the simplest approach.

[CobyUSA]

I will work on sending a file without the data.

Until then, there are about 1200 students that are taking classes over a period of four years. As they complete a course, I need the field for each course they complete to read "Completed". There are dozens of courses and each one has a seperate field with a seperate calculation. The course number makes them unique to the other fields. The data is being pulled from a table that I have related to the table I am working with. As of now, we have to type in each field (course's field) when a student completes a course. Thank you so much for your time!

[comment]

This is not a calculation problem, but a data structure problem.

There are dozens of courses and each one has a seperate field with a seperate calculation.

You need a separate RECORD for each student/course combination (IOW, enrollment). That means three tables (at minimum), of Students, Courses and Enrollments.

In the Enrollment table, you record the StudentID, the CourseID, and also the fact that the student has completed the course (preferably as a date of completion). Grade would also be written to this table.

Alternatively, if a student completes a course automatically, you can write the completion date to the Courses table. Then all the Enrollments records "know" they are completed, because they can see this in their Course record.

[CobyUSA]

I am not writing it like either way(CRSNUM or CRSNUM2) beacuse each calculation is needed in the seperate fields so, I have a calculation listed one per field not several like below in a field.

Is Case only to be used for a list with in a field?

Case(

HCOURSE1::CRSNUM = "2903"; "Completed";

HCOURSE1::CRSNUM = "2901"; "Completed";

HCOURSE1::CRSNUM = "2906"; "Completed";

"")

I have a field for 2903 with a calculation:

Case(

HCOURSE1::CRSNUM = "2903"; "Completed";

"")

I have another field 2901 with a calculation:

Case(

HCOURSE1::CRSNUM = "2901"; "Completed";

"")

I continue this for each of the courses. The result "Completed" needs to appear in their own field for each course.

[CobyUSA]

I have the three tables related. The Grade comes in with a course number and a grade on the Course Table relating to the Student ID table and the Audit Table.

I need the calculation to read the grade and record "Completed" to the Audit Table from the Course table.

What is the calculation used in the Audit table to pull the data from the Course table into each unique field for the courses.

I am trying to create a version to send to this forum.

[comment]

The Grade comes in with a course number and a grade on the Course Table relating to the Student ID table and the Audit Table.

I am afraid that doesn't make sense (as a sentence, I mean). With the proper relationships between the tables:

Students -< Enrollments >- Courses

you won't need any calculations of the kind you describe. And certainly not a field for every course. The structure (tables, fields, relationships) should be set ONCE, and allow ANY number of courses and/or students, without having to add fields for them every time.

[CobyUSA]

You are correct because that is how the Courses table is set up.

My end result is I need the data to eventually go to 24 different Audit forms depending on the students course of studies. The Audit form is used to see if a student has completed the courses necessary for their choice of study.

I am having the data go into the Audit table and the 24 different Audit forms pull from it. If I have the data pulled from the Courses table, what do I put on the Audit forms?

For example on the Audit form:

Course Number Status

Biology 1356: "Completed"

English 1021: "Needed"

Don't I need a calculation to get the letter grade from the corresponding courses in the Courses table to come over as "Completed" to the Audit pages?

[comment]

I cannot answer this without knowing how exactly "the courses necessary for their choice of study" are determined.

But in general, there would be no grades in the Courses table. They would be in the Enrollments table - and if you produce a report from the Enrollments table, summarized by Student, you will get something very similar to what you describe.

[CobyUSA]

I am trying to pull only the courses needed for an Audit form.

All the students grades are in the Courses file not just the needed courses. The file is given to me.

I only need certain courses from the file.

I need a calculation to pull only those courses.

Does any one have a file that they only need certain data from and for it to appear on audit form?

[comment]

What can I say - I don't think that's a good approach. If you attach an example of the file you are given, perhaps we can find a way to make it work.

[CobyUSA]

How would you handle a file that is given to you that has 7 records for each student. 1200 students X 7 records. There are 7 records for each student because they take 7 classes. Not all the courses count towards completing an Audit.

The fields are their ID information and course numbers and their grades. Each Semester, I create a new relationship to add the new Semester Table.

From these massive Tables, I need to pull only courses that meet the needs of each student's course of study. There are 24 different choices for the students each with unique audits.

I created 24 unique Audit Layouts. I need data to go into the aduits.

[comment]

The fields are their ID information and course numbers and their grades

That's exactly what you need for the Enrollments table.

I need to pull only courses that meet the needs of each student's course of study

Where is this information - each student's course of study - recorded, and what does it look like?

I suggest you make your explanations much more exact and detailed, or we'll just keep going around. Also, it's not very helpful for you to keep telling me HOW you want to do this - I need to know WHAT you need to have at the end, and what you have at the beginning.

[CobyUSA]

Course Table has all student records 3 fields (ID field, [color:orange]All Courses field, and Grades field) 1200 students each having 7 records...so over 7000 records

Aduit Table 30 fields (1 Student ID field, 28 [color:orange]Required Course Number fields (empty), 1 Grade field (empty). 1200 records...1 per student.

How do I fill the 29 fields in the Audit table?

Edited August 4, 2008 by Guest

[comment]

I'm sorry - I cannot help you with only this to go on.

[CobyUSA]

I got it to work! I changed my file setup and use:

If ( CNUM = 4847 ; " Completed " )

for the differnt fields and of course changing the course numbers!

Thank you for going through the process with me!