Query with 3

[nikila40]

Not even sure how to ask this. I have an excel spreadsheet that I've been
asked to build into a database that tracks exit interview results by
department. Horizontal column at top = departments, vertical column down left
side = 7 questions about company and four rating choices for each -
Excellent, Good, Fair, Poor, N/A. Data in spreadsheet totals the rating
choices for each department, i.e., there are 2 "Good" ratings for Marketing
under the "Your Salary" question. I've built the database, but can't figure
out how to pull all that data together into one report for the totals. I
hope that makes sense! Tx....

 

[KARL DEWEY]

You are laying out your database like a spreadsheet - wrong.
I suggest this way --
Employees --
EmpID - Autonumber - primay key
LName - text
FName -
MI -
HireDate - DateTime
TermDate - DateTime
TermReason - text

ExitQuestion --
QuestionID - Autonumber - primay key
Question - Text
Old - Yes/No

Rating --
RatingID - Autonumber - primay key
Rating - text - Excellent, Good, Fair, Poor, N/A
Old - Yes/No

Interview --
InterID - Autonumber - primay key
QuestionID - Number - Long Integer - foreign key - related to ExitQuestion
table
EmpID - Number - Long Integer - foreign key - related to Employees table
Rating -
Remarks - text
Interviewer - - Number - Long Integer - foreign key - related to Employees
table


Use the Old - Yes/No when something is no longer to be used but retain
history.

Setup one-to-many relationships between the primary key and the foreign keys.

Use a crosstab query 'to pull all that data together into one report for the
totals.'

 

[nikila40]

Hey Karl! Tx for the response. Another problem is there are many other parts
to this exit interview questionnaire, but not all have the ratings listed
below as responses. There are two that use the same rating I mention below,
another that uses a 0 - 10 rating (self performance rating), and a bunch that
require yes/no/why and other assorted drop-downs. Would I still be able to
use your idea below to build all that as one database? There is also no
"employee" table - too many employees in the company to list (over 9,000).
It was a fill-in.

 

[KARL DEWEY]

There is also no "employee" table - too many employees in the company to
list (over 9,000). It was a fill-in.
Ok, fillin as you go to include interviewer.
require yes/no/why and other assorted drop-downs.
You could build your Rating table to include question number so that the
drop-down only has the approppriate ratings available for the question.

 

[nikila40]

YOU ROCK.....thanks....;o)

list (over 9,000). It was a fill-in.
Ok, fillin as you go to include interviewer.

require yes/no/why and other assorted drop-downs.
You could build your Rating table to include question number so that the
drop-down only has the approppriate ratings available for the question.

 

[nikila40]

Sorry - me again! Are you saying to get rid of the question table and add
the questions to the rating table? If not, how do I add the question number
field and make sure only certain drop-downs are avail for each? Hope that
makes sense....I've been trying to figure out but can't.

 

[KARL DEWEY]

Are you saying to get rid of the question table and add the questions to
the rating table?
Add the QuestionID to the rating table. Use a ComboBox for rating pick.
Use a query as it's source that has QuestionID from current record (your
form) as criteria. In the form refresh the query after update of question.
That way only the ratings appropriate to the question appear for selection in
the combo.

 

[nikila40]

Thanks again!

the rating table?
Add the QuestionID to the rating table. Use a ComboBox for rating pick.
Use a query as it's source that has QuestionID from current record (your
form) as criteria. In the form refresh the query after update of question.
That way only the ratings appropriate to the question appear for selection in
the combo.