Unmatched records ?

[cpampas]

Is there a way to change the unmatched query wizard (Access 2003) in order to find unmatched values from 2 tables, providing that the matched records are matched only once.

Let me explain, my query is like this :

SELECT tblB.ID, tblB.valor
FROM tblB LEFT JOIN tblC ON tblB.valor = tblC.valor
WHERE (((tblC.valor) Is Null));
ORDER BY tblB.DataM;

And my tables :

tblB tblC

id valor valor
1 1.000 3.600
2 5.000 2.000
3 5.000 1.000
4 1.500 5.000
5 1.000

The result should be : 5000, 1500, 1000
But it returns 1000.
Maybe the query should have another condition, like not allowing matches where the ID from tblB was already used. Any clues on how to do that ?
Thanks
Carlos

 

[MSAccessRookie]

I believe that your Query seems to returning EXACTLY what you are asking it to.

Your Query:

[FONT=Courier New]SELECT tblB.ID, tblB.valor[/FONT]
[FONT=Courier New]FROM [B][COLOR=blue]tblB LEFT JOIN tblC[/COLOR][/B] ON tblB.valor = tblC.valor[/FONT]
[FONT=Courier New]WHERE [COLOR=red][B](((tblC.valor) Is Null))[/B][/COLOR];[/FONT]
[FONT=Courier New]ORDER BY tblB.DataM;[/FONT]

Your Data:

[FONT=Courier New]id    tblB.valor    tblC.valor[/FONT]
[FONT=Courier New]1        1.000        3.600[/FONT]
[FONT=Courier New]2        5.000        2.000[/FONT]
[FONT=Courier New]3        5.000        1.000[/FONT]
[FONT=Courier New]4        1.500        5.000[/FONT]
[FONT=Courier New]5        1.000[/FONT]

The LEFT JOIN will result in a Dataset that has all values from tblB and matching values from tblC with Null Values in tblC where none are found.

The (((tblC.valor) Is Null)) will only consider any Dataset rows that have no value in tblC.

The only row that matches is Row 5 (1.000).

-- Rookie

 

[plog]

I'm a little confused about what your starting data and ending data is. Could you post sample data from B, sample data from C, what you actually get and what you should get?

Be sure to seperate B data from C data, don't make just one table like you did in your initial post.

 

[cpampas]

Hi,
Sorry, when I said that my query was returning 1.000, I should have said 1.500

tblB

id tblB.valor
1 1.000
2 5.000
3 5.000
4 1.500
5 1.000


tblC
id tblc.valor
1 3.600
2 2.000
3 1.000
4 5.000


My query :

SELECT tblB.ID, tblB.valor
FROM tblB LEFT JOIN tblC ON tblB.valor = tblC.valor
WHERE (((tblC.valor) Is Null));

I would like the first record on tblB to match the 4th on tblC, and the second on tblB to match the 4th on tblC. And no more matches after that.

I tried creating a query with tblB, grouping the values, and then try to unmatch the query with tblC, no results still.

The purpose of this is to find non matching records between banking data and the accounting.

Thanks for your help
Carlos

 

[plog]

I would like the first record on tblB to match the 4th on tblC, and the second on tblB to match the 4th on tblC. And no more matches after that.

I swear you are trying to confuse people at this point. If you would have just posted your data liked I asked, I probably would have been able to figure it out. That paragraph though cannot be right and is not helping.

Let's try this again. Don't fix your error in the previous post or try to explain what you actually meant, just give me this: post sample data from B, sample data from C, what you actually get and what you should get. No more, no less.