Linking

[EscapeUK]

I have a DB which has not been set-up will, but I am stuck with it.

in table 1 i have a field called Area this field has data in it such as. A1234, A3322, B3311, B3355, C9829, C8392

Here is where it gets tricky.

If this the record has an A at the start i want it to link to Table 2. If it has a B at the start i want to link it to table 3. If it has a C at the start i want to link it to table 4. To make matters worse the data held in tables 2, 3 and 4 do not have the char at the start. It is a numeric field.

Any Ideas

 

[Katy44]

I had a similar but not exactly the same problem. Are you able to create views in the database to separate A, B and C records. If not you may be able to do it with a command in the report. I take it A, B and C correspond to different area types? Is this likely to change?

 

[EscapeUK]

No i won't be able to create a report.

yes the areas may change

 

[EscapeUK]

sorry meant no i won't be able to create a view

 

[synapsevampire]

First, soak the dba in toxic materials for a week, if they live, then they're as evil as I suspect, so kill them.

Not knowing what you intend to do with this data, and what thses joins might accomplish, your request leaves far too much to the imagination.

You might use a Union which resolves your requirements which combines tables 2,3 and 4 in a subreport, and link it to the main report.

If you need further assistance, try posting specifics, such as:

Crystal version
Database used
Example data
Expected Output

-k

 

[Katy44]

You could create three commands in the report (Assuming you only have three types of records - A, B or C) and use those to link to the different tables.

 

[EscapeUK]

create 3 commands?

 

[Katy44]

I'm not sure which version of Crystal you are using, but the way I solved my problem was to do this. When you go to Database Export, and look at your connection, there is an "Add Command" option directly above the database. Choosing this lets you write a query, which you can then use in exactly the same way as you would your tables or views in the reports. It is effectively creating a report-specific view. I would do this to create a view of the table for your records beginning with 'A', one for 'B' and one for 'C'. You then just link these three views separately to tables 2, 3 and 4.

I really hope this helps, let me know if I haven't explained it very clearly.

 

[lbass]

Another approach might be to create three formulas in the main report:

//{@A}:
if instr({table.field},"A") > 0 then
val(replace({table.field},"A"," "))
else 0

//{@B}:
if instr({table.field},"B") > 0 then
val(replace({table.field},"B"," "))
else 0

//{@C}:
if instr({table.field},"C") > 0 then
val(replace({table.field},"C"," "))
else 0

Then insert three subreports, one each for Table2, Table3, and Table4. Link the Table2 numeric field to the main report on {@A}, the Table3 numeric field on {@B} and the Table4 numeric field on {@C}.

I think this would return only the relevant values in the subreports, but I can't really test this.

-LB

 

[EscapeUK]

Katy44 i'm using CR 8.5 and can't see the Export Database option. I don't have the help installed at the moment.

 

[Katy44]

I meant Database Expert, sorry. In 9 that's in the Database menu. I have a feeling all the 'Experts' were introduced in 9 so you may not have it. It's the menu that comes up when you create a Crystal report that allows you to choose a data source etc.

 

[lyanch]

I would do this as a union.

Select xxx,xxx
from tableMain
tableTwo
where substring(tableMain.Area,1,1) = "A" and
convert(int, substring(tableMain.Area,2,4)= tableTwo.Area

UNION
Select xxx,xxx
from tableMain
tableThree
where substring(tableMain.Area,1,1) = "B" and
convert(int, substring(tableMain.Area,2,4)=tableThree.Area

etc.

Its ugly, but should get you in the right direction

Lisa