How to match 4 character word or number or combination by identical 4 characters word

[chskrao]

Dear Sir,
Sub:- How to match 4 character word or number or combination by identical 4 characters word or number or combination in one word have 10 or 15 characters.

I have to two separate tables (Table A and Table B). Table A has one column (Tag No) and Table B has about 15 columns with one column name Tag No as below

Table A		Table B
Tag No		Tag No
2009		ZZZ-2030-DC
2010		ZZZ-2010-M9P
2110		P216-2031-ER-(EXT)
2012		ZAL-6630-1-ER2S
2020		PM-2020-ER1
2021		PM-6050-ER1
2022		LSHH-2031-01-M4S
2023		LSHH-2010-01-M4S
2030		LSHH-2009C-01-M4S
2031		LSHH-2110C-01-M4S
2032		ZZZ-6620-C-M7S
2033		CM-3710C-M5S

They're both in MS Access.I am trying to match 2 tables’ columns (Tag No) with join query, but not success. I want to match 4 characters in Table-A with 4 similar characters in Table-B (Tag No) cell.
What query is suitable to compare two tables. Please help.


Thank You,
Rao

[orange]

What have you tried? What was the result?
What is the meaning of these Tag Nos ZAL-6630-1-ER2S LSHH-2009C-01-M4S??

In Access we try to use 1 field= 1 fact, and not embed some "intelligence" into codes.

[chskrao]

In, Table-A “Tag No” is the Equipment Unit number and in Table-B “Tag No” is the Equipment number. I want to know in a particular unit, how many equipment tag numbers have and it’s details (from other columns).
Thanks
Rao

[recyan]

Will
the 4 numbers / charactres in Tag No in Table B,
always be after
the first hyphen ?

Thanks

[chskrao]

Yes
Thanks
Rao

[recyan]

Yes

Check out INSTR() and MID() functions.

What is the meaning of these Tag Nos ZAL-6630-1-ER2S LSHH-2009C-01-M4S??

In Access we try to use 1 field= 1 fact, and not embed some "intelligence" into codes.

Do not know the status at your end, but all the same, request you to take a second look at Orange's response, specifically the last line, in case you have not done so.

Thanks

[June7]

Try something like:

SELECT TableA.*, TableB.* FROM TableA, TableB WHERE TableA.TagNo = Mid(TableB.TagNo, InStr(TableB.TagNo,"-")+1,4);

[chskrao]

I used the below criteria. The criteria expression working well. My problem was solved.
I think, we cannot do this with a join, but in the criteria for tag no in tableb put
Like "*" & [tablea].[tag no] & "*"
Thank you for your support.
Rao

[June7]

Like operator is fine as long as the 4 characters will not be repeated in the longer string or it doesn't matter if they are. That's why I suggested extracting the 4 characters with string manipulation.

The lack of a pk/fk relationship is why the SQL I suggested does not have JOIN clause. This causes a cartesian joining - every record in each table will join with every record of the other table.