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

[June7]

Try:
SELECT *
FROM TableA
WHERE (((DLookUp("[fieldname]","[TableB]","InStr([fieldname]," & [fieldname] & ")>0")) Is Null));

Domain aggregate functions can be very slow but I don't see how this can be done with a subquery. Alternative is VBA code looping through recordsets and either setting a field to flag the record or write record ID to a temp table.

[deepanadhi]

Try:
SELECT *
FROM TableA
WHERE (((DLookUp("[fieldname]","[TableB]","InStr([fieldname]," & [fieldname] & ")>0")) Is Null));

Domain aggregate functions can be very slow but I don't see how this can be done with a subquery. Alternative is VBA code looping through recordsets and either setting a field to flag the record or write record ID to a temp table.

Hi June7,

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

TIA,
Deepan

[June7]

It is a query statement. Build a query. Open query designer. Either build in Design View with the grid or SQL view and type or copy/paste the statement. Replace with your table and field names where needed.

[recyan]

Perhaps :

SELECT 
	TableA.field1
FROM 
	TableA
WHERE 
	(
		(
			(TableA.field1) 
			Not In 
			(
				SELECT DISTINCT 
					TableA.field1 
				FROM 
					TableA 
					INNER JOIN 
					TableB 
					ON 
					TableB.field2 LIKE TableA.field1 & '*'
			)
		)
	);

Thanks

[June7]

Very close recyan. I had tried something like that - the DISTINCT did not occur to me. So I then tried InStr function in subquery and got stumped. That's when I went to the DLookup.

Your suggestion works if the TableA value will always be at the beginning of the string in TableB value. Now after seeing yours I got this mod to work:

SELECT field FROM TableA WHERE field Not In (SELECT DISTINCT field FROM TableA INNER JOIN TableB ON TableB.field LIKE '*' & TableA.field & '*');

[recyan]

You are right June.
Also perhaps assumption, that at OP's end, TableA field is always subset of TableB field.

Thanks

[deepanadhi]

You are right June.
Also perhaps assumption, that at OP's end, TableA field is always subset of TableB field.

Thanks

Thanksalot June7 & Recyan. Its working

Thanks,
Deepan M