A student is unique and has a StudentIDNumber.
A student attends multiple semesters or quarters in multiple years related to student by StudentIDNumber in SemesterTable.
A student takes multiple classes in the semesters or quarters related to student by StudentIDNumber in ClassTable..
Do you want to find by student?
Do you want to find by semester?
Do you want to find by class in a semester?
There are three tables here so far.
The format for the data being imported does not have student as a unique ID. It comes in similar to:
A - Math -3
A - English -4
A - Science -3
B - Math -2
B - English -3
B - Science -3
where student A has 3 records and student B has 3 records (student C may have only 2). What I'd like to do is consolidate the x records per student into one with the following format.
Student Math English Science
A 3 4 3
B 2 3 3
In terms of performing finds it would be by student.
Thanks in Advance
What happens if you have two students named "john smith"? Do you have any procedure in place where this imported data is generated to enforce unique student names? (by adding a middle initial or number to the second student with the same name for example.) If not, you could experience trouble down the road.
Personally, I'd keep the entries in separate records but replace student names with unique student ID numbers if at all possible. There are several ways to display a "cross tab" style report that still leaves your records uncombined.
Hi Phil. The example I gave above is a representation of the data I'm working with. So it's not really to keep track of grades. =) I thought it'd be easier to explain using the grades example with only 3 fields. Do you have any good references that speak more about cross tabs. The only examples I've been able to come across use date fields in the calculations which in my case would probably be using the class field?
Thanks in Advance
When you post abstract examples, you risk getting responses that don't apply due to the differences between your example and the real problem.
Cross tab style reports generally work either from multiple relationships, one for each column or a series of portals set to display only row--with a different row of the related records specified in each column. Exactly how you do that depends on the specific data and what rules determine which records should report data in a given column.
With either approach, you'll need a second table where you have one record for each "student" and use it for your report.
Multiple relationships is probably the better approach here as you've indicated that not all "students" will have the same number of "subjects":
Define a key field with an auto-entered value or a calculation that matches to a given "subject". For your "math" column, you'd define a key field in the report table that has the value "Math" and so forth for each column.
Define a Table occurrence of your imported data's table for each column.
Your relationships would look like this:
ReportTable::Student = col1TO::Student AND
ReportTable::MathKey = Col1TO::Subject
Each column uses a similar relationship but with a different "subjectkey"
Now place the place the "grade" field from col1TO in column 1, the "grade" field from col2TO in Column 2 and so forth for each subject.