Having trouble outputting in Access SQL based on comparison of multiple variables

[SamScib]

I have a table structured in the following way:

Part Fac1 Fac2 Fac3 Fac4 Fac5 Fac6 Fac7 Fac8
1a1 US US MX US US
1a2 CN CN CN CN
1b1 CN US MX CN
1b2 US US US US US US
1c1 US US US
1c2 CN CN CN CN CN
1c3 US CN

I actually have 34 columns, and used the following type of SQL logic to compare variables and RETURN ANY PART IN THE TABLE THAT HAS A VALUE OTHER THAN BLANK AND DOES NOT MATCH A VALUE IN A SEPARATE COLUMN.

Example: From the table above, I would want to return parts: 1a1, 1b1, and 1c3 as these are the rows with mis-matching data (not including blanks). Here is the code I used, and I am getting the error: Query is too Complex:

Select * from

where fac1 <> fac2 and fac1 <> "" and fac2 <> "" OR fac1 <> fac3 and fac1 <> "" and fac1 <> "", ........ etc. for about a million lines of code.

Needless to say, the query is too complex.

Is there any way to run a loop without calling out all 3 arguments and every single combination of checks in the code?

Thanks for the help!!

Sam

 

[dhookom]

Welcome to Tek-Tips.

If I understand correctly, I would create a normalizing union query and then find parts where count of unique records grouped by Part and Fac is the same as the number of total records for each part.

SELECT Part, Fac1 as Fac
FROM Table
WHERE Fac1 & "" <> ""
UNION ALL
SELECT Part, Fac2
FROM Table
WHERE Fac2 & "" <> ""
UNION ALL
SELECT Part, Fac3
FROM Table
WHERE Fac3 & "" <> ""
--- etc ---
UNION ALL
SELECT Part, Fac34
FROM Table
WHERE Fac34 & "" <> ""

Then create a query like

SELECT Part, Count(*) As CountAll
FROM quniPartFac 
GROUP BY Part

Create a query of unique combinations

SELECT Part, Fac
FROM quniPartFac
GROUP BY Part, Fac

Create a query to group by Part from the previous query

SELECT Part, Count(*) as CountUnique
FROM qgrpPartUnique
GROUP BY Part

Final query to see if the group by query of Part have the same counts

SELECT A.Part
FROM qgrpPartAll A JOIN qgrpPartUniqueCount U ON A.Part = U.Part
WHERE A.CountAll = U.CountUnique

Duane
Hook'D on Access
MS Access MVP

 

[SamScib]

Thank you for that, I believe it is what I'm trying to accomplish. However, is there a way to incorporate the blank fields? In other words, what I would really like to show is a count of unique fields by part and fac combo (Example below) and then gather a list of all parts that have more than 1 unique records, NOT INCLUDING BLANK FIELDS. Here is an example (because the blanks didn't appear the way I thought they would on my first post):

Part Fac1 Fac2 Fac3 Fac4

1a1 US US US "blank"
1a2 US CN US "blank"
1a3 CN CN "blank" CN
1a4 US US "blank" CN
1a5 US "blank "blank" "blank"
1a6 US US "blank" CN

In this example, I would want to pull the rows for parts: 1a2, 1a4, and 1a6. Not including blanks, these rows have mis-matching values ("CN" and "US"), while the rest of the rows have exclusively either "CN" or "US" with blanks, but not both values.

Thanks again for the help..