How to Connect Two Tables by connecting very similar text fields

[willz99ta]

Hi and thanks for your help,

I am trying to connect two tables by connecting very similar text fields.
For example:
First Field is: #234 -34543 -34534
Second Field is: 234-34543-34534

Is there any way to connect these two tables through this field?

Thanks again for your help,
Will

 

[hilfy]

Not by linking the tables in Crystal. However, depending on which database you're connecting to, you might be able to write a SQL Command where you do the join and return the data you need for your report.

The join between the two tables will look something like:

Replace(Replace(table1.field1, ' ', ''), '#', '') = table2.field2

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[lbass]

Or one table could be added in a subreport which uses Dell's formula for linking.

-LB

 

[willz99ta]

you might be able to write a SQL Command where you do the join and return the data you need for your report."


Can you go further in detail on how I can do that? Google can only teach you so much... I am in the command part of the "database expert". I have a basic knowledge of SQL.

Thanks,
Will

 

[Madawc]

I'd do it by creating a temporary file from table A, creating a new field that removed the differences and then creating a second temporary file linking it by the new field to table B.

This may not be the best way but it will work.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[IanWaterman]

Your query would be something like

Select Replace(Replace(table1.field1, ' ', ''), '#', '') as field1, table1.field2, table1.field3,
table2.field2, table2.field3
from table1
inner join table2 on Replace(Replace(table1.field1, ' ', ''), '#', '') = table2.field1

Ian

 

[hilfy]

The command is simply a SQL select statement that will pull all of the data that you need for your report. This way you control the SQL instead of having Crystal create it for you.

If your report requires parameters to filter the data, you'll create them in the Command Editor, not in the report. You'll then add them to the where clause by double-clicking on them. Note: any parameter is a single-select string, you'll need to put quotes around the parameter when you add it to the command - this is not required for multi-select strings.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.