Comapring data in two tables

[Derek]

Hi Guys

I have got 2 access tables named Table1 and Table2. There is one field common in both the tables and that is "Reference" . So I need to match the data in Reference field in both the tables using VBA.

steps to perform:

1. Check each Reference in Table1 against all the values in "Reference" field of Table2.

2. if match occurs then store all the details of that Reference in Table3.

There could be more than 1 match of that Reference in Table 2 and we need to check the whole Table2 for each "reference" value in "table1".

I hope that makes sense. Any help would be much appreciated.

Thanks
Derek

 

[plog]

No offense, but this is a simple query.

SELECT Table1.*
FROM Table1
INNER JOIN Table2 ON Table2.Reference=Table1.Reference

Did I miss something? This seems remedial.

 

[MarkK]

No offense, but this is a simple query.

SELECT Table1.*
FROM Table1
INNER JOIN Table2 ON Table2.Reference=Table1.Reference

Did I miss something? This seems remedial.

Lol. Didn't you miss ALL the fields from table2?

SELECT Table1.*, Table2.*
FROM Table1
INNER JOIN Table2 ON Table2.Reference=Table1.Reference

 

[Derek]

Thanks guys for your help. There is one more condition here. In Table2, Reference field contains data with some extra digits. So we need to use like operator.

e.g Table1 Reference data is 12893,234982, 3377660

but Table2 Reference data is 12893000000,12893776600,2349826666000 etc..

so we need to use like operator probably to match values. I hope you understand what I mean? Thanks

 

[MarkK]

So this isn't true . . .

There is one field common in both the tables and that is "Reference".

What are the exact rules that constitute a match?

 

[plog]

It would probably be best to give us sample data from your 2 tables (include table and field names) and then what you expect as the result for Table3. Be sure to include enough sample data to cover all cases (that includes answering Mark's question about what constitutes a match).

 

[Derek]

As I can't upload access database so I have made a sample workbook as attached . Please have a look. Thanks

 

[MarkK]

So why doesn't 223344 match with 22334499000? What are the exact rules that constitute a match?

 

[Derek]

Ok Mark. The reference number of Table1 should be present in the beginning of Reference number of Table2.. e.g Table1.Reference like Table2.Reference & "*"

or we can use Table1.Reference =Left(Table2.reference,len(Table1.reference))

 

[plog]

You didn't answer his question, you provided code. Is the record Mark identified to be included in Table3?

Assuming it is, this would be your SQL:

SELECT Table1.ID, Table1.Reference, Table2.ID, Table2.Reference
FROM Table1 INNER JOIN Table2 ON Table2.Reference Like Table1.Reference & "*";

 

[MarkK]

So the Excel file you posted incorrectly omits 223344 as a match. It should be present in the "table3" sheet?

Also, it looks like you may have answered your own question here . . .

Table1.Reference =Left(Table2.reference,len(Table1.reference))

 

[Derek]

yes u are right. sorry I missed out 223344. Please see attached again Table3 results

 

[MarkK]

Derek:
Check out plog's query . . .

SELECT Table1.ID, Table1.Reference, Table2.ID, Table2.Reference
FROM Table1 INNER JOIN Table2 ON Table2.Reference Like Table1.Reference & "*";

I didn't think you could use LIKE in a join expression, but that might be exactly what you are looking for, and simple, OMG.