John W Vinson MVP... (Assistance please with Query on form)

[James]

Hello...

Ok I have

1 table... Which has the following in:

Are you male or Female With a value list as an answer....

Then Q01 with a value list of true or false....
Then Q02 As above...
All the way through to Q25....

Now how do I create this query?? I have a text box which
is where I will have the total... Basically do I need to
create a query??? If so please could you assist me with
the createtion of that. I would like it to take the values
of the 3 combo boxes... and then look through the records
with that matching criteria and then give me the total
records it has found in the text box.

Does this help any?

Thanks

James

-----Original Message-----


What's the structure of your table? Combo boxes can be used as
criteria in a Query, and you can create a Totals query using those
criteria; for instance you could create an unbound Form named frmCrit
with combos cboGender, cboQ and cboYesNo (or just use a checkbox). You
can then create a Query using

=Forms!frmCrit!cboGender
=Forms!frmCrit!cboQ
=Forms!frmCrit!chkYesNo <or> cboYesNo

on the Criteria line; change it to a Totals query using the Greek
Sigma icon to calculate your sums and percentages (I can't say just
how you'ld do this because I don't know what you're summing).


.

..

 

[John Vinson]

Hello...

Ok I have

1 table... Which has the following in:

Are you male or Female With a value list as an answer....

Then Q01 with a value list of true or false....
Then Q02 As above...
All the way through to Q25....

Now how do I create this query?? I have a text box which
is where I will have the total... Basically do I need to
create a query??? If so please could you assist me with
the createtion of that. I would like it to take the values
of the 3 combo boxes... and then look through the records
with that matching criteria and then give me the total
records it has found in the text box.

Does this help any?

Sorry, but no. It doesn't.

I do not know if Q01 through Q25 are *fields in a table* or *values in
multiple records of a table*.

I do not know ahat you want to total. The number of Yes answers among
the 25? The number of Yes answers for Q01, the number of Yes answers
for Q02, etc.?

I do not know aht "the three combo boxes" are. You have a gender field
and (I'm guessing) 25 yes/no fields; it is not at all clear what
relevance the combo boxes have to these fields.

Could you please clarify?

 

[James]

Right the Q01,02 etc are all fields in a table... they are
all YES/NO boxes...

I would like to total whats in the combo boxes... For
example...

If in Combo 1 = Male

Combo 2 = Q02

Combo 3 = False...

I would like to know what percentage of the total number
of records in the database match that criteria specified
above...

Does this help?

Many Thanks

James

 

[John Vinson]

Right the Q01,02 etc are all fields in a table... they are
all YES/NO boxes...

I would like to total whats in the combo boxes... For
example...

If in Combo 1 = Male

Combo 2 = Q02

Combo 3 = False...

I would like to know what percentage of the total number
of records in the database match that criteria specified
above...

Well, let me see if I'm getting it here.

You want to select a value (M or F) from combo box 1; a fieldname from
combo box 2; and a criterion to be applied to that fieldname for combo
box 3... right?

If so, you'll need VBA code. Your table structure IS INCORRECT, in
that you are storing data in fieldnames. (If you ever add a
twenty-sixth question, or delete a question, you'll need to redesign
all your tables, forms, queries, and reports; and your table design is
making this question both hard to understand and difficult to answer).

But let's see...

you'll need some VBA code to construct a SQL query. Let's say you have
the combo boxes on a from named frmMyForm, and they're named cboSex,
cboFieldname, and cboYesNo (suggesting that you use meaningful control
names).

You'ld need a command button on the Form with code like:

Private Sub cmdCalc_Click()
Dim strSQL As String
strSQL = "SELECT Count(*) AS Total, Sum(Abs(" _
& [Forms]![frmMyForm]![cboFieldname] _
& " = " & [Forms]![frmMyForm]![cboYesNo] & ))" _
& " AS Match, " _
& Sum(Abs(" & [Forms]![frmMyForm]![cboFieldname] _
& " = " & [Forms]![frmMyForm]![cboYesNo] _
& ")) / Count(*) AS PercentMatch" _
& " FROM yourtable;"
Me.RecordSource = strSQL
Me.Requery
End Sub

Put a textbox on the Form bound to PercentMatch (and additional
textboxes bound to Match and Total if you wish) and it should show you
the value you want.

But a properly normalized table (two tables in a one to many
relationship, with fields for QuestionNo and Answer and ID) will make
this MUCH easier.

 

[James]

Ok thanks for that when I put in the below code:

Dim strSQL As String
strSQL = "SELECT Count(*) AS Total, Sum(Abs(" _
& [Forms]![frmQry]![cboQuestion] _
& " = " & [Forms]![frmQry]![cboAnswer] & ))" _
& " AS Match, " _
& Sum(Abs(" & [Forms]![frmQry![cboQuestion] _
& " = " & [Forms]![frmQuery]![cboAnswer] _
& ")) / Count(*) AS PercentMatch" _
& " FROM tblMain;"
Me.RecordSource = strSQL
Me.Requery

It gives me a Syntex Error and also I am just curious as
to where it picks up the Gender?

Also when you say create text boxes for Match and Total
where will these come in and also whats the Match Field
Used For?

Many Thanks

Jams

-----Original Message-----

Right the Q01,02 etc are all fields in a table... they are
all YES/NO boxes...

I would like to total whats in the combo boxes... For
example...

If in Combo 1 = Male

Combo 2 = Q02

Combo 3 = False...

I would like to know what percentage of the total number
of records in the database match that criteria specified
above...

Well, let me see if I'm getting it here.

You want to select a value (M or F) from combo box 1; a fieldname from
combo box 2; and a criterion to be applied to that fieldname for combo
box 3... right?

If so, you'll need VBA code. Your table structure IS INCORRECT, in
that you are storing data in fieldnames. (If you ever add a
twenty-sixth question, or delete a question, you'll need to redesign
all your tables, forms, queries, and reports; and your table design is
making this question both hard to understand and difficult to answer).

But let's see...

you'll need some VBA code to construct a SQL query. Let's say you have
the combo boxes on a from named frmMyForm, and they're named cboSex,
cboFieldname, and cboYesNo (suggesting that you use meaningful control
names).

You'ld need a command button on the Form with code like:

Private Sub cmdCalc_Click()
Dim strSQL As String
strSQL = "SELECT Count(*) AS Total, Sum(Abs(" _
& [Forms]![frmMyForm]![cboFieldname] _
& " = " & [Forms]![frmMyForm]![cboYesNo] & ))" _
& " AS Match, " _
& Sum(Abs(" & [Forms]![frmMyForm]![cboFieldname] _
& " = " & [Forms]![frmMyForm]![cboYesNo] _
& ")) / Count(*) AS PercentMatch" _
& " FROM yourtable;"
Me.RecordSource = strSQL
Me.Requery
End Sub

Put a textbox on the Form bound to PercentMatch (and additional
textboxes bound to Match and Total if you wish) and it should show you
the value you want.

But a properly normalized table (two tables in a one to many
relationship, with fields for QuestionNo and Answer and ID) will make
this MUCH easier.


.

 

[John Vinson]

Ok thanks for that when I put in the below code:

Dim strSQL As String
strSQL = "SELECT Count(*) AS Total, Sum(Abs(" _
& [Forms]![frmQry]![cboQuestion] _
& " = " & [Forms]![frmQry]![cboAnswer] & ))" _
& " AS Match, " _
& Sum(Abs(" & [Forms]![frmQry![cboQuestion] _
& " = " & [Forms]![frmQuery]![cboAnswer] _
& ")) / Count(*) AS PercentMatch" _
& " FROM tblMain;"
Me.RecordSource = strSQL
Me.Requery

It gives me a Syntex Error and also I am just curious as
to where it picks up the Gender?

Also when you say create text boxes for Match and Total
where will these come in and also whats the Match Field
Used For?

The query should contain three fields: Total, the total number of
records in the table; Match, the number of records which match the
provided criteria; and PercentMatch, the ratio of these two. I was
suggesting that you display these three fields in textboxes on your
form (not knowing what else you might want to do with them).

The code was untested (I don't have your database in front of me) and
complicated (due to your incorrect table structure). I'd suggest
running the code in debug mode; look at strSQL after it has been
created and see what it contains. I'm sorry, but I'm trying to help
you learn how to do this - not trying to write your completed
application for you; you'll need to do some work to understand and
adapt my *SUGGESTIONS* (which were not intended as final black-box
solutions).