Unmatch query (1 Viewer)

[izen]

Hi everyone

I have a problem about unmatch query wizard

I have 2 quries that I want to retrive the records that are different value
I used the unmatch wizard but it doesnt list any record

my queries are QueryA

ProductID ProductName Quantity Unit Cost
1 AAA 1 KG $500
2 BBB 1 Kg $600

QueryB

ProductID ProductName Quantity Unit Cost
1 AAA 1 KG $400
2 BBB 1 Kg $600

I would like the result of

ProductID ProductName Quantity Unit Cost
1 AAA 1 KG $400

or either way

ProductID ProductName Quantity Unit Cost
1 AAA 1 KG $500

The unmatch query works with this matter ?
If yes wHY my unmatch query doesnt show the unmatched list

If no How can i do ?

Cheers

 

[RuralGuy]

So the ProductID field is unique to each record and you want to join the two queries on that field and only return records where the [Unit Cost] from one query <> the [Unit Cost] of the other query, right? Does that give you any ideas?

 

[izen]

Ohh yes thanks I got it

If I like to find all unmatch fields except ProductID Can i just put critirua in the other fields some thing like <>[ProductName], <><Quantity>

Anyway I did do it but It doesnt work do u have any suggestion?

thanks

 

[RuralGuy]

You need to point to the "other" field.

 

[izen]

alright I did it
thanks

 

[dallr]

Two options :
1. Match your two tables on the primary key and then us an IIF statement to match ALL the similar fields in question. e.g IIF(T1.Cost = T2.Cost,"Match","No Match") , .......
Then filter to show all fields with "No Match".

2. Option 2 which will be faster. Please take note of the AND and multiple OR's. The bracketing is important for this query. Notice how all the OR's are collectively surrounded by one bracket.

SELECT T1.*, T2.*
FROM Yourtable2 AS T2 
     INNER JOIN Yourtable AS T1 
          ON ((T2.Cost <> T1.Cost) OR 
                (T2.Unit <> T1.Unit) OR 
                (T2.Quantity<> T1.Quantity) OR 
                (T2.ProductName <> T1.ProductName)) AND 
                (T2.ProductID = T1.ProductID);

Dallr

 

[RuralGuy]

alright I did it
thanks

Excellent! You're welcome.

 

[izen]

Thanks guys

another question
I would like to change the color of text on the feilds that are different
i just want it is more easy to find the different one

can we do that ?

cheers,

 

[RuralGuy]

Look into Conditional Formatting.

 

[izen]

i could not find Conditional Formatting on the query view

 

[RuralGuy]

It is only available in a form.

 

[izen]

I tried to add the Expression Is <> (queryB.ProductName)
but It shows invalid syntax
any idea ?

 

[RuralGuy]

No Is...just <> queryB.ProductName should do it.

 

[izen]

still can not do it
could u pls check the sample file that i attached

Conditional formating on Cost feild

Expression Is [tProduct].[Cost]<>[tOrder].[Cost]

thanks

 

[RuralGuy]

Here's one way and a link to read to boot.

 

[izen]

thanks for your sample it works but when
I compare 2 values which are food costs
I would like to check there are the same values or not
so I use the field value is not equal to [fieldname]
other fields look good, except cost fields it shows the result opposit
for example
not equal toI would like to changeto red color if the 2 values are not the equal but it does change even the value equal.

2 values, the first value comes from calculation another comes from typing
but on the 2 fields look exactly the same to me

I reckon the vaule that comes from calculation maybe not exactly the same with the user type maybe something like this $5.31234124 (calculation) $5.3124 (typing)
However I set the format and decimal only 4 positions
so on the field I see just $5.3124 (calculation) $5.3124 (typing)

you guys have any idea how to fix up ?