Multiple Unmatched values Query??

[BDLand]

I have a need to find only the records were either of 2 + 2 fields don't match. Example: Record1, field1 and field2 values don't match OR field3 and field4 values don't match then show record. This query is only pulling from 2 tables. the fields 1 & 3 are from one table and fields 2 & 4 are from the other table. When finished I would like to show the values of each of the fields. I am fairly new at Access so it may be something simple I am overlooking.

 

[SkipVought]

Hi,

Well its kinda hard to know what you're query is doing, when you don't show us your SQL code.

Skip,
[sub]
[red][/red]
[/sub]

 

[lespaul]

If there's no corresponding key in both tables:

SELECT FIELD1, FIELD2, FIELD3, FIELD4
FROM TABLE1, TABLE2
WHERE (FIELD1 <> FIELD2) OR (FIELD3 <> FIELD4)

if there IS a key in both tables:

SELECT FIELD1, FIELD2, FIELD3, FIELD4
FROM TABLE1
INNER JOIN TABLE2 ON TABLE1.KEYFIELD = TABLE2.KEYFIELD
WHERE (FIELD1 <> FIELD2) OR (FIELD3 <> FIELD4)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[BDLand]

Leslie,
Thanks I think your code will work, but I am evidently leaving something out. Could you take a look and let me know if you see anything?

 SELECT DISTINCT Q891666.PART_NUMBE, Q891666.DESCRIPTIO, Q891666.QTY, Q891666.OP, Q891666.WORK, Q891666.CENTER, Q891666.BASE AS [Old Base], [891666].BASE AS [New Base], Q891666.CURRENT AS [Old Current], [891666].CURRENT AS [New Current],
WHERE (Q891666.BASE AS [Old Base] <>  [891666].BASE AS [New Base]) OR (Q891666.CURRENT AS [Old Current] <> [891666].CURRENT AS [New Current])

SkipVought,
Sorry, I guess I wasn't clear. I had not yet created the query (tried but nothing worked). So I didn't have any code to include.


David

 

[lespaul]

well you don't have a FROM clause and you've got aliases in the WHERE clause (I already took those out).....

SELECT DISTINCT Q891666.PART_NUMBE, Q891666.DESCRIPTIO, Q891666.QTY, Q891666.OP, Q891666.WORK, Q891666.CENTER, Q891666.BASE AS [Old Base], [891666].BASE AS [New Base], Q891666.CURRENT AS [Old Current], [891666].CURRENT AS [New Current]
[b]FROM Q891666, 891666[/b]
WHERE (Q891666.BASE <>  [891666].BASE) OR (Q891666.CURRENT <> [891666].CURRENT)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins