Totals Query?

  • Thread starter Thread starter James
  • Start date Start date
J

James

Hello I have a form and on this form I have 3 combo
boxes...

Lets just say there called:

cbo1
cbo2
cbo3

I would like to have a query where by it calculates the
total number of records the criteria above specifies and
also a percentage out of them records...

So something like 20% of 200 Records.

So for example the criteria may be:

Q01
Answer as True
Female

Then it would be like 20% of the 200 records that have
been answered... So as the amount of records go up in the
table so will the no of records on the form... Where by
the percentage may change according to teh results....

Can anyone assist?

Many Thanks

James
 
Assuming your table looks something like:
QuestionID, Question, Answer, Gender

Then the solution will look something like:

SELECT Count(Question)/(SELECT Count(*) FROM MyTable As M2 WHERE M2.Question
= [Forms]![MyForm]![cbo1]) As Percent
FROM MyTable
WHERE MyTable.Question = [Forms]![MyForm]![cbo1]
AND MyTable.Answer = [Forms]![MyForm]![cbo2]
AND MyTable.Gender = [Forms]![MyForm]![cbo3]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Ok well it looks more like...

Gender,Q01,Q02 ETC TO Q25

There all value lists with certain values as the answers
i.e. True False

Does this affect the query Any?

Many Thanks

James
-----Original Message-----
Assuming your table looks something like:
QuestionID, Question, Answer, Gender

Then the solution will look something like:

SELECT Count(Question)/(SELECT Count(*) FROM MyTable As M2 WHERE M2.Question
= [Forms]![MyForm]![cbo1]) As Percent
FROM MyTable
WHERE MyTable.Question = [Forms]![MyForm]![cbo1]
AND MyTable.Answer = [Forms]![MyForm]![cbo2]
AND MyTable.Gender = [Forms]![MyForm]![cbo3]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hello I have a form and on this form I have 3 combo
boxes...

Lets just say there called:

cbo1
cbo2
cbo3

I would like to have a query where by it calculates the
total number of records the criteria above specifies and
also a percentage out of them records...

So something like 20% of 200 Records.

So for example the criteria may be:

Q01
Answer as True
Female

Then it would be like 20% of the 200 records that have
been answered... So as the amount of records go up in the
table so will the no of records on the form... Where by
the percentage may change according to teh results....

Can anyone assist?

Many Thanks

James
Click to expand...


.
Click to expand...
 
Also when I use the SQL you have given me it comes up and
tells me I am using a reserved word. Which is Percent??

What is goin on here?

Many Thanks

James
-----Original Message-----
Ok well it looks more like...

Gender,Q01,Q02 ETC TO Q25

There all value lists with certain values as the answers
i.e. True False

Does this affect the query Any?

Many Thanks

James
-----Original Message-----
Assuming your table looks something like:
QuestionID, Question, Answer, Gender

Then the solution will look something like:

SELECT Count(Question)/(SELECT Count(*) FROM MyTable As M2 WHERE M2.Question
= [Forms]![MyForm]![cbo1]) As Percent
FROM MyTable
WHERE MyTable.Question = [Forms]![MyForm]![cbo1]
AND MyTable.Answer = [Forms]![MyForm]![cbo2]
AND MyTable.Gender = [Forms]![MyForm]![cbo3]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hello I have a form and on this form I have 3 combo
boxes...

Lets just say there called:

cbo1
cbo2
cbo3

I would like to have a query where by it calculates the
total number of records the criteria above specifies and
also a percentage out of them records...

So something like 20% of 200 Records.

So for example the criteria may be:

Q01
Answer as True
Female

Then it would be like 20% of the 200 records that have
been answered... So as the amount of records go up in the
table so will the no of records on the form... Where by
the percentage may change according to teh results....

Can anyone assist?

Many Thanks

James
Click to expand...


.
Click to expand...
.
Click to expand...
 
You have two problems:

1) You cannot specify a field name with a parameter
2) Your table is badly designed - it violates First Normal Form (repeating
group)

You can either redesign your table and use a query similar to what I
suggested or you must write code to dynamically create the query with the
correct field name. Your table design should look something like:

tblQuestionnaires - QuestionnaireID, Description, Requestor, etc.

tblQuestions - QuestionnaireID, QuestionID, QuestionType, QuestionText

tblRespondents - RespondentID, Name, Gender, Address, eMail, etc.

tblQuestionResponses - RespondentID, QuestionnaireID, QuestionID, Answer

Note that the above design allows you to define an infinite number of
questionnaires, a variable number of questions per questionnaire, and an
infinite number or respondents, and to track the responses to each
questionnaire/question/respondent combination. Designed like this, it's a
simple matter to create a parameter query that joins tblRespondents (to get
Gender) with tblQuestionResponses to count, get percentages, and do all
sorts of analysis.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
James said:
Ok well it looks more like...

Gender,Q01,Q02 ETC TO Q25

There all value lists with certain values as the answers
i.e. True False

Does this affect the query Any?

Many Thanks

James
-----Original Message-----
Assuming your table looks something like:
QuestionID, Question, Answer, Gender

Then the solution will look something like:

SELECT Count(Question)/(SELECT Count(*) FROM MyTable As M2 WHERE M2.Question
= [Forms]![MyForm]![cbo1]) As Percent
FROM MyTable
WHERE MyTable.Question = [Forms]![MyForm]![cbo1]
AND MyTable.Answer = [Forms]![MyForm]![cbo2]
AND MyTable.Gender = [Forms]![MyForm]![cbo3]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hello I have a form and on this form I have 3 combo
boxes...

Lets just say there called:

cbo1
cbo2
cbo3

I would like to have a query where by it calculates the
total number of records the criteria above specifies and
also a percentage out of them records...

So something like 20% of 200 Records.

So for example the criteria may be:

Q01
Answer as True
Female

Then it would be like 20% of the 200 records that have
been answered... So as the amount of records go up in the
table so will the no of records on the form... Where by
the percentage may change according to teh results....

Can anyone assist?

Many Thanks

James
Click to expand...


.
Click to expand...
Click to expand...
 
Ok this I understand...

The user only wants to input the following information:

Gender
Answer to questions
and then get a percentage out of the results...

So all she wants is litrally what I have set up... 25
Questions just labled Q01 - Q25 and all the answers are
True or False and then she just wants the Gender...

Is all hwat you have said still need to be done?

Many Thanks

James
-----Original Message-----
You have two problems:

1) You cannot specify a field name with a parameter
2) Your table is badly designed - it violates First Normal Form (repeating
group)

You can either redesign your table and use a query similar to what I
suggested or you must write code to dynamically create the query with the
correct field name. Your table design should look something like:

tblQuestionnaires - QuestionnaireID, Description, Requestor, etc.

tblQuestions - QuestionnaireID, QuestionID, QuestionType, QuestionText

tblRespondents - RespondentID, Name, Gender, Address, eMail, etc.

tblQuestionResponses - RespondentID, QuestionnaireID, QuestionID, Answer

Note that the above design allows you to define an infinite number of
questionnaires, a variable number of questions per questionnaire, and an
infinite number or respondents, and to track the responses to each
questionnaire/question/respondent combination. Designed like this, it's a
simple matter to create a parameter query that joins tblRespondents (to get
Gender) with tblQuestionResponses to count, get percentages, and do all
sorts of analysis.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Ok well it looks more like...

Gender,Q01,Q02 ETC TO Q25

There all value lists with certain values as the answers
i.e. True False

Does this affect the query Any?

Many Thanks

James
-----Original Message-----
Assuming your table looks something like:
QuestionID, Question, Answer, Gender

Then the solution will look something like:

SELECT Count(Question)/(SELECT Count(*) FROM MyTable As M2 WHERE M2.Question
= [Forms]![MyForm]![cbo1]) As Percent
FROM MyTable
WHERE MyTable.Question = [Forms]![MyForm]![cbo1]
AND MyTable.Answer = [Forms]![MyForm]![cbo2]
AND MyTable.Gender = [Forms]![MyForm]![cbo3]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hello I have a form and on this form I have 3 combo
boxes...

Lets just say there called:

cbo1
cbo2
cbo3

I would like to have a query where by it calculates the
total number of records the criteria above specifies and
also a percentage out of them records...

So something like 20% of 200 Records.

So for example the criteria may be:

Q01
Answer as True
Female

Then it would be like 20% of the 200 records that have
been answered... So as the amount of records go up in the
table so will the no of records on the form... Where by
the percentage may change according to teh results....

Can anyone assist?

Many Thanks

James


.
Click to expand...
Click to expand...


.
Click to expand...
 
If this is a "one off" project, you can probably make it work with the table
structure you have. Yes, Percent is a reserved word, so I should have
enclosed it in brackets. Your query for question 1 might look like:

SELECT Count(Q01)/(SELECT Count(*) FROM MyTable As M2) As PercentQ01
FROM [MyTable]
WHERE [MyTable].Q01= [Forms]![MyForm]![cbo1]
AND [MyTable].Gender = [Forms]![MyForm]![cbo2]

... where cbo1 contains True or False and cb02 contains the gender desired.
"MyTable" is the name of your table, and "MyForm" is the name of your form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
James said:
Ok this I understand...

The user only wants to input the following information:

Gender
Answer to questions
and then get a percentage out of the results...

So all she wants is litrally what I have set up... 25
Questions just labled Q01 - Q25 and all the answers are
True or False and then she just wants the Gender...

Is all hwat you have said still need to be done?

Many Thanks

James
-----Original Message-----
You have two problems:

1) You cannot specify a field name with a parameter
2) Your table is badly designed - it violates First Normal Form (repeating
group)

You can either redesign your table and use a query similar to what I
suggested or you must write code to dynamically create the query with the
correct field name. Your table design should look something like:

tblQuestionnaires - QuestionnaireID, Description, Requestor, etc.

tblQuestions - QuestionnaireID, QuestionID, QuestionType, QuestionText

tblRespondents - RespondentID, Name, Gender, Address, eMail, etc.

tblQuestionResponses - RespondentID, QuestionnaireID, QuestionID, Answer

Note that the above design allows you to define an infinite number of
questionnaires, a variable number of questions per questionnaire, and an
infinite number or respondents, and to track the responses to each
questionnaire/question/respondent combination. Designed like this, it's a
simple matter to create a parameter query that joins tblRespondents (to get
Gender) with tblQuestionResponses to count, get percentages, and do all
sorts of analysis.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Ok well it looks more like...

Gender,Q01,Q02 ETC TO Q25

There all value lists with certain values as the answers
i.e. True False

Does this affect the query Any?

Many Thanks

James

-----Original Message-----
Assuming your table looks something like:
QuestionID, Question, Answer, Gender

Then the solution will look something like:

SELECT Count(Question)/(SELECT Count(*) FROM MyTable As
M2 WHERE M2.Question
= [Forms]![MyForm]![cbo1]) As Percent
FROM MyTable
WHERE MyTable.Question = [Forms]![MyForm]![cbo1]
AND MyTable.Answer = [Forms]![MyForm]![cbo2]
AND MyTable.Gender = [Forms]![MyForm]![cbo3]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
message
Hello I have a form and on this form I have 3 combo
boxes...

Lets just say there called:

cbo1
cbo2
cbo3

I would like to have a query where by it calculates the
total number of records the criteria above specifies and
also a percentage out of them records...

So something like 20% of 200 Records.

So for example the criteria may be:

Q01
Answer as True
Female

Then it would be like 20% of the 200 records that have
been answered... So as the amount of records go up in
the
table so will the no of records on the form... Where by
the percentage may change according to teh results....

Can anyone assist?

Many Thanks

James


.
Click to expand...


.
Click to expand...
Click to expand...
 
Would I have to do teh below SQL for each Question???

The reason being is that Its totally dependant on whta the
user selects as to what question is pulled?

Would it be easier if you had a quick scan at the
database? so you have a better understanding and then
maybe you could assist further based on what you see??

Many Thanks

James
-----Original Message-----
If this is a "one off" project, you can probably make it work with the table
structure you have. Yes, Percent is a reserved word, so I should have
enclosed it in brackets. Your query for question 1 might look like:

SELECT Count(Q01)/(SELECT Count(*) FROM MyTable As M2) As PercentQ01
FROM [MyTable]
WHERE [MyTable].Q01= [Forms]![MyForm]![cbo1]
AND [MyTable].Gender = [Forms]![MyForm]![cbo2]

... where cbo1 contains True or False and cb02 contains the gender desired.
"MyTable" is the name of your table, and "MyForm" is the name of your form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Ok this I understand...

The user only wants to input the following information:

Gender
Answer to questions
and then get a percentage out of the results...

So all she wants is litrally what I have set up... 25
Questions just labled Q01 - Q25 and all the answers are
True or False and then she just wants the Gender...

Is all hwat you have said still need to be done?

Many Thanks

James
-----Original Message-----
You have two problems:

1) You cannot specify a field name with a parameter
2) Your table is badly designed - it violates First Normal Form (repeating
group)

You can either redesign your table and use a query similar to what I
suggested or you must write code to dynamically create the query with the
correct field name. Your table design should look something like:

tblQuestionnaires - QuestionnaireID, Description, Requestor, etc.

tblQuestions - QuestionnaireID, QuestionID,
Click to expand...
QuestionType,
QuestionText
tblRespondents - RespondentID, Name, Gender, Address, eMail, etc.

tblQuestionResponses - RespondentID, QuestionnaireID, QuestionID, Answer

Note that the above design allows you to define an infinite number of
questionnaires, a variable number of questions per questionnaire, and an
infinite number or respondents, and to track the responses to each
questionnaire/question/respondent combination.
Click to expand...
Designed
like this, it's a
simple matter to create a parameter query that joins tblRespondents (to get
Gender) with tblQuestionResponses to count, get percentages, and do all
sorts of analysis.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Ok well it looks more like...

Gender,Q01,Q02 ETC TO Q25

There all value lists with certain values as the answers
i.e. True False

Does this affect the query Any?

Many Thanks

James

-----Original Message-----
Assuming your table looks something like:
QuestionID, Question, Answer, Gender

Then the solution will look something like:

SELECT Count(Question)/(SELECT Count(*) FROM MyTable As
M2 WHERE M2.Question
= [Forms]![MyForm]![cbo1]) As Percent
FROM MyTable
WHERE MyTable.Question = [Forms]![MyForm]![cbo1]
AND MyTable.Answer = [Forms]![MyForm]![cbo2]
AND MyTable.Gender = [Forms]![MyForm]![cbo3]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
message
Hello I have a form and on this form I have 3 combo
boxes...

Lets just say there called:

cbo1
cbo2
cbo3

I would like to have a query where by it
Click to expand...
calculates
the
total number of records the criteria above
Click to expand...
specifies
and
also a percentage out of them records...

So something like 20% of 200 Records.

So for example the criteria may be:

Q01
Answer as True
Female

Then it would be like 20% of the 200 records that have
been answered... So as the amount of records go up in
the
table so will the no of records on the form...
Click to expand...
Where
by
the percentage may change according to teh results....

Can anyone assist?

Many Thanks

James


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Yes, you would have to run a separate query for each question.

No, I do not want to see your database. I'm happy to try to answer specific
questions in these newsgroups for free, but if you want me to analyze your
database, we'll have to go offline and talk about my hourly rates. Send me
an email at JohnV at Viescas.com if you're interested in pursuing this
further.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
james said:
Would I have to do teh below SQL for each Question???

The reason being is that Its totally dependant on whta the
user selects as to what question is pulled?

Would it be easier if you had a quick scan at the
database? so you have a better understanding and then
maybe you could assist further based on what you see??

Many Thanks

James
-----Original Message-----
If this is a "one off" project, you can probably make it work with the table
structure you have. Yes, Percent is a reserved word, so I should have
enclosed it in brackets. Your query for question 1 might look like:

SELECT Count(Q01)/(SELECT Count(*) FROM MyTable As M2) As PercentQ01
FROM [MyTable]
WHERE [MyTable].Q01= [Forms]![MyForm]![cbo1]
AND [MyTable].Gender = [Forms]![MyForm]![cbo2]

... where cbo1 contains True or False and cb02 contains the gender desired.
"MyTable" is the name of your table, and "MyForm" is the name of your form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Ok this I understand...

The user only wants to input the following information:

Gender
Answer to questions
and then get a percentage out of the results...

So all she wants is litrally what I have set up... 25
Questions just labled Q01 - Q25 and all the answers are
True or False and then she just wants the Gender...

Is all hwat you have said still need to be done?

Many Thanks

James

-----Original Message-----
You have two problems:

1) You cannot specify a field name with a parameter
2) Your table is badly designed - it violates First
Normal Form (repeating
group)

You can either redesign your table and use a query
similar to what I
suggested or you must write code to dynamically create
the query with the
correct field name. Your table design should look
something like:

tblQuestionnaires - QuestionnaireID, Description,
Requestor, etc.

tblQuestions - QuestionnaireID, QuestionID, QuestionType,
QuestionText

tblRespondents - RespondentID, Name, Gender, Address,
eMail, etc.

tblQuestionResponses - RespondentID, QuestionnaireID,
QuestionID, Answer

Note that the above design allows you to define an
infinite number of
questionnaires, a variable number of questions per
questionnaire, and an
infinite number or respondents, and to track the
responses to each
questionnaire/question/respondent combination. Designed
like this, it's a
simple matter to create a parameter query that joins
tblRespondents (to get
Gender) with tblQuestionResponses to count, get
percentages, and do all
sorts of analysis.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
message
Ok well it looks more like...

Gender,Q01,Q02 ETC TO Q25

There all value lists with certain values as the answers
i.e. True False

Does this affect the query Any?

Many Thanks

James

-----Original Message-----
Assuming your table looks something like:
QuestionID, Question, Answer, Gender

Then the solution will look something like:

SELECT Count(Question)/(SELECT Count(*) FROM MyTable As
M2 WHERE M2.Question
= [Forms]![MyForm]![cbo1]) As Percent
FROM MyTable
WHERE MyTable.Question = [Forms]![MyForm]![cbo1]
AND MyTable.Answer = [Forms]![MyForm]![cbo2]
AND MyTable.Gender = [Forms]![MyForm]![cbo3]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
message
Hello I have a form and on this form I have 3 combo
boxes...

Lets just say there called:

cbo1
cbo2
cbo3

I would like to have a query where by it calculates
the
total number of records the criteria above specifies
and
also a percentage out of them records...

So something like 20% of 200 Records.

So for example the criteria may be:

Q01
Answer as True
Female

Then it would be like 20% of the 200 records that
have
been answered... So as the amount of records go up in
the
table so will the no of records on the form... Where
by
the percentage may change according to teh
results....

Can anyone assist?

Many Thanks

James


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Well ok then so how do I create a seperate query for each
question? Not to mention could it all be in one SQL query??

Many Thanks

James
-----Original Message-----
Yes, you would have to run a separate query for each question.

No, I do not want to see your database. I'm happy to try to answer specific
questions in these newsgroups for free, but if you want me to analyze your
database, we'll have to go offline and talk about my hourly rates. Send me
an email at JohnV at Viescas.com if you're interested in pursuing this
further.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Would I have to do teh below SQL for each Question???

The reason being is that Its totally dependant on whta the
user selects as to what question is pulled?

Would it be easier if you had a quick scan at the
database? so you have a better understanding and then
maybe you could assist further based on what you see??

Many Thanks

James
-----Original Message-----
If this is a "one off" project, you can probably make
Click to expand...
it
work with the table
structure you have. Yes, Percent is a reserved word,
Click to expand...
so
I should have
enclosed it in brackets. Your query for question 1
Click to expand...
might
look like:
SELECT Count(Q01)/(SELECT Count(*) FROM MyTable As M2)
Click to expand...
As
PercentQ01
FROM [MyTable]
WHERE [MyTable].Q01= [Forms]![MyForm]![cbo1]
AND [MyTable].Gender = [Forms]![MyForm]![cbo2]

... where cbo1 contains True or False and cb02 contains the gender desired.
"MyTable" is the name of your table, and "MyForm" is
Click to expand...
the
name of your form.
--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Ok this I understand...

The user only wants to input the following information:

Gender
Answer to questions
and then get a percentage out of the results...

So all she wants is litrally what I have set up... 25
Questions just labled Q01 - Q25 and all the answers are
True or False and then she just wants the Gender...

Is all hwat you have said still need to be done?

Many Thanks

James

-----Original Message-----
You have two problems:

1) You cannot specify a field name with a parameter
2) Your table is badly designed - it violates First
Normal Form (repeating
group)

You can either redesign your table and use a query
similar to what I
suggested or you must write code to dynamically create
the query with the
correct field name. Your table design should look
something like:

tblQuestionnaires - QuestionnaireID, Description,
Requestor, etc.

tblQuestions - QuestionnaireID, QuestionID, QuestionType,
QuestionText

tblRespondents - RespondentID, Name, Gender, Address,
eMail, etc.

tblQuestionResponses - RespondentID, QuestionnaireID,
QuestionID, Answer

Note that the above design allows you to define an
infinite number of
questionnaires, a variable number of questions per
questionnaire, and an
infinite number or respondents, and to track the
responses to each
questionnaire/question/respondent combination. Designed
like this, it's a
simple matter to create a parameter query that joins
tblRespondents (to get
Gender) with tblQuestionResponses to count, get
percentages, and do all
sorts of analysis.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
message
Ok well it looks more like...

Gender,Q01,Q02 ETC TO Q25

There all value lists with certain values as the answers
i.e. True False

Does this affect the query Any?

Many Thanks

James

-----Original Message-----
Assuming your table looks something like:
QuestionID, Question, Answer, Gender

Then the solution will look something like:

SELECT Count(Question)/(SELECT Count(*) FROM MyTable As
M2 WHERE M2.Question
= [Forms]![MyForm]![cbo1]) As Percent
FROM MyTable
WHERE MyTable.Question = [Forms]![MyForm]![cbo1]
AND MyTable.Answer = [Forms]![MyForm]![cbo2]
AND MyTable.Gender = [Forms]![MyForm]![cbo3]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"James" <[email protected]>
Click to expand...
wrote
in
message
Hello I have a form and on this form I have 3 combo
boxes...

Lets just say there called:

cbo1
cbo2
cbo3

I would like to have a query where by it calculates
the
total number of records the criteria above specifies
and
also a percentage out of them records...

So something like 20% of 200 Records.

So for example the criteria may be:

Q01
Answer as True
Female

Then it would be like 20% of the 200 records that
have
been answered... So as the amount of records go up in
the
table so will the no of records on the form... Where
by
the percentage may change according to teh
results....

Can anyone assist?

Many Thanks

James


.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Use the SQL I gave you previously and substitute Q02, Q03, etc. for Q01 in
the original query. You might be able to create a UNION query:

SELECT "Q01" As QuestionName, Count(Q01)/(SELECT Count(*) FROM MyTable As
M2) As PercentQ01
FROM [MyTable]
WHERE [MyTable].Q01= [Forms]![MyForm]![cbo1]
AND [MyTable].Gender = [Forms]![MyForm]![cbo2]
GROUP BY "Q01"
UNION
SELECT "Q02" As QuestionName, Count(Q01)/(SELECT Count(*) FROM MyTable As
M2) As PercentQ01
FROM [MyTable]
WHERE [MyTable].Q02= [Forms]![MyForm]![cbo1]
AND [MyTable].Gender = [Forms]![MyForm]![cbo2]
UNION ... {repeat for 25 queries}

That should give you a list of question names and the relative percentages
for each. Access might tell you that this query is "too complex" with more
than 16 queries in the UNION.

This would really be much easier if your tables were "normalized" as I
originally suggested.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
James said:
Well ok then so how do I create a seperate query for each
question? Not to mention could it all be in one SQL query??

Many Thanks

James
-----Original Message-----
Yes, you would have to run a separate query for each question.

No, I do not want to see your database. I'm happy to try to answer specific
questions in these newsgroups for free, but if you want me to analyze your
database, we'll have to go offline and talk about my hourly rates. Send me
an email at JohnV at Viescas.com if you're interested in pursuing this
further.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Would I have to do teh below SQL for each Question???

The reason being is that Its totally dependant on whta the
user selects as to what question is pulled?

Would it be easier if you had a quick scan at the
database? so you have a better understanding and then
maybe you could assist further based on what you see??

Many Thanks

James

-----Original Message-----
If this is a "one off" project, you can probably make it
work with the table
structure you have. Yes, Percent is a reserved word, so
I should have
enclosed it in brackets. Your query for question 1 might
look like:

SELECT Count(Q01)/(SELECT Count(*) FROM MyTable As M2) As
PercentQ01
FROM [MyTable]
WHERE [MyTable].Q01= [Forms]![MyForm]![cbo1]
AND [MyTable].Gender = [Forms]![MyForm]![cbo2]

... where cbo1 contains True or False and cb02 contains
the gender desired.
"MyTable" is the name of your table, and "MyForm" is the
name of your form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
message
Ok this I understand...

The user only wants to input the following information:

Gender
Answer to questions
and then get a percentage out of the results...

So all she wants is litrally what I have set up... 25
Questions just labled Q01 - Q25 and all the answers are
True or False and then she just wants the Gender...

Is all hwat you have said still need to be done?

Many Thanks

James

-----Original Message-----
You have two problems:

1) You cannot specify a field name with a parameter
2) Your table is badly designed - it violates First
Normal Form (repeating
group)

You can either redesign your table and use a query
similar to what I
suggested or you must write code to dynamically create
the query with the
correct field name. Your table design should look
something like:

tblQuestionnaires - QuestionnaireID, Description,
Requestor, etc.

tblQuestions - QuestionnaireID, QuestionID,
QuestionType,
QuestionText

tblRespondents - RespondentID, Name, Gender, Address,
eMail, etc.

tblQuestionResponses - RespondentID, QuestionnaireID,
QuestionID, Answer

Note that the above design allows you to define an
infinite number of
questionnaires, a variable number of questions per
questionnaire, and an
infinite number or respondents, and to track the
responses to each
questionnaire/question/respondent combination.
Designed
like this, it's a
simple matter to create a parameter query that joins
tblRespondents (to get
Gender) with tblQuestionResponses to count, get
percentages, and do all
sorts of analysis.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
message
Ok well it looks more like...

Gender,Q01,Q02 ETC TO Q25

There all value lists with certain values as the
answers
i.e. True False

Does this affect the query Any?

Many Thanks

James

-----Original Message-----
Assuming your table looks something like:
QuestionID, Question, Answer, Gender

Then the solution will look something like:

SELECT Count(Question)/(SELECT Count(*) FROM
MyTable As
M2 WHERE M2.Question
= [Forms]![MyForm]![cbo1]) As Percent
FROM MyTable
WHERE MyTable.Question = [Forms]![MyForm]![cbo1]
AND MyTable.Answer = [Forms]![MyForm]![cbo2]
AND MyTable.Gender = [Forms]![MyForm]![cbo3]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
in
message
Hello I have a form and on this form I have 3
combo
boxes...

Lets just say there called:

cbo1
cbo2
cbo3

I would like to have a query where by it
calculates
the
total number of records the criteria above
specifies
and
also a percentage out of them records...

So something like 20% of 200 Records.

So for example the criteria may be:

Q01
Answer as True
Female

Then it would be like 20% of the 200 records that
have
been answered... So as the amount of records go
up in
the
table so will the no of records on the form...
Where
by
the percentage may change according to teh
results....

Can anyone assist?

Many Thanks

James


.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
You might want to look at how a survey can be normalized. There is a demo
"At Your Survey" at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP


John Viescas said:
Use the SQL I gave you previously and substitute Q02, Q03, etc. for Q01 in
the original query. You might be able to create a UNION query:

SELECT "Q01" As QuestionName, Count(Q01)/(SELECT Count(*) FROM MyTable As
M2) As PercentQ01
FROM [MyTable]
WHERE [MyTable].Q01= [Forms]![MyForm]![cbo1]
AND [MyTable].Gender = [Forms]![MyForm]![cbo2]
GROUP BY "Q01"
UNION
SELECT "Q02" As QuestionName, Count(Q01)/(SELECT Count(*) FROM MyTable As
M2) As PercentQ01
FROM [MyTable]
WHERE [MyTable].Q02= [Forms]![MyForm]![cbo1]
AND [MyTable].Gender = [Forms]![MyForm]![cbo2]
UNION ... {repeat for 25 queries}

That should give you a list of question names and the relative percentages
for each. Access might tell you that this query is "too complex" with more
than 16 queries in the UNION.

This would really be much easier if your tables were "normalized" as I
originally suggested.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
James said:
Well ok then so how do I create a seperate query for each
question? Not to mention could it all be in one SQL query??

Many Thanks

James
-----Original Message-----
Yes, you would have to run a separate query for each question.

No, I do not want to see your database. I'm happy to try to answer specific
questions in these newsgroups for free, but if you want me to analyze your
database, we'll have to go offline and talk about my hourly rates. Send me
an email at JohnV at Viescas.com if you're interested in pursuing this
further.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Would I have to do teh below SQL for each Question???

The reason being is that Its totally dependant on whta the
user selects as to what question is pulled?

Would it be easier if you had a quick scan at the
database? so you have a better understanding and then
maybe you could assist further based on what you see??

Many Thanks

James

-----Original Message-----
If this is a "one off" project, you can probably make it
work with the table
structure you have. Yes, Percent is a reserved word, so
I should have
enclosed it in brackets. Your query for question 1 might
look like:

SELECT Count(Q01)/(SELECT Count(*) FROM MyTable As M2) As
PercentQ01
FROM [MyTable]
WHERE [MyTable].Q01= [Forms]![MyForm]![cbo1]
AND [MyTable].Gender = [Forms]![MyForm]![cbo2]

... where cbo1 contains True or False and cb02 contains
the gender desired.
"MyTable" is the name of your table, and "MyForm" is the
name of your form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
message
Ok this I understand...

The user only wants to input the following information:

Gender
Answer to questions
and then get a percentage out of the results...

So all she wants is litrally what I have set up... 25
Questions just labled Q01 - Q25 and all the answers are
True or False and then she just wants the Gender...

Is all hwat you have said still need to be done?

Many Thanks

James

-----Original Message-----
You have two problems:

1) You cannot specify a field name with a parameter
2) Your table is badly designed - it violates First
Normal Form (repeating
group)

You can either redesign your table and use a query
similar to what I
suggested or you must write code to dynamically create
the query with the
correct field name. Your table design should look
something like:

tblQuestionnaires - QuestionnaireID, Description,
Requestor, etc.

tblQuestions - QuestionnaireID, QuestionID,
QuestionType,
QuestionText

tblRespondents - RespondentID, Name, Gender, Address,
eMail, etc.

tblQuestionResponses - RespondentID, QuestionnaireID,
QuestionID, Answer

Note that the above design allows you to define an
infinite number of
questionnaires, a variable number of questions per
questionnaire, and an
infinite number or respondents, and to track the
responses to each
questionnaire/question/respondent combination.
Designed
like this, it's a
simple matter to create a parameter query that joins
tblRespondents (to get
Gender) with tblQuestionResponses to count, get
percentages, and do all
sorts of analysis.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
message
Ok well it looks more like...

Gender,Q01,Q02 ETC TO Q25

There all value lists with certain values as the
answers
i.e. True False

Does this affect the query Any?

Many Thanks

James

-----Original Message-----
Assuming your table looks something like:
QuestionID, Question, Answer, Gender

Then the solution will look something like:

SELECT Count(Question)/(SELECT Count(*) FROM
MyTable As
M2 WHERE M2.Question
= [Forms]![MyForm]![cbo1]) As Percent
FROM MyTable
WHERE MyTable.Question = [Forms]![MyForm]![cbo1]
AND MyTable.Answer = [Forms]![MyForm]![cbo2]
AND MyTable.Gender = [Forms]![MyForm]![cbo3]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
in
message
Hello I have a form and on this form I have 3
combo
boxes...

Lets just say there called:

cbo1
cbo2
cbo3

I would like to have a query where by it
calculates
the
total number of records the criteria above
specifies
and
also a percentage out of them records...

So something like 20% of 200 Records.

So for example the criteria may be:

Q01
Answer as True
Female

Then it would be like 20% of the 200 records that
have
been answered... So as the amount of records go
up in
the
table so will the no of records on the form...
Where
by
the percentage may change according to teh
results....

Can anyone assist?

Many Thanks

James


.



.



.



.
Click to expand...
Click to expand...
Click to expand...
 
Right ok then I am set-up 3 tables now... one for the
Question's one for the Answers and one for the gender...

So how do I create a rational set of tables on what I
have... I have a question ID, Answer ID and GenderID
Fields in each as well.

Ok now lets get this made easy then...

Many Thanks

James
-----Original Message-----
You might want to look at how a survey can be normalized. There is a demo
"At Your Survey" at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hook om,Duane.

--
Duane Hookom
MS Access MVP


John Viescas said:
Use the SQL I gave you previously and substitute Q02, Q03, etc. for Q01 in
the original query. You might be able to create a UNION query:

SELECT "Q01" As QuestionName, Count(Q01)/(SELECT Count (*) FROM MyTable As
M2) As PercentQ01
FROM [MyTable]
WHERE [MyTable].Q01= [Forms]![MyForm]![cbo1]
AND [MyTable].Gender = [Forms]![MyForm]![cbo2]
GROUP BY "Q01"
UNION
SELECT "Q02" As QuestionName, Count(Q01)/(SELECT Count (*) FROM MyTable As
M2) As PercentQ01
FROM [MyTable]
WHERE [MyTable].Q02= [Forms]![MyForm]![cbo1]
AND [MyTable].Gender = [Forms]![MyForm]![cbo2]
UNION ... {repeat for 25 queries}

That should give you a list of question names and the relative percentages
for each. Access might tell you that this query
Click to expand...
is "too complex" with
more
than 16 queries in the UNION.

This would really be much easier if your tables were "normalized" as I
originally suggested.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Well ok then so how do I create a seperate query for each
question? Not to mention could it all be in one SQL query??

Many Thanks

James
-----Original Message-----
Yes, you would have to run a separate query for each
question.

No, I do not want to see your database. I'm happy to try
to answer specific
questions in these newsgroups for free, but if you want
me to analyze your
database, we'll have to go offline and talk about my
hourly rates. Send me
an email at JohnV at Viescas.com if you're interested in
pursuing this
further.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
message
Would I have to do teh below SQL for each Question???

The reason being is that Its totally dependant on whta
the
user selects as to what question is pulled?

Would it be easier if you had a quick scan at the
database? so you have a better understanding and then
maybe you could assist further based on what you see??

Many Thanks

James

-----Original Message-----
If this is a "one off" project, you can probably make
it
work with the table
structure you have. Yes, Percent is a reserved word,
so
I should have
enclosed it in brackets. Your query for question 1
might
look like:

SELECT Count(Q01)/(SELECT Count(*) FROM MyTable As M2)
As
PercentQ01
FROM [MyTable]
WHERE [MyTable].Q01= [Forms]![MyForm]![cbo1]
AND [MyTable].Gender = [Forms]![MyForm]![cbo2]

... where cbo1 contains True or False and cb02 contains
the gender desired.
"MyTable" is the name of your table, and "MyForm" is
the
name of your form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
message
Ok this I understand...

The user only wants to input the following
information:

Gender
Answer to questions
and then get a percentage out of the results...

So all she wants is litrally what I have set up... 25
Questions just labled Q01 - Q25 and all the answers
are
True or False and then she just wants the Gender...

Is all hwat you have said still need to be done?

Many Thanks

James

-----Original Message-----
You have two problems:

1) You cannot specify a field name with a parameter
2) Your table is badly designed - it violates First
Normal Form (repeating
group)

You can either redesign your table and use a query
similar to what I
suggested or you must write code to dynamically
create
the query with the
correct field name. Your table design should look
something like:

tblQuestionnaires - QuestionnaireID, Description,
Requestor, etc.

tblQuestions - QuestionnaireID, QuestionID,
QuestionType,
QuestionText

tblRespondents - RespondentID, Name, Gender,
Address,
eMail, etc.

tblQuestionResponses - RespondentID,
QuestionnaireID,
QuestionID, Answer

Note that the above design allows you to define an
infinite number of
questionnaires, a variable number of questions per
questionnaire, and an
infinite number or respondents, and to track the
responses to each
questionnaire/question/respondent combination.
Designed
like this, it's a
simple matter to create a parameter query that joins
tblRespondents (to get
Gender) with tblQuestionResponses to count, get
percentages, and do all
sorts of analysis.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
in
message
Ok well it looks more like...

Gender,Q01,Q02 ETC TO Q25

There all value lists with certain values as the
answers
i.e. True False

Does this affect the query Any?

Many Thanks

James

-----Original Message-----
Assuming your table looks something like:
QuestionID, Question, Answer, Gender

Then the solution will look something like:

SELECT Count(Question)/(SELECT Count(*) FROM
MyTable As
M2 WHERE M2.Question
= [Forms]![MyForm]![cbo1]) As Percent
FROM MyTable
WHERE MyTable.Question = [Forms]![MyForm]! [cbo1]
AND MyTable.Answer = [Forms]![MyForm]![cbo2]
AND MyTable.Gender = [Forms]![MyForm]![cbo3]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"James"
Click to expand...
wrote
in
message
[email protected]...
Hello I have a form and on this form I have 3
combo
boxes...

Lets just say there called:

cbo1
cbo2
cbo3

I would like to have a query where by it
calculates
the
total number of records the criteria above
specifies
and
also a percentage out of them records...

So something like 20% of 200 Records.

So for example the criteria may be:

Q01
Answer as True
Female

Then it would be like 20% of the 200 records
that
have
been answered... So as the amount of records go
up in
the
table so will the no of records on the form...
Where
by
the percentage may change according to teh
results....

Can anyone assist?

Many Thanks

James


.



.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
I also have a tblMain and I was wondering how you wish for
me to set this up so that it holds all the data...

Then we can create this query the easy way :)

Thanks

James
-----Original Message-----
You might want to look at how a survey can be normalized. There is a demo
"At Your Survey" at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hook om,Duane.

--
Duane Hookom
MS Access MVP


John Viescas said:
Use the SQL I gave you previously and substitute Q02, Q03, etc. for Q01 in
the original query. You might be able to create a UNION query:

SELECT "Q01" As QuestionName, Count(Q01)/(SELECT Count (*) FROM MyTable As
M2) As PercentQ01
FROM [MyTable]
WHERE [MyTable].Q01= [Forms]![MyForm]![cbo1]
AND [MyTable].Gender = [Forms]![MyForm]![cbo2]
GROUP BY "Q01"
UNION
SELECT "Q02" As QuestionName, Count(Q01)/(SELECT Count (*) FROM MyTable As
M2) As PercentQ01
FROM [MyTable]
WHERE [MyTable].Q02= [Forms]![MyForm]![cbo1]
AND [MyTable].Gender = [Forms]![MyForm]![cbo2]
UNION ... {repeat for 25 queries}

That should give you a list of question names and the relative percentages
for each. Access might tell you that this query
Click to expand...
is "too complex" with
more
than 16 queries in the UNION.

This would really be much easier if your tables were "normalized" as I
originally suggested.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Well ok then so how do I create a seperate query for each
question? Not to mention could it all be in one SQL query??

Many Thanks

James
-----Original Message-----
Yes, you would have to run a separate query for each
question.

No, I do not want to see your database. I'm happy to try
to answer specific
questions in these newsgroups for free, but if you want
me to analyze your
database, we'll have to go offline and talk about my
hourly rates. Send me
an email at JohnV at Viescas.com if you're interested in
pursuing this
further.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
message
Would I have to do teh below SQL for each Question???

The reason being is that Its totally dependant on whta
the
user selects as to what question is pulled?

Would it be easier if you had a quick scan at the
database? so you have a better understanding and then
maybe you could assist further based on what you see??

Many Thanks

James

-----Original Message-----
If this is a "one off" project, you can probably make
it
work with the table
structure you have. Yes, Percent is a reserved word,
so
I should have
enclosed it in brackets. Your query for question 1
might
look like:

SELECT Count(Q01)/(SELECT Count(*) FROM MyTable As M2)
As
PercentQ01
FROM [MyTable]
WHERE [MyTable].Q01= [Forms]![MyForm]![cbo1]
AND [MyTable].Gender = [Forms]![MyForm]![cbo2]

... where cbo1 contains True or False and cb02 contains
the gender desired.
"MyTable" is the name of your table, and "MyForm" is
the
name of your form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
message
Ok this I understand...

The user only wants to input the following
information:

Gender
Answer to questions
and then get a percentage out of the results...

So all she wants is litrally what I have set up... 25
Questions just labled Q01 - Q25 and all the answers
are
True or False and then she just wants the Gender...

Is all hwat you have said still need to be done?

Many Thanks

James

-----Original Message-----
You have two problems:

1) You cannot specify a field name with a parameter
2) Your table is badly designed - it violates First
Normal Form (repeating
group)

You can either redesign your table and use a query
similar to what I
suggested or you must write code to dynamically
create
the query with the
correct field name. Your table design should look
something like:

tblQuestionnaires - QuestionnaireID, Description,
Requestor, etc.

tblQuestions - QuestionnaireID, QuestionID,
QuestionType,
QuestionText

tblRespondents - RespondentID, Name, Gender,
Address,
eMail, etc.

tblQuestionResponses - RespondentID,
QuestionnaireID,
QuestionID, Answer

Note that the above design allows you to define an
infinite number of
questionnaires, a variable number of questions per
questionnaire, and an
infinite number or respondents, and to track the
responses to each
questionnaire/question/respondent combination.
Designed
like this, it's a
simple matter to create a parameter query that joins
tblRespondents (to get
Gender) with tblQuestionResponses to count, get
percentages, and do all
sorts of analysis.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
in
message
Ok well it looks more like...

Gender,Q01,Q02 ETC TO Q25

There all value lists with certain values as the
answers
i.e. True False

Does this affect the query Any?

Many Thanks

James

-----Original Message-----
Assuming your table looks something like:
QuestionID, Question, Answer, Gender

Then the solution will look something like:

SELECT Count(Question)/(SELECT Count(*) FROM
MyTable As
M2 WHERE M2.Question
= [Forms]![MyForm]![cbo1]) As Percent
FROM MyTable
WHERE MyTable.Question = [Forms]![MyForm]! [cbo1]
AND MyTable.Answer = [Forms]![MyForm]![cbo2]
AND MyTable.Gender = [Forms]![MyForm]![cbo3]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"James"
Click to expand...
wrote
in
message
[email protected]...
Hello I have a form and on this form I have 3
combo
boxes...

Lets just say there called:

cbo1
cbo2
cbo3

I would like to have a query where by it
calculates
the
total number of records the criteria above
specifies
and
also a percentage out of them records...

So something like 20% of 200 Records.

So for example the criteria may be:

Q01
Answer as True
Female

Then it would be like 20% of the 200 records
that
have
been answered... So as the amount of records go
up in
the
table so will the no of records on the form...
Where
by
the percentage may change according to teh
results....

Can anyone assist?

Many Thanks

James


.



.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
What is the complete field layout for your old table? Do you have the new
ones set up as I suggested? List the field names of all the tables, and
I'll give you the SQL you need to "move" the data from the old one to the
new ones.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
James said:
Right ok then I am set-up 3 tables now... one for the
Question's one for the Answers and one for the gender...

So how do I create a rational set of tables on what I
have... I have a question ID, Answer ID and GenderID
Fields in each as well.

Ok now lets get this made easy then...

Many Thanks

James
-----Original Message-----
You might want to look at how a survey can be normalized. There is a demo
"At Your Survey" at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hook om,Duane.

--
Duane Hookom
MS Access MVP


John Viescas said:
Use the SQL I gave you previously and substitute Q02, Q03, etc. for Q01 in
the original query. You might be able to create a UNION query:

SELECT "Q01" As QuestionName, Count(Q01)/(SELECT Count (*) FROM MyTable As
M2) As PercentQ01
FROM [MyTable]
WHERE [MyTable].Q01= [Forms]![MyForm]![cbo1]
AND [MyTable].Gender = [Forms]![MyForm]![cbo2]
GROUP BY "Q01"
UNION
SELECT "Q02" As QuestionName, Count(Q01)/(SELECT Count (*) FROM MyTable As
M2) As PercentQ01
FROM [MyTable]
WHERE [MyTable].Q02= [Forms]![MyForm]![cbo1]
AND [MyTable].Gender = [Forms]![MyForm]![cbo2]
UNION ... {repeat for 25 queries}

That should give you a list of question names and the relative percentages
for each. Access might tell you that this query
Click to expand...
is "too complex" with
more
than 16 queries in the UNION.

This would really be much easier if your tables were "normalized" as I
originally suggested.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Well ok then so how do I create a seperate query for each
question? Not to mention could it all be in one SQL query??

Many Thanks

James
-----Original Message-----
Yes, you would have to run a separate query for each
question.

No, I do not want to see your database. I'm happy to try
to answer specific
questions in these newsgroups for free, but if you want
me to analyze your
database, we'll have to go offline and talk about my
hourly rates. Send me
an email at JohnV at Viescas.com if you're interested in
pursuing this
further.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
message
Would I have to do teh below SQL for each Question???

The reason being is that Its totally dependant on whta
the
user selects as to what question is pulled?

Would it be easier if you had a quick scan at the
database? so you have a better understanding and then
maybe you could assist further based on what you see??

Many Thanks

James

-----Original Message-----
If this is a "one off" project, you can probably make
it
work with the table
structure you have. Yes, Percent is a reserved word,
so
I should have
enclosed it in brackets. Your query for question 1
might
look like:

SELECT Count(Q01)/(SELECT Count(*) FROM MyTable As M2)
As
PercentQ01
FROM [MyTable]
WHERE [MyTable].Q01= [Forms]![MyForm]![cbo1]
AND [MyTable].Gender = [Forms]![MyForm]![cbo2]

... where cbo1 contains True or False and cb02 contains
the gender desired.
"MyTable" is the name of your table, and "MyForm" is
the
name of your form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
message
Ok this I understand...

The user only wants to input the following
information:

Gender
Answer to questions
and then get a percentage out of the results...

So all she wants is litrally what I have set up... 25
Questions just labled Q01 - Q25 and all the answers
are
True or False and then she just wants the Gender...

Is all hwat you have said still need to be done?

Many Thanks

James

-----Original Message-----
You have two problems:

1) You cannot specify a field name with a parameter
2) Your table is badly designed - it violates First
Normal Form (repeating
group)

You can either redesign your table and use a query
similar to what I
suggested or you must write code to dynamically
create
the query with the
correct field name. Your table design should look
something like:

tblQuestionnaires - QuestionnaireID, Description,
Requestor, etc.

tblQuestions - QuestionnaireID, QuestionID,
QuestionType,
QuestionText

tblRespondents - RespondentID, Name, Gender,
Address,
eMail, etc.

tblQuestionResponses - RespondentID,
QuestionnaireID,
QuestionID, Answer

Note that the above design allows you to define an
infinite number of
questionnaires, a variable number of questions per
questionnaire, and an
infinite number or respondents, and to track the
responses to each
questionnaire/question/respondent combination.
Designed
like this, it's a
simple matter to create a parameter query that joins
tblRespondents (to get
Gender) with tblQuestionResponses to count, get
percentages, and do all
sorts of analysis.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
in
message
Ok well it looks more like...

Gender,Q01,Q02 ETC TO Q25

There all value lists with certain values as the
answers
i.e. True False

Does this affect the query Any?

Many Thanks

James

-----Original Message-----
Assuming your table looks something like:
QuestionID, Question, Answer, Gender

Then the solution will look something like:

SELECT Count(Question)/(SELECT Count(*) FROM
MyTable As
M2 WHERE M2.Question
= [Forms]![MyForm]![cbo1]) As Percent
FROM MyTable
WHERE MyTable.Question = [Forms]![MyForm]! [cbo1]
AND MyTable.Answer = [Forms]![MyForm]![cbo2]
AND MyTable.Gender = [Forms]![MyForm]![cbo3]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"James"
Click to expand...
wrote
in
message
[email protected]...
Hello I have a form and on this form I have 3
combo
boxes...

Lets just say there called:

cbo1
cbo2
cbo3

I would like to have a query where by it
calculates
the
total number of records the criteria above
specifies
and
also a percentage out of them records...

So something like 20% of 200 Records.

So for example the criteria may be:

Q01
Answer as True
Female

Then it would be like 20% of the 200 records
that
have
been answered... So as the amount of records go
up in
the
table so will the no of records on the form...
Where
by
the percentage may change according to teh
results....

Can anyone assist?

Many Thanks

James


.



.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Right ok I have 4 tables now....

Table1 - tblGender

Fields - GenderID (AutoNumber)
Gender (Text)

Data - Male
Female

Table2 - tblAnswer

Fields - AnswerID (AutoNumber)
Answer (Text)

Data - True
False

Table3 - tblQuestion

Fields - QuestionID (AutoNumber)
Question (Text)

Data - Q01
Q02
ETC (Until Q25)

Table4 - tblMain (Where the data will be stored)

Fields - Are you Male or Female
Q01
Q02
ETC (Until Q25)

Data - None at the current Moment


Ok Table4 is my old table which I would like all the data
to be stored in. Table4's fields are all value lists which
have the appropriate data in for example (Q01 - Q25 have
True and False values, where by Are you male or female has
the values of Male or Female).

I have no relationships as of yet.

Please could you assist me on my query now and on my
relationships? Also is this much easier to work with?

Many Thanks Again

James

-----Original Message-----
What is the complete field layout for your old table? Do you have the new
ones set up as I suggested? List the field names of all the tables, and
I'll give you the SQL you need to "move" the data from the old one to the
new ones.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Right ok then I am set-up 3 tables now... one for the
Question's one for the Answers and one for the gender...

So how do I create a rational set of tables on what I
have... I have a question ID, Answer ID and GenderID
Fields in each as well.

Ok now lets get this made easy then...

Many Thanks

James
-----Original Message-----
You might want to look at how a survey can be
Click to expand...
normalized.
There is a demo
"At Your Survey" at
Click to expand...
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hook
om,Duane.

--
Duane Hookom
MS Access MVP


Use the SQL I gave you previously and substitute Q02, Q03, etc. for Q01 in
the original query. You might be able to create a UNION query:

SELECT "Q01" As QuestionName, Count(Q01)/(SELECT
Click to expand...
Count
(*) FROM MyTable As
M2) As PercentQ01
FROM [MyTable]
WHERE [MyTable].Q01= [Forms]![MyForm]![cbo1]
AND [MyTable].Gender = [Forms]![MyForm]![cbo2]
GROUP BY "Q01"
UNION
SELECT "Q02" As QuestionName, Count(Q01)/(SELECT
Click to expand...
Count
(*) FROM MyTable As
M2) As PercentQ01
FROM [MyTable]
WHERE [MyTable].Q02= [Forms]![MyForm]![cbo1]
AND [MyTable].Gender = [Forms]![MyForm]![cbo2]
UNION ... {repeat for 25 queries}

That should give you a list of question names and the relative percentages
for each. Access might tell you that this query is "too complex" with
more
than 16 queries in the UNION.

This would really be much easier if your tables were "normalized" as I
originally suggested.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"James" <[email protected]> wrote
Click to expand...
in
message
Well ok then so how do I create a seperate query
Click to expand...
for
each
question? Not to mention could it all be in one SQL query??

Many Thanks

James
-----Original Message-----
Yes, you would have to run a separate query for each
question.

No, I do not want to see your database. I'm happy to try
to answer specific
questions in these newsgroups for free, but if you want
me to analyze your
database, we'll have to go offline and talk about my
hourly rates. Send me
an email at JohnV at Viescas.com if you're interested in
pursuing this
further.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"james" <[email protected]>
Click to expand...
wrote
in
message
Would I have to do teh below SQL for each Question???

The reason being is that Its totally dependant
Click to expand...
on
whta
the
user selects as to what question is pulled?

Would it be easier if you had a quick scan at the
database? so you have a better understanding and then
maybe you could assist further based on what you see??

Many Thanks

James

-----Original Message-----
If this is a "one off" project, you can
Click to expand...
probably
make
it
work with the table
structure you have. Yes, Percent is a reserved word,
so
I should have
enclosed it in brackets. Your query for
Click to expand...
question
1
might
look like:

SELECT Count(Q01)/(SELECT Count(*) FROM MyTable As M2)
As
PercentQ01
FROM [MyTable]
WHERE [MyTable].Q01= [Forms]![MyForm]![cbo1]
AND [MyTable].Gender = [Forms]![MyForm]![cbo2]

... where cbo1 contains True or False and cb02 contains
the gender desired.
"MyTable" is the name of your table,
Click to expand...
and "MyForm"
is
the
name of your form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
message
Ok this I understand...

The user only wants to input the following
information:

Gender
Answer to questions
and then get a percentage out of the results...

So all she wants is litrally what I have set up... 25
Questions just labled Q01 - Q25 and all the answers
are
True or False and then she just wants the Gender...

Is all hwat you have said still need to be done?

Many Thanks

James

-----Original Message-----
You have two problems:

1) You cannot specify a field name with a parameter
2) Your table is badly designed - it
Click to expand...
violates
First
Normal Form (repeating
group)

You can either redesign your table and use a query
similar to what I
suggested or you must write code to dynamically
create
the query with the
correct field name. Your table design
Click to expand...
should
look
something like:

tblQuestionnaires - QuestionnaireID, Description,
Requestor, etc.

tblQuestions - QuestionnaireID, QuestionID,
QuestionType,
QuestionText

tblRespondents - RespondentID, Name, Gender,
Address,
eMail, etc.

tblQuestionResponses - RespondentID,
QuestionnaireID,
QuestionID, Answer

Note that the above design allows you to define an
infinite number of
questionnaires, a variable number of
Click to expand...
questions
per
questionnaire, and an
infinite number or respondents, and to track the
responses to each
questionnaire/question/respondent combination.
Designed
like this, it's a
simple matter to create a parameter query
Click to expand...
that
joins
tblRespondents (to get
Gender) with tblQuestionResponses to count, get
percentages, and do all
sorts of analysis.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"James"
Click to expand...
Click to expand...
wrote
in
message
[email protected]...
Ok well it looks more like...

Gender,Q01,Q02 ETC TO Q25

There all value lists with certain values
Click to expand...
as
the
answers
i.e. True False

Does this affect the query Any?

Many Thanks

James

-----Original Message-----
Assuming your table looks something like:
QuestionID, Question, Answer, Gender

Then the solution will look something like:

SELECT Count(Question)/(SELECT Count(*) FROM
MyTable As
M2 WHERE M2.Question
= [Forms]![MyForm]![cbo1]) As Percent
FROM MyTable
WHERE MyTable.Question = [Forms]!
Click to expand...
[MyForm]!
[cbo1]
AND MyTable.Answer = [Forms]![MyForm]! [cbo2]
AND MyTable.Gender = [Forms]![MyForm]! [cbo3]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"James"
wrote
in
message
news:e9fe01c40c01$7589c400
Click to expand...
[email protected]...
Hello I have a form and on this form I have 3
combo
boxes...

Lets just say there called:

cbo1
cbo2
cbo3

I would like to have a query where by it
calculates
the
total number of records the criteria above
specifies
and
also a percentage out of them records...

So something like 20% of 200 Records.

So for example the criteria may be:

Q01
Answer as True
Female

Then it would be like 20% of the 200 records
that
have
been answered... So as the amount of records go
up in
the
table so will the no of records on the form...
Where
by
the percentage may change according to teh
results....

Can anyone assist?

Many Thanks

James


.



.



.



.





.
Click to expand...
Click to expand...


.
Click to expand...
 
Oh, so you have no data yet? Table4 is still dead wrong. The fields Q01,
Q01, ..., Q25 form a repeating group that is absolutely incorrect in a
relational table design. Table1 and Table2 are useless "lookup" tables.
Table3 looks a bit like the "tblQuestions" table that I originally
suggested:

tblQuestions - QuestionID, QuestionType, QuestionText

tblRespondents - RespondentID, Name, Gender, Address, eMail, etc.

tblQuestionResponses - RespondentID, QuestionID, Answer

The correct way to do this is to have each Respondent log in to the
database - perhaps creating a new record in tblRespondents when they start
the application. Next take them to a "Wizard" form that walks them through
the questions saved in tblQuestions one at a time. The form should write
one new row to tblQuestionReponses for each answer. After you collect the
data in tblQuestionReponses, it will be easy to "slice and dice" the data
with queries to report all sorts of statistics.


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Ok but the user has asked me for a database where by all
they want is a form with Q01...Q25 with the answers of
True or False and to know if the person if Male or Female
because they dont want detailed information about the
person because of there job role...

So would me tables be sufficiant now? If not then please
let me know how to set them up based on the above
information...

Then I just need to get this query to total them as a
percentage but we will sort the tables out first :)

Many Thanks

James
 
No, your table would still be wrong. If you don't need any other info about
the Respondent other than Gender, you could cheat and put Gender in
tblQuestionResponses.

Let me lay it out for you so that you can better understand the problem.

Your table:
Gender Q01 Q02 Q03 Q04 .....
M T T F F
M F T T T
F T F F F

My table:
RepondentID Gender Question Answer
1 M Q01 T
1 M Q02 T
1 M Q03 F
1 M Q04 F
....
2 M Q01 F
2 M Q02 T
2 M Q03 T
2 M Q04 T
....
3 F Q01 T
3 F Q02 F
3 F Q03 F
3 F Q04 F


See the difference? I can now create one query to give me the totals and
percentages for all questions (Group By Question). If the user suddenly
decides to add questions 26-30, no problem. I don't have to change the
table design.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
James,
Did you ever download and look at the At Your Survey application? I think
this would meet your needs without having to create or modify a table, form,
report, query, etc. The table structure is similar to what John is
suggesting.
 
Back
Top