Form and SQL Help please

[Advo]

Basically needing a bit of help with MS Access, and a few forms im
doing.

Ive made a form, which is now linked with an imported MS SQL
database/table.

I need to display a result in a label. I've already done this (a bit of
it) to display in a drop down box, but dont think I can in a label(need
to though, or atleast have the default value showing in the drop down
box, rather than having to click it first.

The queries I need to perform in one go are:

A) SELECT count(*) AS TOTAL FROM questionnaire_samplegroup3;
B) SELECT count(*) AS YES FROM questionnaire_samplegroup3 WHERE
question1 = 'Yes';
C) SELECT count(*) AS NO FROM questionnaire_samplegroup3 WHERE
question1 = 'No';

So these need to be like in the same row source? dont think that will
work, and then basically perform the calculations

so that I can work on the percentage of the results of each of these
queries.

for example:

A) 147
B) 4
C) 143

A) Changes all the time, well, they all do so cant just work out the %
once, it need to auto calculate as above.

I thought of something like this:

"

SELECT SUM(YES / TOTAL * 1000), count(*) AS TOTAL, (SELECT count(*) AS
YES FROM questionnaire_samplegroup3 WHERE question1 = 'Yes') FROM
questionnaire_samplegroup3;


"

Here its not apreciating the subquery..



Who would think i've got a degree in this stuff


It's been ages since ive done it, even longer since I used access in
this depth, and even longer since i slept. Helpen moi

 

[Graham Mandeno]

Hi Advo

You can get all three values in one query as follows:

Select count(*) as TOTAL, Abs(Sum(question1='Yes')) as YES,
Abs(Sum(question1='No')) as NO from questionnaire_samplegroup3;

I'm a bit worried about the fact that you have a field named "question1".
Does this mean you have a "question2" and "question3" etc also?

This structure is not normalised and will make it difficult to process
results.

A much better structure would involve three tables: Questionnaires,
Questions and Responses

The Responses table has just three fields: QuestionnaireID, QuestionID, and
Response.

You can then create a single GROUP BY or crosstab query to return the total
responses, and the number with each answer, for each question.

 

[Advo]

Yea, its not very normalised at all really, but i didnt have much time
to set it up, and its only a one off thing that will be deleted soon.
That gets me the count etc, but how can i get the % of that rather than
just the count value?

Thanks for the help though!

Hi Advo

You can get all three values in one query as follows:

Select count(*) as TOTAL, Abs(Sum(question1='Yes')) as YES,
Abs(Sum(question1='No')) as NO from questionnaire_samplegroup3;

I'm a bit worried about the fact that you have a field named "question1".
Does this mean you have a "question2" and "question3" etc also?

This structure is not normalised and will make it difficult to process
results.

A much better structure would involve three tables: Questionnaires,
Questions and Responses

The Responses table has just three fields: QuestionnaireID, QuestionID, and
Response.

You can then create a single GROUP BY or crosstab query to return the total
responses, and the number with each answer, for each question.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Basically needing a bit of help with MS Access, and a few forms im
doing.

Ive made a form, which is now linked with an imported MS SQL
database/table.

I need to display a result in a label. I've already done this (a bit of
it) to display in a drop down box, but dont think I can in a label(need
to though, or atleast have the default value showing in the drop down
box, rather than having to click it first.

The queries I need to perform in one go are:

A) SELECT count(*) AS TOTAL FROM questionnaire_samplegroup3;
B) SELECT count(*) AS YES FROM questionnaire_samplegroup3 WHERE
question1 = 'Yes';
C) SELECT count(*) AS NO FROM questionnaire_samplegroup3 WHERE
question1 = 'No';

So these need to be like in the same row source? dont think that will
work, and then basically perform the calculations

so that I can work on the percentage of the results of each of these
queries.

for example:

A) 147
B) 4
C) 143

A) Changes all the time, well, they all do so cant just work out the %
once, it need to auto calculate as above.

I thought of something like this:

"

SELECT SUM(YES / TOTAL * 1000), count(*) AS TOTAL, (SELECT count(*) AS
YES FROM questionnaire_samplegroup3 WHERE question1 = 'Yes') FROM
questionnaire_samplegroup3;


"

Here its not apreciating the subquery..



Who would think i've got a degree in this stuff


It's been ages since ive done it, even longer since I used access in
this depth, and even longer since i slept. Helpen moi

 

[Graham Mandeno]

Hi Advo

The percents can be calculated from YES/Total*100 and NO/Total*100.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Yea, its not very normalised at all really, but i didnt have much time
to set it up, and its only a one off thing that will be deleted soon.
That gets me the count etc, but how can i get the % of that rather than
just the count value?

Thanks for the help though!

Hi Advo

You can get all three values in one query as follows:

Select count(*) as TOTAL, Abs(Sum(question1='Yes')) as YES,
Abs(Sum(question1='No')) as NO from questionnaire_samplegroup3;

I'm a bit worried about the fact that you have a field named "question1".
Does this mean you have a "question2" and "question3" etc also?

This structure is not normalised and will make it difficult to process
results.

A much better structure would involve three tables: Questionnaires,
Questions and Responses

The Responses table has just three fields: QuestionnaireID, QuestionID,
and
Response.

You can then create a single GROUP BY or crosstab query to return the
total
responses, and the number with each answer, for each question.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Basically needing a bit of help with MS Access, and a few forms im
doing.

Ive made a form, which is now linked with an imported MS SQL
database/table.

I need to display a result in a label. I've already done this (a bit of
it) to display in a drop down box, but dont think I can in a label(need
to though, or atleast have the default value showing in the drop down
box, rather than having to click it first.

The queries I need to perform in one go are:

A) SELECT count(*) AS TOTAL FROM questionnaire_samplegroup3;
B) SELECT count(*) AS YES FROM questionnaire_samplegroup3 WHERE
question1 = 'Yes';
C) SELECT count(*) AS NO FROM questionnaire_samplegroup3 WHERE
question1 = 'No';

So these need to be like in the same row source? dont think that will
work, and then basically perform the calculations

so that I can work on the percentage of the results of each of these
queries.

for example:

A) 147
B) 4
C) 143

A) Changes all the time, well, they all do so cant just work out the %
once, it need to auto calculate as above.

I thought of something like this:

"

SELECT SUM(YES / TOTAL * 1000), count(*) AS TOTAL, (SELECT count(*) AS
YES FROM questionnaire_samplegroup3 WHERE question1 = 'Yes') FROM
questionnaire_samplegroup3;


"

Here its not apreciating the subquery..



Who would think i've got a degree in this stuff


It's been ages since ive done it, even longer since I used access in
this depth, and even longer since i slept. Helpen moi

 

[UpRider]

Advo, you could use the DCOUNT function as the data source for 3 textboxes
(not labels)
Perhaps in the form's current event or the click event of a command button:
Something like this...
txtTotal = DCOUNT ("*", "questionnaire_samplegroup3"
txtYES = DCOUNT("question1","questionnaire_samplegroup3","question1='YES'")
txtNO = DCOUNT("question1","questionnaire_samplegroup3","question1='NO'")

Then you can calculate your percentages from the textboxes...

UpRider