QUERY - ACCESS 2000

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the below type of data
name Q1 Q1f Q2 Q2f Q3 Q3f
brown yes Manager no yes HR
green no yes HR no
red yes Manager yes Manager no

my main objective is to create a report that will allow me to total the
number of yes and no and also create a report that will allow me to sort by
manager of HR.

Can anyone tell me what I need to do with the above data to get it in the
right format. I am new to Acccess and of course have went the long way for
yes and no response by creating individual update queries for each question
and appending to another table. Please let me know that there is a more
efficent way for this .
Thanks in advance to helping out a novice
 
I would say you need to normalize you database structure but first I need to
know what is the significance of the three columns?
Your table probably should look like this --
name Q Qf X

What is the sunificence
 
Thanks for the quick reply - each row represents data from one person - the
way that you have the table set up is the look that I am looking to
accomplish. The only way I was able to do this was by running a query on
each Q1, Q2, Q3, etc... and then append it to the master table. I do not want
to have to do the same for the Q1f, Q2f, Q3f, etc. Can you tell me how to
"normalize" as this data is in an Excel spreadsheet.
 
You have to append Q1f at the same time with Q1 and name. The "X" field I
put was to distinguish your Q1 function from Q2.
 
Melissa you asked: "Can you tell me how to
"normalize" as this data is in an Excel spreadsheet."
Click to expand...

If this was in a database you would have two tables one for the personal
identification and one for q.

table1 = Person (with fields like)
PID = ID for person, this is unique to each
FNAME = this is for the first name
LNAME = this is for the last name
.... ( you can also put in things like address or anything else relative to
this person)

table2 = Q (with fields like)
QID = ID for Q, this is unique to each
Q = Text field to put in yes or no
Qf = Text field for info such as manager, hr, etc...
QNUM = This stands for your fields of Q1, Q2, Q3...
PID = foreign key relating back to the Person table

This way your table2 based on the data you already gave would look like this
QID Q Qf QNUM PID
1 YES Manager Q1 1
2 No Q2 1
3 Yes HR Q3 1
4 No Q1 2
5 Yes HR Q2 2
6 No Q3 2
7 Yes Manager Q1 3
8 Yes Manager Q2 3
9 No Q3 3

That would normalize your data.

hth

Thanks for the quick reply - each row represents data from one person - the
way that you have the table set up is the look that I am looking to
accomplish. The only way I was able to do this was by running a query on
each Q1, Q2, Q3, etc... and then append it to the master table. I do not want
to have to do the same for the Q1f, Q2f, Q3f, etc. Can you tell me how to
"normalize" as this data is in an Excel spreadsheet.
I would say you need to normalize you database structure but first I need to
know what is the significance of the three columns?
Click to expand...
[quoted text clipped - 19 lines]
Click to expand...
 
Back
Top