Entering Data with A CrossTab Query

[Olivier L]

Dear Access Masters,
This is my first post, so it might be a little messy. I hope I'll give
enough info. I am trying to develop an employee assessment system. I managed
to make a form in table view, it works fine but it is less practical than a
crosstab. The form is based on a one-table-based Query with the following
fields:
Appraisal session, date
Appraisee, Text
Appraiser, Text
Criteria, Text
Score, Number
I would like to get the following form:
Criteria1 Criteria2
Employee1 a b
Employee2 c d

The Appraiser would enter "a" as the score value for Employee1 on criteria1.
Apparently, crosstab queries are read only. Is there any other way around it,
or should I go with the initial table view with one row per
Appraisee-Criteria combination?

Thank you very much for your help

Olivier L

 

[Allen Browne]

The table structure you have makes good sense.

The interface is a little more tricky. One approach would be to make a
temporary table set up like a crosstab. Users can enter the data into the
temp table. At the end of that process, they click a button to write the
data to the real table, and you then execute a series of Append queries that
write a record for each criterion.

 

[Olivier L]

Thanks Allen! May I ask what you mean by "then they click a button to write
the data to the real Table"? You mean like a Make-Table query, or a form in
datasheet view, or...? Also, that would mean a single table per appraiser, am
i correct?

Cheers!

 

[Allen Browne]

In the Click event of the query, you would loop through however many
criteria you had columns for, and execute an Append query for each one.

This kind of thing:
Private Sub cmdOk_Click()
Dim db As DAO.Database
db.Execute "INSERT INTO ...
...