Complex Search and Display

[SMM]

All,

I created a customer evaluation db using FMPro 6. I didn't have the opportunity to set up relational tables, so I'm stuck with non-relational layouts.

Each record in the database represents a client survey based on a unique project number. We might generate for and receive from a client as many as 3 surveys per project number. The record lists 3 to 5 staff members and their ratings, 1 through 6. The problem comes when I want to display a table of project numbers vs. staff names and show the staff's corresponding scores. An example looks like this:

Project___Mary___Bob____Sue

No.

12345______5______4

23456_____________3______5

23456_____________3______6

So the display would tell me that for project 12345, on which Mary and Bob worked, they got a 5 and 4, respectively. Two client contacts reviewed Bob and Sue's performance on Project 23456.

Here's the hard part: The database is not organized such that I know that a particular staff member--say, Bob--is always in the db field named staff1 (or staff2 or etc.) He could show up in the field staff1 for one survey, but in staff5 for another survey!

I'm at a loss to determine how I can organize my data without starting all over with a relational model.

Thank you for any help, and for "listening".

Steve M.

Edited April 19, 2006 by Guest