Unmatched Query Qizard.

[deepanadhi]

Hi,
I have to find the records in table A which are not present in table B and display it as result. But the Problem is value in table A might not be exactly same as the values in Table B.

For Ex : Table A - contain value 125458
Table B might contain value same as 125458 or something like this 125458-1234 or 1234-125458 or '125458, 1234'.

In all the above cases , it is considered that both the values are equal and this record should not be displayed in the output.

Any help how this can be done. I dont have much knowledge about VB coding.

Thanks,
Deepan​

 

[pr2-eugin]

What are the data types of the values? I think the data in table A must be numbers and that of Table B is String. Is that correct?

 

[deepanadhi]

//What are the data types of the values? I think the data in table A must be numbers and that of Table B is String. Is that correct? //

In both the cases its string. Mostly Alphanumeric codes.
Eg : Table A - TGHLU9455040
Table B - Eb5-CHJ20175, TGHLU9455040

 

[pr2-eugin]

Not sure if this is the best option, there might be other answers that are far better and more efficient. So try this at last if you do not have any more answers.

[B]SELECT Table_A.TITLE, Table_A.[FIRST NAME], Table_A.[LAST NAME], Table_A.[ADDRESS LINE 1], Table_A.[ADDRESS LINE 2], Table_A.[ADDRESS LINE 3], Table_A.COUNTY, Table_A.[POST CODE], Table_A.TEL
FROM Table_A LEFT JOIN Table_B ON Table_A.[FIRST NAME] like "*" & Table_B.[FIRST NAME] & "*"
WHERE (((Table_B.[FIRST NAME]) Is Null));[/B]

This is not only the least sought option, but also remember it might return undesirable results as well.. For example,

If tableA has TGHLU9455040, CH750175040
tableB has Eb5-CHJ20175, TGHLU9455040

It might return nothing at all, as you see * is a wildcard.. thus, CH750175040 is nothing but (CH75)* & 0175 & *(040)..

 

[deepanadhi]

Not sure if this is the best option, there might be other answers that are far better and more efficient. So try this at last if you do not have any more answers.

[B]SELECT Table_A.TITLE, Table_A.[FIRST NAME], Table_A.[LAST NAME], Table_A.[ADDRESS LINE 1], Table_A.[ADDRESS LINE 2], Table_A.[ADDRESS LINE 3], Table_A.COUNTY, Table_A.[POST CODE], Table_A.TEL[/B]
[B]FROM Table_A LEFT JOIN Table_B ON Table_A.[FIRST NAME] like "*" & Table_B.[FIRST NAME] & "*"[/B]
[B]WHERE (((Table_B.[FIRST NAME]) Is Null));[/B]

This is not only the least sought option, but also remember it might return undesirable results as well.. For example,

If tableA has TGHLU9455040, CH750175040
tableB has Eb5-CHJ20175, TGHLU9455040

It might return nothing at all, as you see * is a wildcard.. thus, CH750175040 is nothing but (CH75)* & 0175 & *(040)..

Hi,

Thanks alot for the reply. I am verymuch new to the Access.
So can you be more specific where this piece of code should be used. your help would be much appreciated if you can explain me in bit more detail.

Thanks,
Deepan

 

[deepanadhi]

Hi,

I wrote query as u suggested. But when 'LIKE' is used in query, the relation between Table A & Table B is the Design View gets disconnected and filtering doesnt happens as expected. All the Records are displayed in the output.

SELECT *
FROM tablea LEFT JOIN tableb ON tablea.name = "*" & tableb.name & "*"
WHERE (((tableb.name) Is Null));

Anyidea why filtering isnt happening correctly???
TIA.

- Deepan

 

[pr2-eugin]

It does do that in deisgn view, but it works.. Say if the value of tableb.name is 'Paul' then,

When you use '=' it is going to look out for a string that is *Paul*
When you use 'LIKE' it is going to look for a string that looks like 'Paul' i.e. Paul, Pauline, Paula.. http://en.wikipedia.org/wiki/Jean-Paul