Complex filters...

[Craggy]

I have set a database up to record the answers to a set of questions. In order to allow multiple answers to a question I decided to store the results in another table in the following format.

Response ID, Question ID, Answer ID

(There are another few fields but they’re not relevant)

This works fine for storing the responses, it allows me to have as many questions as I need, each with as many answers as I need.

Unfortunately I did not think about how I could report on this information. Obviously finding out the number of people who answered a particular question with a particular answer is easy but my manager has now stated that she wants to cross reference questions.

For example, if one of the questions were “Do you work for company A or B?” (Question ID 1) and the possible answers were Yes and No (answer ID 1 and 2)

This would need cross referencing with another question “Which website/s experienced the problems” (question ID 2) and there are say 10 possible answers (Answer ID 3-13)

I know what I need to do, but I just don’t understand who I would do this in Access.

Basically I need to filter my table for all Question ID 1 where the answer was Answer ID 1. (People who had a problem with the internet). Using this filter I need to make a record of the Response ID’s which had a problem with the internet and use this list to make another filter on a table. (so this new filter would now show the full response for everyone who had answered yes to the first question)

e.g.

Response ID, Question ID, Answer ID

1,1,1
1,2,5
1,2,6
1,2,9
2,1,1
2,2,5
2,2,6
3,1,2
4,1,1
4,2,9
5,1,2

This would be the contents of a table where 5 people had responded.
3 of the respondents have answered Yes to question 1
2 of the respondents have answered No to question 1.

I now need to filter this list for any Question ID 1 where the Answer ID is also 1. (The result of this filter for this example would be:

1,1,1
2,1,1
4,1,1

Obviously this isn’t really helpful as it has filtered out all of the other questions, but what it has done is provided me with a list of the response ID who has answered yes to question 1 (1,2 and 4). In this example there is only 3, but this could potentially be 1000)

So far im fine up to this point. Performing the initial filter is easy - What I need to know now is, is it possible to generate another filter from the contents of the first field in my filter? Then displaying all of the data from those particular respondent ID’s?
(1,2 and 4)

This would be:

1,1,1
1,2,5
1,2,6
1,2,9
2,1,1
2,2,5
2,2,6
4,1,1
4,2,9

Which I could now do another filter on to find out cross referenced answers, such as which particular websites caused the problem.

Now I must explain that this is just an example question, the system is currently designed so it has a full admin section where you can design questions and answer sets, so the question and answers needing cross-referenced could potentially be about anything. It is likely that each question”set” would have many more questions and possible answers.

I know this is quite complicated and i’ve probably not explained it that well but any help to point me in the right direction (or even tell me it’s not possible) would be greatly appreciated.

[Craggy]

Is there a limit to how long a filter can be?

e.g.

"1" or "2" or "3"....

If one could be made up by reading the first field in the filtered table and then using that to filer another table then this would be possible.

If there is a limit this would not really be a viable option since I would need upto 1000 numbers in the filter to ensure that the database produces accurate reports.

It would be much easier if there was a way to filter a table based on the contents of each record in another, filtered table.

[NeoPa]

If you're asking "Is there a way to filter one query depending on the results of another?", then the answer is yes.

You wouldn't use OR but In() instead. Let me see if I can come up with a simple example reflecting your original question for you.

I suggest, in the mean time, you look up the In() function & subqueries (Subqueries in SQL) to get a better understanding of how it works.

[NeoPa]

I've just noticed in your data that [Response ID]s 1 & 2 both have multiple answers for [Question ID] 2. Does this make sense?
This shouldn't effect the method to use, but thought I'd flag it anyway.

[NeoPa]

I have assumed a table of [tblResult].

Response ID, Question ID, Answer ID
   1             1           1
   1             2           5
   1             2           6
   1             2           9
   2             1           1
   2             2           5
   2             2           6
   3             1           2
   4             1           1
   4             2           9
   5             1           2

This is simply your original data rearranged for illustration. Multiple answers for the same question may well still be a problem for you, but should not effect this process.

SELECT [Response ID],[Question ID],[Answer ID]
FROM [tblResult]
WHERE [Response ID] In(SELECT [Response ID]
    FROM [tblResult]
    WHERE ([Question ID]=1) AND ([Answer ID]=1))

[Craggy]

Some questions will be singe choice and others multiple.

For example a question may be "Which of the following supermarkets do you use regulary". ASDA, Tesco, M&S, Iceland

Its possible people would shop at more than one, and i would need to record that.

Now for example another question could be "how often do you shop" Regulary, rarely

What i need to be able to do basically is filer which stores people who shop regulary visit, and which stored people who shop rarely visit.

(these are completely fabricated questions by the way!)

[Craggy]

The problem i have is technically one response is covered over many records. If it was over just 1 record the filters i need would be easy.

Response ID, Question ID, Answer ID
    1             1           1
   1             2           5
   1             2           6
   1             2           9
   2             1           1
   2             2           5
   2             2           6
   3             1           2
    4             1           1
   4             2           9
   5             1           2
 

What i have highlited in bold is what a filter would show. in the above example this would just be showing people who shop regulary, but then all other information about these regular shoppers is lost. But i do know that Response ID 1,2 and 4 are all regular shoppers now.

I need to be able to use that data to now find out what they answered to the second question (or whichever quesiton it may be). I now want to filter out all records except the ones that have the same Response ID as the results from the first filter (1,2 and 4)

The results would be:

Response ID, Question ID, Answer ID
   1             1           1
   1             2           5
   1             2           6
   1             2           9
   2             1           1
   2             2           5
   2             2           6
   4             1           1
   4             2           9
 

[Craggy]

This probably sounds like a stupid question but where would I put the code?

SELECT [Response ID],[Question ID],[Answer ID]
FROM [tblResult]
WHERE [Response ID] In(SELECT [Response ID]
    FROM [tblResult]
    WHERE ([Question ID]=1) AND ([Answer ID]=1))
 
 

:o

[Craggy]

Here is the code re-written for my database:

SELECT [ResponseRespondentID],[ResponseQuestionID],[ResponseAnswer]
FROM [report]
WHERE [ResponseRespondentID] In (SELECT [ResponseRespondentID]
FROM [report]
WHERE ([ResponseAnswer]=83))
 

Since there are other fields in the table (which werent really relevent to display) I assume I can change it to:

SELECT * FROM [report]
WHERE [ResponseRespondentID] In (SELECT [ResponseRespondentID]
FROM [report]
 WHERE ([ResponseAnswer]=83))
 

There is no need to select the question as part of the filter criteria since the answers are unique to the question anyway. so in the case ResponseAnswer = 83 is a "yes" answer that is associated to a particular question.

I still dont quite understand where this code goes though. I tried it in the filter section in the properties tab for my form and it had no effect.

I tried creating a query but couldnt figure out where the code would go there either.

[NeoPa]

...
Since there are other fields in the table (which werent really relevent to display) I assume I can change it to:

SELECT * FROM [report]
WHERE [ResponseRespondentID] In (SELECT [ResponseRespondentID]
FROM [report]
 WHERE ([ResponseAnswer]=83))

looking good ;)

There is no need to select the question as part of the filter criteria since the answers are unique to the question anyway. so in the case ResponseAnswer = 83 is a "yes" answer that is associated to a particular question.

Makes sense.

I still dont quite understand where this code goes though. I tried it in the filter section in the properties tab for my form and it had no effect.

I tried creating a query but couldnt figure out where the code would go there either.

Open a new query in design mode. Select View / SQL. Paste in the SQL code you just worked out. Switch back to Design view.

Good luck, and let me know how it works out.

[Craggy]

Seems to be working!

Absolutely excellent mate. much simpler than i had expected.

Many thanks for the help!

I assume that I can put a reference to a form field in SQL as I would in VBA.

[NeoPa]

No problems. Glad it helped.
Try that out (referring to form controls from within SQL). Certainly I would expect any SQL code at least to require a full reference (it's not run "within" any object after all), but I'm pretty sure it can work. See Referring to Items on a Sub-Form for details on form control referencing.

[Craggy]

This works perfectly.

I can now have a form with linked dropdowns to select the question set, the questions within that set and finally the answers associated with the question - and filter the results accordingly.

I could probably take that further but dumping that data into excell then doing pivot tables seems to work well enough not to bother!

Thanks again!

[NeoPa]

Our pleasure :)
Always appreciate a response to let us know it worked though - thanks.