Dee:
A form would be the obvious way to go and is perfectly feasible but needs a
bit of work. What you'd need would be series of unbound controls in which
you'd enter or select the criteria. You'd then build the SQL statement in
code in a button's Click event procedure and open the query, a form or a
report. I'd strongly recommend one of the latter rather than just opening a
query as you can then design the layout of the form or report to give a far
better presentation of the results than a crude datasheet view of a query.
For a form or report you'd pass the SQL statement to it as its OpenArgs
property and then set the RecordSource property of the form or report in its
Open event procedure.
As regards the controls on the form you'll need to group these into two
sets, one the criteria for the main outer query (gender and language in your
example), the other the criteria for the subqueries (PptId, QstnID,
QstnaireID and Answer in your example) as you'll need to build several of
these. The controls can include more than the columns which you are going to
use as each will be tested for Null in the code, but I'll include only the
above ones in the code samples below. I'm still assuming hat all columns are
of text data type BTW so the values will be wrapped in quotes characters when
building the string expressions.
Rather than tying in the values in text boxes it would be better to use
combo boxes from which the values can be selected. This also means the combo
boxes can display a meaningful text column rather than a numeric code, but
the value of the control will be the number, which will be in a hidden
column. For the question say, assuming you have a table Questions with
columns QstnID and Question the combo box would be set up like this:
RowSource: SELECT QstnID, Question FROM Questions ORDER BY Question;
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.
As you'll be building up the string expression in different event procedures
you'll need to firstly create a module level variable to store it by outing
this in the form's module's declarations area:
Dim strSQL As String
On the form you'd need various buttons, the first to build the successive
subqueries, whose Click event procedure's code would go like this:
Dim strSub as String
Dim strCriteria As String
strSub = _
" AND EXISTS " & _
"(SELECT * "
"FROM TblResponses INNER JOIN TblReponseDetails " & _
"ON TblReponseDetails.ResponseID = TblResponses.ResponseID " & _
"WHERE "
If Not IsNull(Me,cboRptId) Then
strCriteria = " AND RptId = """ & cboRptId & """"
End If
If Not IsNull(Me,cboQstnID) Then
strCriteria = strCriteria & " AND QstnID= """ & cboQstnID & """"
End If
If Not IsNull(Me,cboQstnaireID) Then
strCriteria = strCriteria & " AND QstnaireID = """ & cboQstnaireID &
""""
End If
If Not IsNull(Me,cboAnswer) Then
strCriteria = strCriteria & " AND Answer = """ & cboAnswer & """"
End If
' remove leading AND operator
strCriteria = Mid(strCriteria,6)
strSub = strSub & strCriteria & ")"
strSQL = strSQL & strSub
For each subquery you'd select the values from the combo boxes, leaving a
combo box Null if you don't want to use it, and click the button.
The next button will be to clear the combo boxes so you can enter a new set
of sub-criteria. Its code would be:
cboRptId = Null
cboQstnID = Null
cboQstnaireID = Null
cboAnswer = Null
The code for a button to open the form or report would build the SQL for the
outer query in much the same way and then open the form or report, passing
the SQL statement to it:
Dim strOuter as String
Dim strCriteria As String
strOuter = _
" SELECT PptFirst, PptLast, " & _
"FROM tblPpts WHERE "
If Not IsNull(Me,cboGender) Then
strCriteria = strCriteria " AND Gender = """ & cboGender & """"
End If
If Not IsNull(Me,cboLanguage) Then
strCriteria = strCriteria & " AND Language= """ & cboLanguage & """"
End If
' remove leading AND operator
strCriteria = Mid(strCriteria ,6)
strOuter = strOuter & strCriteria
strSQL = strOuter & Mid(strSQL,6)
' open form
DoCmd.OpenForm "YourFormName", OpenArgs:=strSQL
Then in the form's (or report's) Open event procedure set its RecordSource
property with:
Me.RecordSource = Me.OpenArgs
Finally you'd have a button to clear all the controls and set the strSQL
variable back to a zero length string ready for a new bunch of criteria to be
selecetd:
cboGender = Null
cboLanguage = Null
cboRptId = Null
cboQstnID = Null
cboQstnaireID = Null
cboAnswer = Null
strSQL = ""
The above is written off the top of my head and I obviously haven't been
able to test any of it; it will almost certainly require some debugging, but
that's something only you can do. I haven't included anything to generate
the Q&A column, as that's an enhancement we can come back to. Its more
important to get the main functionality up and running first.
Ken Sheridan
Stafford, England
dee said:
Hi Ken,
I finally found some time to try this and it worked beautifully. Thanks so
much.
It leads me to another question. By any chance is it possible to create
some kind of form that would allow a user to type the criteria (question
number, etc.), like a parameter query, that would modify the sql?
Thanks again.
--
Thanks!
Dee
Ken Sheridan said:
Dee:
To do it using a simple constant you put the text you want to appear in a
column in quotes, "Question ID = 21 and Answer = Yes or Question ID = 4 and
Answer = No" in my example, then follow that by AS [Q&A] to call the column
Q&A. You can call it whatever you like of course, so you could have put AS
[Question and Answer] or whatever else suits. This just inserts the constant
string as a column in the queries result table.
Otherwise the query remains completely unchanged, so the whole thing would be:
SELECT PptFirst, PptLast,
"Question ID = 21 and Answer = Yes or Question ID = 4 and Answer = No"
AS [Q&A]
FROM tblPpts
WHERE PptGender = "2"
AND PptLanguage = "English"
AND EXISTS
(SELECT *
FROM TblResponses INNER JOIN TblReponseDetails
ON TblReponseDetails.ResponseID = TblResponses.ResponseID
WHERE TblResponses.PptId = tblPpts.PptId
AND QstnaireID = "4"
AND QstnID = "21"
AND Answer = "Yes")
AND EXISTS
(SELECT *
FROM TblResponses INNER JOIN TblReponseDetails
ON TblReponseDetails.ResponseID = TblResponses.ResponseID
WHERE TblResponses.PptId = tblPpts.PptId
AND QstnaireID = "5"
AND QstnID = "4"
AND Answer = "No");
The other way is more complex as you have to include the other tables in the
outer query as well as tblPpts and put the extra criteria in the WHERE clause
so it returns only the rows which match either criterion. I also think its
less satisfactory because it would return two rows per respondent rather than
just one. The above returns just one row per respondent and all you have to
do is make sure the string constant matches whatever questions and answers
you've included in the subqueries.
Ken Sheridan
Stafford, England
:
In the second scenario, when you put [Q&A], do I actually type that (probably
a dumb question) and then continue with the rest as you had posted
previously?
I did try it briefly and it seems to see it as a parameter query, asking me
for the qstnID from the response_details table. Probably something I've done
after fooling around with it for quite awhile at the end of a long day. I
will try this first thing in the morning.
Thanks so much for your assistance!
--
Thanks!
Dee
:
To show the question ids and answers one way would be to join the relevant
tables to the tblPpts table in the outer query, and in its WHERE clause
include two sets of criteria within parentheses in an OR operation, e.g.
WHERE PptGender = "2"
AND PptLanguage = "English"
AND ((QstnaireID = "4" AND QstnID = "21" AND Answer = "Yes")
OR (QstnaireID = "5" AND QstnID = "4" AND Answer = "No"))
AND EXISTS
(etc
You can then include them in the outer query's SELECT clause. You'd now get
two rows per respondent returned of course.
Another, and simpler, way would be to just add a constant to the outer
query's SELECT clause, e.g.
SELECT PptFirst, PptLast,
"Question ID = 21 and Answer = Yes or Question ID = 4 and Answer = No"
AS [Q&A]
FROM tblPpts
WHERE PptGender = "2"
etc
Ken Sheridan
Stafford, England
:
Hello Ken,
That worked perfectly. I've been using all kinds of separate queries that I
refer to in a main query, which is way to cumbersome!
I changed:
SELECT *
FROM tblPpts
to
SELECT PptFirst, PptLast
FROM tblPpts
to view only those fields, instead of everything.
If I want to view the question ids and answers, where should I change it? I
tried in the SELECT * for the two subqueries, but it didn't seem to display
anything.
BTW, without going into detail, I just wanted to say that the table
structure works beautifully. I had lots of help, especially from Tina in
this newsgroup, who really helped me out.
