create join on two fields containing prefixes

[dalerguy]

Hello,

I have two tables that have related data except each field has a prefix. Table 1 has an A and Table 2 has a R. The other numbers are the same. How do I create an equal join on these two fields.



Table1
OrderNumber
A1234567890
A9876543210
A54321
A678901234

Table2
OrderNumber
R1234567890
R9876543210
R54321
R678901234

 

[Jackblack1]

I easiest way maybe to create a primary key and joint them that way.

 

[dalerguy]

Ended up using MID which will allows me to join on OrderNumber

SELECT DISTINCT [authorization transactions].[Order Number], [authorization transactions].[Credit Card Type], [authorization transactions].StudentID, [authorization transactions].[Customer Name], [authorization transactions].[Customer Address], [authorization transactions].[Customer City], [authorization transactions].GivingAs, [recuring transactions].[Order Number], [recuring transactions].[Transaction Date], [recuring transactions].[Transaction Amount]
FROM [authorization transactions], [recuring transactions]
WHERE (((Mid([authorization transactions].[Customer Name],1,Len([authorization transactions].[Customer Name])))=Mid([recuring transactions].[Customer Name],1,Len([recuring transactions].[Customer Name]))));

 

[dalerguy]

Ignore posted sql. references. I didn't look before i posted. oops.

here is what I used:
SELECT DISTINCT [authorization transactions].[Order Number], [authorization transactions].[Credit Card Type], [authorization transactions].StudentID, [authorization transactions].[Customer Name], [authorization transactions].[Customer Address], [authorization transactions].[Customer City], [authorization transactions].GivingAs, [recuring transactions].[Order Number], [recuring transactions].[Transaction Date], [recuring transactions].[Transaction Amount]
FROM [authorization transactions], [recuring transactions]
WHERE (((Mid([authorization transactions].[Order Number],2,Len([authorization transactions].[Order Number])))=Mid([recuring transactions].[Order Number],2,Len([recuring transactions].[Order Number]))));