Table join on formula field?

[leftfldr5]

Crystal 10, SQL database. Need to link Item Master(IV00101) to Item Master Summary (IV30101) by a formula.

Desired results:

Item Number Item Number (summary) QTY Last Year Sales
111+C 111+O 2 0
222+C 222+O 5 2500

I need all the +C item numbers from the item master. I then need qty and last year sales from the summary table, but as the +O item of the same number.

Tried doing an Add Command and a View, but do not have the correct syntax to translate the +C item to a +O for linking purposes.
Pleas help. Thanks!

 

[satinsilhouette]

leftfldr5

Can you show us the formula you were trying to use and are these in two different database tables the item master summary and the item master?

 

[leftfldr5]

This is the add command I am trying:
replace({IV00101.ITEMNMBR}, '+C', '+O')
but I receive the error: Failed to open a rowset: Syntax error or access violation.

I thought I could then link by this to the IV30101.
The IV00101 and IV30101 are two different tables within the same database.

 

[synapsevampire]

Does SQL database mean SQL Server? SQL database means that a database supports SQL language.

If the item numbers are always a length of 3, then try a formula of:

substring(table.field,1,3)

as the join for each table.

-k

 

[leftfldr5]

Yes SQL Server.
The item lengths vary, but is always the last two characters I want to change. EX. XXXX+C to XXXX+O or ZZ+C to ZZ+O

 

[leftfldr5]

Bump....anyone please?