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
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?


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 ...

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question