Connect tables with formula/SQL 1

[DOMAL]

I need to be able to link two tables together with a formula.

Basically, I have one table with the field Req_Code which has strings like this:

380101
380202
380303

The other table only has the last four digits in the middle of the string. The field name "Group_Code" has the data like this

NA0101 GHT
NG0202 YTH
GH0303 IKJ

The "38" in the first field is always a constant and writing a formula to get the second field right is dead easy (using "38" & {mid(GROUP_CODE),4,4}). BUT I need to link the tables. I have to do this in crystal, I cant use access as I have had this suggested to me.

Help!!

Thanks

DoMaL

 

[lbass]

You can do this in a command (database expert->your datasource->above the tables list->add command:

Select table1.field, table2.field
from table1
inner join table2 on
{fn right(table1.req_code,4)} = {fn right(table2.group_code,4)}
where //etc.

You might be able to use simply right() depending upon your datasource. Add the table/field punctuation appropriate to your datasource.

Another approach would be to use a subreport and link the sub to the main report on formulas using the right() function, but the command would give you more flexibility and speed.

-LB

 

[DOMAL]

I'm using crystal 9 and cant seem to see this. Is it available in this version?


Thanks

DoMaL

 

[lbass]

Yes. I don't have CR 9, but I know commands are available. When you first select your datasource, you should see the option "Add command" above your list of tables.

-LB

 

[DOMAL]

Found it!

First time user of SQL, do I need to put anything after "where" as its a rule for all fields?

Thanks

 

[lbass]

If you are using a command, you should use it as the datasource for the entire report, so you would add all fields you want to work with in the Select clause, and add all record selection criteria in the where clause. If you have none, then omit the "where".

-LB