Hw can I pull same "Like" ids from another table 2

[monaamit]

Hello,

I am working on Access application. One of my table has Purchase order and another table have payments made to purchase orders. I need to pull all related information from payment table. My problem is the id's for purchase orders are not same and same length. Suppose purchase order is "1234567". The payment table will have id as "1234567/01" and "12345767/02" and "1234567/F". It means payment was made 3 times and I need to pull all ids that start with "1234567".

Please help.

 

[PHV]

One way:
SELECT O.*, P.*
FROM [order table] AS O, [payments table] AS P
WHERE rderID = Left(P.paymentID, Len(rderID))

Another way:
SELECT ...
FROM ...
WHERE P.paymentID Like rderID & '/*'

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

 

[Remou]

If the slash (/) is always there, something like this may suit:
[tt]SELECT Purchase.ID, Payment.ID
FROM Purchase INNER JOIN Payment ON Purchase.ID = Val(mid(Payment.ID,1,instr(Payment.ID,"/")-1));[/tt]

I have assumed that Payment has a text ID and that Purchase has a numeric ID for the purpose of testing.

 

[monaamit]

Thanks for all you help.

 

[monaamit]

what will be the correct syntax on SQL server for Val(mid(Payment.ID,1,instr(Payment.ID,"/")-1));

 

[PHV]

WHERE Payment.ID Like Purchase.ID & '/%'

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

 

[monaamit]

Thanks for quick reply. Another big favor. I am doing query backwards now. I am pulling all the purchase order which was finalized in 2006. My payments id's are as below 'F' in the end means finalized.

L9991/WE306F
L9281/WB337/F
M0097/F

I need to pull all orders with like Order_id. see example
L9991/WE306
L9281/WB337
M0097

Thanks alot for all your help.
Mona

 

[abaldwin]

Is this a database that you have design controll over?

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[monaamit]

No, I do not have design control over a database