How Can I Put Many Table Fields In Single Query Column?

C

ChadNick

I am making a system to show why employees are leaving a company, based on a
five-point system. 1 being an important reason, 5 being unimportant. At the
moment, an employee will, upon leaving, be asked to fill in an electronic
questionaire.

As an example, let me say that their are 3 fields, [Pay], [Argument],
[Retirement]. (Brackets for ease of reading.

So for example, if I was leaving the company due to being underpaid, I would
put a 1 for Pay, a 3 for Argument since I don't like my boss, and a 5 in
retirement.

My question is this; how can I create a query to display the average reasons
people have said they are leaving, in ascending order? Since if each record
is summed, then divided by the total number of records in the database, the
lowest value will be the most important reason.

I currently have a query, showing for each field, the Sum of values in that
field, and a count of records. I can then make a query to show for each
field, what the average is. But how can I then display these values in a way
that can be sorted?
 
D

Duane Hookom

I would not go any further until the table(s) are normalized. The reason for
leaving should be a single field. Each reason creates a new record in a
table. You should be able to add more types of reasons without changing any
tables, forms, queries, reports,...

Do you understand where I am going with this? If not, try search the web for
Normalization and/or
http://office.microsoft.com/en-us/assistance/HA010563211033.aspx
 
C

ChadNick

I did consider normalisation when creating this database, but I'm not sure if
it can work in my situation. There are many more different fields that need
to be stored, such as what industry the employee will be working in, if they
were head-hunted for their new position etc.

In this situation, I am not sure if it is possible to normalise this table.
 
D

Duane Hookom

You can use your current table and add a primary key field. Then just pull
out the "repeating" columns. You can leave the "industry the employee will
be working in" unless you have fields like [Manufacturing], [Medical],
[Technology], ...
 
D

Duane Hookom

"Reason for leaving" is not an attribute of employee. This would be an
attribute of an employee termination.

You can set up constraints based on referential integrity and business
rules.

I can think of no good reason to use the bitwise values on this when the
proper normalization and table structures can handle all requirements.
 
G

Gary Walter

Jamie Collins said:
After all the effort I put in, I couldn't be more disappointed by your
reply.
Click to expand...
I think Duane was afraid he might start an "Argumnet" :cool:

If it means anything, I was certainly impressed by the
work you put into it. When it slows down here at work,
I want to go back and think about what you have
posted.

Gary Walter
 
D

Duane Hookom

Hi, I appreciate the work you have put into this and I must admit I would
use code or create a class object to enforce data integrity.

Question on your solution: What happens if you add more values to your valid
reasons for leaving? I doubt Access/Jet allows you to create constraints
that would be required across tables other than foreign key type. I am not
that knowledgeable on this. I would not however, hard-code in acceptable
values for the reasons for leaving.

--
Duane Hookom
MS Access MVP


Gary said:
I think Duane was afraid he might start an "Argumnet" :cool:
Click to expand...

But I sincerely admire his honesty. He was decent enough to admit has
When it slows down here at work,
I want to go back and think about what you have
posted.
Click to expand...

Bear in mind what I posted concluded that Duane's model leads to a
situation where AFAIK constraints cannot enforce the business rules. So
instead, consider the following simpler model, based on the idea that
reason for leaving *is* an attribute of an employee, with appropriate
constraints:

CREATE TABLE Employees (
ssn CHAR(9) NOT NULL PRIMARY KEY,
employment_start_date DATETIME DEFAULT DATE() NOT NULL,
employment_end_date DATETIME,
reasons_for_leaving INTEGER,
CONSTRAINT ssn_valid_format CHECK(LEN(ssn) = 9),
CONSTRAINT employment_date_order
CHECK (employment_start_date <= employment_end_date),
CONSTRAINT leaving_code_requires_employment_end_date
CHECK(
(employment_end_date IS NULL AND reasons_for_leaving IS NULL)
OR (employment_end_date IS NOT NULL AND reasons_for_leaving IS NOT
NULL)),
CONSTRAINT leaving_code_valid
CHECK(reasons_for_leaving IN (1,2,3,4,8))
)
;

Note although the CHECK syntax has been part of Jet since Access2000,
the UI has lagged so you need either Access2003 or ADO to be able to
*execute* the DDL however, once created, the table may be used in the
earlier versions of the UI.

Jamie.

--
 
G

Gary Walter

ChadNick said:
I am making a system to show why employees are leaving a company, based on a
five-point system. 1 being an important reason, 5 being unimportant. At the
moment, an employee will, upon leaving, be asked to fill in an electronic
questionaire.

As an example, let me say that their are 3 fields, [Pay], [Argument],
[Retirement]. (Brackets for ease of reading.

So for example, if I was leaving the company due to being underpaid, I would
put a 1 for Pay, a 3 for Argument since I don't like my boss, and a 5 in
retirement.

My question is this; how can I create a query to display the average reasons
people have said they are leaving, in ascending order? Since if each record
is summed, then divided by the total number of records in the database, the
lowest value will be the most important reason.

I currently have a query, showing for each field, the Sum of values in that
field, and a count of records. I can then make a query to show for each
field, what the average is. But how can I then display these values in a way
that can be sorted?
Click to expand...

Hi Chad,

Initially it appeared (to me) you needed a tblExitSurvey
and tblQuestions with structures *similar to*:

CREATE TABLE tblQuestions (
QuestionID AUTOINCREMENT CONSTRAINT PK_QuestionID PRIMARY KEY,
Question TEXT(50)
);

CREATE TABLE tblExitSurvey (
SurveyID AUTOINCREMENT CONSTRAINT PK_SurveyID PRIMARY KEY,
EmpID LONG NOT NULL CONSTRAINT FK_EmpID
REFERENCES tblEmployees (EmployeeID),
SurveyDate DATETIME,
QID LONG NOT NULL CONSTRAINT FK_QID
REFERENCES tblQuestions (QuestionID),
Rank INTEGER
);

(assuming you had a tblEmployees w/pk EmployeeID)

So your tblQuestions might look like:

QuestionID Question
1 Pay
2 Argument
3 Retirement

assume tblEmployees

EmployeeID EmpName
1 Homer
2 Marge

and both quit and filled in your survey.

Some sample data for tblExitSurvey might be:

SurveyID EmpID SurveyDate QID Rank
1 1 2/18/2005 1 1
2 1 2/18/2005 2 3
3 1 2/18/2005 3 5
4 2 2/18/2005 1 1
5 2 2/18/2005 2 5
6 2 2/18/2005 3 5

Then I think you wanted a query like:

SELECT
tblQuestions.Question,
Avg(tblExitSurvey.Rank) AS AvgOfRank
FROM tblQuestions INNER JOIN tblExitSurvey
ON tblQuestions.QuestionID = tblExitSurvey.QID
GROUP BY tblQuestions.Question
ORDER BY Avg(tblExitSurvey.Rank);

giving

Question AvgOfRank
Pay 1
Argument 4
Retirement 5

see how making structure "thin, rather than wide"
makes the query so easy?

But...then you added
I did consider normalisation when creating this database, but I'm not sure if
it can work in my situation. There are many more different fields that need
to be stored, such as what industry the employee will be working in, if they
were head-hunted for their new position etc.
Click to expand...

One *possibility* might be to add one more field ("Response") to tblExitSurvey,
then add these questions to tblQuestions:

CREATE TABLE tblExitSurvey (
SurveyID AUTOINCREMENT CONSTRAINT PK_SurveyID PRIMARY KEY,
EmpID LONG NOT NULL CONSTRAINT FK_EmpID
REFERENCES tblEmployees (EmployeeID),
SurveyDate DATETIME,
QID LONG NOT NULL CONSTRAINT FK_QID
REFERENCES tblQuestions (QuestionID),
Rank INTEGER,
Response TEXT(255)
);

For questions that have a Rank, fill that in and
leave Response blank...likewise for questions
that provide a Response, leave Rank blank.

I think (disclaimer: I have no true insight into what
Duane "thinks"....I am mostly a wiseacre with little
time to give serious thought to this) that was
what Duane was suggesting.

Gary Walter
 
D

Duane Hookom

I won't argue with your observations and points. I applaud Joe Celko and
have a couple copies of books he has written.

I will continue to create tables and program using the methods that work
best for me. Dropping and creating constraints on an application that gets
deployed to end users doesn't seem like a good option. I suppose you could
code this with ddl queries. I haven't created apps that would require this
level of constraints that I wouldn't build into the front-end forms and
code. The constraints that I can easily put into the tables, I will put
there.

Based on the original question (that is why we are here isn't it) my
original solution would be the same.
 
Top