I'm not even sure where to post this question, so if anyone moves it to a more appropriate forum, thanks in advance.

My boss recently asked me to keep track of web-based test scores for some of our students. We receive the scores from the test administering company via email in an Excel file. Each student is listed in a row with the following in successive columns:

LastName, FirstName, Score1, Score2,...Score14,Score15,TotalScore,DateofTest

Scores for sections 1-15 are single-digit, and the total score is 2 digits.

I created a database with text fields for student names and separate fields for each of the individual scores. Each record has roughly 20 fields. I can capture all the data in the newly-created "Test" database and relate it back to my master "Student" database by using the FirstLast::FirstLast relationship of student names, but it seems like there has to be a much more elegant solution than having 15 fields per student with only 1 number per field.

What I would like to do, (or to be told why it's a bad idea) is to put the 15 scores into one field in the master "Student" database. That way, I would use less database files, less fields, and be able to capture any students who take the test, but are not already in the master list. I've never used concatenated entries into a single field, and am unaware of the benefits/pitfalls. If there's a prior discussion on FMF, I'd love to see it.

Functionally, the individual scores do not need to be searchable, since the aggregate total is the ultimate pass/fail indicator. For the students' interest, however, I may eventually want to make the individual scores available via the web. I'm guessing I could use the Position() function to extract particular scores. I'm a little concerned about importing new records when new emails show up. (works well with individual fields)

After reading several posts from other FM enthusiasts about parsing out data that has all been stored in one field, I'm gun-shy about condensing this into one field. Of course, I'm equally certain that there is a much cleaner way to store this data. Any comments or help would be appreciated.

Paul

FileMaker Version: 6

Platform: Windows XP

Definitely keep the test scores in separate fields for that file. If you put the scores together, it will just be more work to extract them later (what happens if a score is blank?.) It is possible to have a separate Score file that has one score per student, but for only 15 scores, this probably is not necessary.

I would also recommend using a Student ID, instead of relying on the names as a key. There are often times when two different students will have the same first and last name.

Thanks Andrew. I get nervous every time I add "just one more file" to my system, and was looking for a way to incorporate test scores into my existing file without creating 20 new fields. I suppose it could be fairly restrictive, though.

Paul

20 more text or number fields won't add much to the file size (unless you have hundreds of thousands of records.) It's indexes and containers that can really add weight.