Substr comparison

[nithink]

Hi,

I've an output from a query like given below. I've to again manipulate the result to get another output.
I've to compare the values in the first field with a column in another table,Table2.
But the question is, I've to compare from the 4th position of the cl_id.
Ex: I should check this value '228100-674700' against the column 'Col1' in 'table2' and if it is equal,
get the value from column,'Col2', of that table. If it is not equal leave it as it is.
Can you pls help me out in this ?

Query Output:
--------------

cl_id post_date
-----------------------------------
44-228100-674700 10/31/2003
44-228100-674700 05/31/2003
44-228100-674700 06/30/2003
44-228100-674700 07/31/2003
44-228100-674700 08/31/2003
44-228100-674700 09/30/2003
44-228100-674700 10/31/2003
44-228100-674700 11/30/2003

Table2
--------

Col1 Col2
---------------------

228100-674700 674700C1

 

[lespaul]

How about:

SELECT cl_id, post_date, Col2 FROM [Query Output] INNER JOIN Table2 on Table2.Col1 = RIGHT([Query Output].cl_id, 13)

Leslie

 

[nithink]

Thanks Leslie..
the query works fine.. but my another question is, the cl_id is not a fixed length, it can be 13
or less than that.. for the below given values there is matching values in Table2.. so i need to
get the matching values from Table2 for the below cl_id....

cl_id post_date
---------------------------
44-135000-1-2 09/30/2002
44-135000-1-2 08/31/2002
44-135000-1-2 07/31/2002

Table2
--------

Col1 Col2
---------------------

135000-1-2 135000C1

And other thing is, I want to retain the values if it doesnt have a match in Table2 like given below.
So the below given cl_id's dont have match in Table2 and I just want to leave it like that.
So my query output should have the same number of records like it has in the original first query output.
Hope I explained correctly. Sorry if I was not clear.

cl_id post_date
----------------------------
44-135000 08/31/2001
44-135000 06/30/2001
44-135000 05/31/2001
44-135000 04/30/2001
44-135000 03/31/2001

Thanks,

 

[lespaul]

OK, then instead of using right, use MID

MID(([Query Output].cl_id, 4, length([Query Output].cl_id))

if there are cl_id values in the first table that are not in Table2 then change the INNER JOIN to a RIGHT JOIN:

SELECT cl_id, post_date, Col2 
FROM [Query Output] 
RIGHT JOIN Table2 on Table2.Col1 = MID(([Query Output].cl_id, 4, length([Query Output].cl_id))

Leslie

 

[nithink]

Sorry.. again forgot to mention one more thing..
If it matches with Table2's column value, I need to get the value and put it in place of that existing value.

Ex:

44-228100-674700 has a matching value in Table2 as

Col1 Col2
---------------------

228100-674700 674700C1

and now it has to be, 44-674700C1. Rite now, it just replaces 44-228100-674700 with 674700C1 but it should be 44-674700C1. Sorry for the confusion

 

[lespaul]

So you want to change the value of cl_id to the value of Col2 in Table2 with the beginning of the original cl_id?

In that case is [Query Output] really a query or is it the name of a table? I'm going to assume that it's a table and you want to change the cl_id:

UPDATE [Query Output] A set A.cl_id = (SELECT LEFT(B.cl_id, 4) & T.Col2  
FROM [Query Output] B
INNER JOIN Table2 T on T.Col1 = MID((B.cl_id, 4, length(B.cl_id))) WHERE MID((A.cl_id, 4, length(A.cl_id))) = T.Col2

Before running the UPDATE portion, make sure that the inner select statement runs correctly:

SELECT LEFT(B.cl_id, 4) & T.Col2  
FROM [Query Output] B
INNER JOIN Table2 T on T.Col1 = MID((B.cl_id, 4, length(B.cl_id)))

Be sure to run the update on a copy first!

HTH

leslie

 

[nithink]

OH no leslie... Its a Query not a table... So how we
have to do now ? Should I create some temp table and
populate it and then run this update ? Thanks..

 

[lespaul]

Depends, do you want to change the data in the table forever or do you need it changed temporarily for a report or something?

The update will work on the table as well as long as it has the same fields.

Leslie

 

[nithink]

hi Leslie, back after a break...

Actually its a query output which I want to update...
That is I want to change the value of cl_id to the value of Col2 in Table2 with the beginning of the original cl_id if
there are any matchings with cl_id from the query output to
the Col1 of the Table2. Can you pls let me know.. So confused.. haven't worked much in Access...
Thanks...

 

[lespaul]

Nope sorry, not clear yet.

Do you want to update the information in a table (permanent change)?


Or you need a query that has that information in it to use somewhere else?

Leslie

 

[nithink]

Hi Leslie,
Given below is the UPDATE query I'm trying.. but its giving
"OPERATION MUST USE AN UPDATABLE QUERY"
Can you pls let me know..
Thanks...

UPDATE CapgComp_init A set A.cl_id = (SELECT LEFT(B.cl_id, 4)&T.future_state_account_num
FROM CapgComp_init B
INNER JOIN current_future_acct_num AS T ON MID(B.cl_id,4,len(cl_id)) =T.current_pim_account_num
WHERE MID(A.cl_id, 4, len(A.cl_id)) = T.current_pim_account_num)