SQL filter problem

[Glen]

I have inherited a problem that I have narrowed to one SQL statement.
I am having great difficulty fixing this problem and greatly appreciate
any help. Here is the SQL:

strSQL = "SELECT tbl_QA_Junc.QA_ID, tbl_QA_Junc.Cat_ID,
tbl_Main_QA.Tech_Code FROM tbl_Main_QA RIGHT JOIN tbl_QA_Junc ON
tbl_Main_QA.QA_ID = tbl_QA_Junc.QA_ID GROUP BY tbl_QA_Junc.QA_ID,
tbl_QA_Junc.Cat_ID, tbl_Main_QA.Tech_Code HAVING (((tbl_QA_Junc.Cat_ID)
In (" & strFilt & ")) AND ((tbl_Main_QA.Tech_Code) In (" & stCodeFilt &
")));"

I have a form where the user selects different categories to generate a
list of questions. The Main_QA table holds a general technical code
for the associated questions (i.e. electrical, mechanical, structural,
etc.). The QA_Junc table holds several categories associated with the
technical code questions. The QA_ID is the primary key for the MAIN_QA
table and is used in the QA_Junc table for cross-referencing.

strCodeFilt = numerical values assigned to tech codes (1-8)
strFilt = numerical values assigned to the categories (15-78)

Problem:
The way this functions now is to create a complete list of distinct
questions. I need it to create a list of exclusive questions.
For example: Suppose a user selects ALL technical codes (177
questions) and then selects a Conceptual category (23 of the 177
questions) and a Schematic category (55 of the 177 questions). This
will generate 78 questions total. A filter discovers that 20 of the
conceptual questions are duplicates so the generated report has 58
questions. I need the report to generate the 20 duplicates exclusive
to both schematic and conceptual categories and not the 58 distinct
questions.

Thank you for taking the time to read this and again, any help would be
greatly appreciated.

 

[Wolfgang Kais]

Hello Glen.

I have inherited a problem that I have narrowed to one SQL
statement. I am having great difficulty fixing this problem and
greatly appreciate any help. Here is the SQL:

strSQL = "SELECT tbl_QA_Junc.QA_ID, tbl_QA_Junc.Cat_ID,
tbl_Main_QA.Tech_Code FROM tbl_Main_QA RIGHT JOIN
tbl_QA_Junc ON tbl_Main_QA.QA_ID = tbl_QA_Junc.QA_ID
GROUP BY tbl_QA_Junc.QA_ID, tbl_QA_Junc.Cat_ID,
tbl_Main_QA.Tech_Code HAVING (((tbl_QA_Junc.Cat_ID)
In (" & strFilt & ")) AND ((tbl_Main_QA.Tech_Code) In (" &
stCodeFilt & ")));"

Since you use criteria for both Cat_ID and Tech_Code, you don't need
the outer join. Also, instead of grouping all fields, use select distinct.
Instead of using a "having" for a grouping column, use a where clause:
Nevertheless, your query should work, but could be optimized a little:

strSQL = "SELECT DISTINCT tbl_QA_Junc.QA_ID, tbl_QA_Junc.Cat_ID,
tbl_Main_QA.Tech_Code FROM tbl_Main_QA INNER JOIN tbl_QA_Junc
ON tbl_Main_QA.QA_ID = tbl_QA_Junc.QA_ID WHERE (((tbl_QA_Junc.Cat_ID)
In (" & strFilt & ")) AND ((tbl_Main_QA.Tech_Code) In (" & stCodeFilt &
")));"

I have a form where the user selects different categories to generate
a list of questions. The Main_QA table holds a general technical
code for the associated questions (i.e. electrical, mechanical,
structural, etc.). The QA_Junc table holds several categories
associated with the technical code questions. The QA_ID is the
primary key for the MAIN_QA table and is used in the QA_Junc
table for cross-referencing.

strCodeFilt = numerical values assigned to tech codes (1-8)
strFilt = numerical values assigned to the categories (15-78)

Problem:
The way this functions now is to create a complete list of distinct
questions. I need it to create a list of exclusive questions.
For example: Suppose a user selects ALL technical codes (177
questions) and then selects a Conceptual category (23 of the 177
questions) and a Schematic category (55 of the 177 questions). This
will generate 78 questions total. A filter discovers that 20 of the
conceptual questions are duplicates so the generated report has 58
questions. I need the report to generate the 20 duplicates exclusive
to both schematic and conceptual categories and not the 58 distinct
questions.

As noted above, your query should work. You cold verify that by stopping
the code after the "strSQL =" line (using the Stop statement and the
in the debug window enter "?strSQL" (without quotation marks). This
will return the SQL statement. Create a blank query and paste that SQL
statement to the SQL view of the query design window. Run the query.

What do you mean saying "a filter discovers duplicates"? Duplicates of
QA_ID / Cat_ID / Tech_Code combinations are dropped through your
"group by" (or my distinct). But since the two Cat_IDs in your example
differ (I hope), they both must be displayed in the result.
So, what's the "filter" that discovers dunpliytes?

 

[Glen]

Thanks for your reply Wolfgang. To explain further, this SQL query
selects the QA_ID, Cat_ID, and Tech Code. It can duplicate QA_ID if
there is a QA_ID associated with each Cat_ID. For example, the QA_ID
or Question Identifier Code for a certain electrical question could be
equal to 125. This electrical question (tech code 2) could pertain to
several different categories, schematic drawings (Cat_ID 74) and
conceptual designation (Cat_ID 15). The query will return something
like the following:

QA_ID Cat_ID Tech Code
125 15 2
125 74 2

The way the original designer wrote the program was to collect these
duplicate values for QA_ID and filter them in a While loop to grab only
one question for each hit. Unfortunately, that is not the way the user
wants the program to work now. The user wants to eliminate any
questions that match in different categories. I hope this helps
explain it a little better. I will try the SQL debugging steps you
suggested and see what I get. If you have any other suggestions I
welcome them. As it stands right now, I spent the better part of
yesterday writing a function to filter the results according to
category selection using a series of if statements and while loops.
It's convoluted and definitely not the most efficient way to accomplish
what I need, but it works.

Thanks again.

Glen

 

[Glen]

I used the SQL string provided and it returns the same results as
before. I had tried a series of SQL statements yesterday with similar
results. I don't know if any of my attempts were exactly like this
suggestion, but I know they were similar. The return from the query is
78 questions, 23 conceptual and 55 schematic. The return should be 20
as there are 20 questions that match both the conceptual and schematic
categories. I apologize for the confusion of my prior post, the user
wants to return only one hit for those questions that MATCH both
categories. I said the opposite. Sorry, its early.

 

[Wolfgang Kais]

Hello Glen.

I used the SQL string provided and it returns the same results
as before. I had tried a series of SQL statements yesterday
with similar results. I don't know if any of my attempts were
exactly like this suggestion, but I know they were similar.
The return from the query is 78 questions, 23 conceptual
and 55 schematic. The return should be 20 as there are 20
questions that match both the conceptual and schematic
categories. I apologize for the confusion of my prior post,
the user wants to return only one hit for those questions that
MATCH both categories. I said the opposite. Sorry, its early.

Ok, that's a little more complicated.
I assume, that there is a table containing all categories (tbl_QA_Cat)
that has a primary key field of Cat_ID. Then, I hope that the following
query solves the problem:

strSQL = "SELECT tbl_Main_QA.QA_ID, tbl_QA_Junc.Cat_ID, " & _
"tbl_Main_QA.Tech_Code FROM tbl_Main_QA INNER JOIN " & _
"tbl_QA_Junc ON tbl_Main_QA.QA_ID = tbl_QA_Junc.QA_ID " & _
"WHERE (((tbl_QA_Junc.Cat_ID) In (" & strFilt & ")) AND " & _
"((tbl_Main_QA.Tech_Code) In (" & stCodeFilt & ")) AND " & _
"(Not Exists (SELECT * FROM tbl_QA_Cat WHERE " & _
"(((tbl_QA_Cat.Cat_ID) In (" & strFilt & ")) AND (Not Exists " & _
"(SELECT * FROM tbl_Main_QA AS MQA INNER JOIN tbl_QA_Junc " & _
"AS QAJ ON MQA.QA_ID = QAJ.QA_ID WHERE (((MQA.Tech_Code)=" & _
"tbl_Main_QA.Tech_Code) AND ((QAJ.Cat_ID)=tbl_QA_Cat.Cat_ID" & _
"))))))));"

To explain what the query does:
Select all present Tech_Code/Cat_ID combinations where the values
satisfy the criteria entered by the user and where no category exists
that was selected by the user and that has no corresponding junction
to the selected Tech_Code.

 

[Glen]

I don't know if you will get this or not. I just wanted to say thanks
for the response. It's been a while since I've entered groups. What
ended up happening with this job you ask? The client was confused and
really wanted it to work another way. It was a little less complicated
and the original posted SQL statement was what I needed, imagine that.
Anyway, thanks for taking your time to answer me.

 

[Wolfgang Kais]

Hello Glen.

I don't know if you will get this or not. I just wanted to say thanks
for the response. It's been a while since I've entered groups.
What ended up happening with this job you ask? The client was
confused and really wanted it to work another way. It was a little less
complicated and the original posted SQL statement was what I
needed, imagine that. Anyway, thanks for taking your time to answer me.

Wellcome, I got you message.