Matching the last 3 characters

[wdbouk]

Hello,

I have two tables. They could be linked together through a common variable however in one table this variable is reported as a 9 characters and in another one as a 3 characters. For example, in one table this variable is reported as 124884AA5 and in another table as AA5 . Does anyone know how to link the two tables?
Best wishes

 

[lespaul]

If table 1 has the longer information, something like this should work:

SELECT * FROM TABLE1 INNER JOIN TABLE2 ON mid(Table1.FieldName, len(Table1.FieldName) - 3), 3) = table2.FieldName

Leslie

 

[wdbouk]

Hi Leslie,
the table with longer information is called debt_ids
the table with shorter information is called 0VirginDatabase Naic303
The variable name in debt-ids is called id_num
the variable name in 0VirginDatabase Naic303 is called issue_cusip

I tried your method and wrote the following SQL codes


SELECT * FROM [debt_ids] INNER JOIN [0VirginDatabase Naic303] ON mid(debt_ids.id_num, len(debt_ids.id_num) - 3), 3) = [0VirginDatabase Naic303].issue_cusip;

I keep on receiving that joint expression is not supported
Any hint of what went wrong?
Thx

 

[lespaul]

Try this instead: (I think the len function was returning the wrong number)

SELECT * FROM [debt_ids] INNER JOIN [0VirginDatabase Naic303] ON mid(debt_ids.id_num, len(debt_ids.id_num) - 2), 3) = [0VirginDatabase Naic303].issue_cusip;


Leslie

 

[wdbouk]

Hi leslie,

I am receiving the same error message.
Wd

 

[lespaul]

what happens if you just try this:

SELECT mid(debt_ids.id_num, len(debt_ids.id_num) - 2), 3) FROM debt_ids

do you get the correct information?


Leslie

 

[wdbouk]

I am receiving extra ) in query expression'3)' i deleted the ) after number 3 and I got two columns , the first one is the last 3 characters and in the second column just number 3 for all cells. There is still something wrong.

 

[lespaul]

I wrote it wrong, try this (you removed the wrong ')'):

SELECT mid(debt_ids.id_num, len(debt_ids.id_num) - 2, 3) FROM debt_ids


Leslie

 

[wdbouk]

I am receiving one column from the first table with the last 3 characters. I can proceed from here .
Thanks a lot Leslie , I hope I didn't cause too much annoyance
wd

 

[wdbouk]

Hello again,

I am trying to perform a query using the results of the colmn we have produced but I keep on receiving invalid argument. Is it becasue of what we have just done or it is another problem that I need to post a new thread for?
Thx

 

[lespaul]

OK, so now you have:

SELECT * FROM [debt_ids] INNER JOIN [0VirginDatabase Naic303] ON mid(debt_ids.id_num, len(debt_ids.id_num) - 2, 3) =  [0VirginDatabase Naic303].issue_cusip;

and it works ok?

Post the SQL from your query and we'll see if we can figure it out.

les

 

[wdbouk]

I am having ,
SELECT mid(debt_ids.id_num, len(debt_ids.id_num) - 2, 3) FROM debt_ids

 

[wdbouk]

I am receiving an invalid argument when I am trying to create a Table where all the variables in Table 1 are included and the column obtained from the query below matches the common variable in table 1 (I hope I didn't confuse you)
SELECT mid(debt_ids.id_num, len(debt_ids.id_num) - 2, 3) FROM debt_ids)

 

[lespaul]

Ok, the query you have there will just return the last three characters from the id_num field. Once you have that information what do you need to do with it?

leslie

Leslie

 

[wdbouk]

Actually I added other variables from the debt_ids table to the three character column. consequently I obtained a table called MoodyCusip using the following query:
SELECT Mid(debt_ids.id_num,Len(debt_ids.id_num)-2,3) AS Expr1, debt_ids.debt_num, debt_ids.id_num_cd, debt_ids.id_num INTO MoodyCusip
FROM debt_ids;

After that I tried to add the debt_num from the MoodyCusip table to the 0VirginDatabase Naic303 Table using the following query:

SELECT [0VirginDatabase Naic303].[Primary Key], [0VirginDatabase Naic303].date_acquired, [0VirginDatabase Naic303].par_val_bonds, [0VirginDatabase Naic303].name_of_vendor, [0VirginDatabase Naic303].actual_cost, [0VirginDatabase Naic303].issue_id, [0VirginDatabase Naic303].issuer_cusip, [0VirginDatabase Naic303].issue_cusip, [0VirginDatabase Naic303].pd_acrd_int_div, [0VirginDatabase Naic303].Price, [0VirginDatabase Naic303].flat_price, [0VirginDatabase Naic303].accrued_interest, [0VirginDatabase Naic303].[Price($100)], MoodyCusip.debt_num AS Expr1 INTO [Virgin+Moody]
FROM [0VirginDatabase Naic303], MoodyCusip;

And here I obtained the invalid argument message.
I hope you could help

 

[PHV]

Have you tried to replace this:
SELECT * FROM [debt_ids] INNER JOIN [0VirginDatabase Naic303] ON mid(debt_ids.id_num, len(debt_ids.id_num) - 3), 3) = [0VirginDatabase Naic303].issue_cusip;
By this ?
SELECT * FROM [debt_ids], [0VirginDatabase Naic303]
WHERE Right(debt_ids.id_num, 3) = [0VirginDatabase Naic303].issue_cusip;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[wdbouk]

Hi PH,

it is working but when I try to save the query or run it as a make table query it gives me an invalid argument. So basically, I was able to see the table but I wasn't able to save it. Any help about how to overcome this problem?

 

[lespaul]

why do you need to save it?


Leslie

 

[wdbouk]

Hello guys ,
I have good news and bad news
the good news is that I transformed the original files (text data files) to Access and the invalid argument problem is solved.
The bad news is that I need to add the last 3 digits to another number consisting of 6 digits. Sorry for that but I am trying to link two different databases and I am trying to build a new variable that would make the linkage possible.
the first 6 digits belong to a variable called issuer_cusip and the last 3 digits belong to a variable called issue_cusip . both variables belongs to 0VirginDatabase Naic303 Table. Any help on that?