do I need a join file?

[susan]

I'm working on a solution where I am tracking the scores of a number of different tests for a number of different students and I'm not sure if I need a join file...here are the details:

Students take the Iowa Basics test in Reading and Math, a Title 1 test in Reading and Math, State Standards tests in Reading, Math, and Writing, and QRI in the Spring and Fall. I want to be able to compare a student's score on a particular test over the course of their elementary years. (The test scores are never compared to a different test. In other words, I will only compare their Iowa Basic Reading score from one year with their Iowa Basic Reading score from a different year).

I have a Students file that contains all the demographics for each student. Do I need a separate file for each test, or can I create a Tests file which will contain all the different tests for a given year? I think I can do it this way, but what would I use for the Key? Student_ID?

Thanks.

Susan

[Ocean West]

A seperate file for all test should suit your purposes. Once you have data in them you can do a find for a student/test and compare all like tests for that student. And or create a summarized report by test.

IE:

Reading Test for Jimmy Jones

1999 = 87

2000 = 85

2001 = 88

2002 = 79

Math Tests for Jimmy Jones

1999 = 77

2000 = 79

2001 = 73

2002 = 76

or something to that effect.

[susan]

Thanks. I have that set up to do what I want, and I can track a given student for the six years they are in the system. But the client reminded me that we need to also track the test scores across X number of years (somewhat unrelated to a specific student). In 2, 10, 18 years, whatever, they want to be able to compare the average test scores across time so they can show the overall hopeful improvement. How do I set that up?

Right now I have a Students.fm5 file and a file for each test (IowaReading, IowaMath, MCAReading, etc.) Each of the test files has a record for Student_ID and grades K through 5. I have them both related to Reports.fm5 where I pull in the data. I know I could track across time by creating a file for each test for each year (IowaReading2001, Iowa Reading 2002, etc), but what a nightmare. I know there is a better way....can you shine some light on it for me? Thanks!

Susan

[Rigsby]

What you need to do is to also get the test date stored in your test files, and then use the dates as a key to create another link to your reports file. You don’t even need to be using different test files, you could store all the information in one test file. So your solution would look something like this:

Student File:

Student ID is a must here.

Test File:

Test name

Test date (lets just use years for now)

Student ID

Result

Ok, now you need to create your keys. Take each of the first 4 fields above as your basic keys – so:

Test name

Test date

Student ID

Now start to create further keys using calculation-fields – for example

(so we’re sort of creating an array)

Test name

Test name & Test date

Test name & Student ID

Test name & Test date & Student ID

Test date

Test date & Student ID

I don’t know if you are following this, but with the above keys to create relationships (and you could develop these keys even further) you can pull any information into your report file that you want – you just need to link the files several times – getting my drift??? With this you could sort results for each, any or all tests in any year or over any series of years. You can do the same for students, etc., etc.

I really don’t know if you can follow this, or if it helps – but I hope so!

Rigsby

[susan]

I think I'm following it (at least it makes sense!) But I'm confused about the calculation fields...Do I create a field for each key in my Tests file(like TestNameDate or something) and make it a calculation (=TestName & TestDate)and then make a field in my Reports file for each key? Or I can probably use the same key field in Reports and link it to more than one field in Tests? Thanks for your help!

Susan