Table Challenge

[Tom]

I need some help with modifying a current design which does not seem to be
very efficient (after having integrated a process changes).

Here's what I currently have:

A generic survey (table) structure that follows the type of an "outline
principal". What do I mean by that?

Here's an example of the questions (Qs) table/structure.

Q1
Q1.1
Q1.1.1
Q1.1.2
Q1.2
Q1.2.1
Q1.3
Q1.4
Q1.4.1
Q1.4.2
Q2
Q2.1
Q2.2
etc., etc.

- The questions on the 1st level (Q1, Q2) are stored in one table.
- The questions on the 2nd level (Q1.1, 1.2, 1.3, 1.4, etc.) are stored in
another table (and linked to 1st level table).
- The same for the 3rd level questions (linked to 2nd level table).

Essentially, the 1st level and 2nd level break down survey questions into
topics and subtopics. Finally, the 3rd level contains the actual questions
where answers (scores) will be collected. Makes sense so far?

As of now, I have only 1 placeholder (in the 3-rd level table) for each
answer (score).


Here's now where the new process would make this -- currently working --
system inefficient.

Naturally, I will have to ask more than just 1 employee to complete the
survey. Under the old structure, I had 1 record for each question; and 4
fields (1 for each quarter).

Now, however, I need to figure out how to collect the data/scores from all
employees. All of them will be asked the same questions (4 times a year...
each quarter).

Am I wrong to think about 2 approaches:
1. Replicate the same 3-tier structure e.g. 200 times (if I had 200
employees)... that does not seem to make sense.
or
2. Duplicate the 4 Quarters fields for each employee... but that would mean
I have to add fields for each employee (in this case 800 fields)... seems
impossible as well.

Logically, it would make sense to simply add records for each employee.
But, there would be a problem w/ this approach as well (I think).
Currently (with 1 employee), I run queries to average the scores and divide
them the number of instances for each question that belongs to a "subtopic".

So, for example, if the answers are:
1.1.1 = 50%
1.1.2 = 100%
1.1.3 = 75%

then this particular employee has given the question/topic "1.1" an overall
score of 75%. If I were to add all employees, I'm not sure how to
distinguish between all of them?

Hopefully, this makes sense?


Thanks,
Tom

 

[Sprinks]

Hi, Tom.

As you sense, whenever a proposed design looks like it's going to involve a
lot of fields to implement, it should raise a red flag that the design is
flawed. It would probably be a good idea for you to pick up a good Access
reference book, and thoroughly cover "Normalization". A well-normalized
application makes for smooth, easy development.

Unless I'm missing something, all you need to add is an EmployeeID field to
your 3rd table (a foreign key to an Employees table). And a single Totals
parameter query should give you the totals you need, grouped on EmployeeID,
the Quarter, the first 3 characters of the question number, and averaging the
score. The parameter prompts for the quarter (I'm assuming you're going to
dump the old scores the next year, so that you don't also need a Year field).
The SQL would be something like:

SELECT Scores.EmployeeID, Scores.Quarter, Left([Question],3) AS Category,
Avg(Scores.Score) AS AvgOfScore
FROM Scores
GROUP BY Scores.EmployeeID, Scores.Quarter, Left([Question],3)
HAVING (((Scores.Quarter)=[Enter Quarter:]));

To print them out, join this query to the other tables as required to get
the Employee Name, Category Name, etc. and base your report on this 2nd query.

Hope that helps.
Sprinks