Can I use the IIF function to link 3 separate tables? 1

[glthornton]

I'm trying to link 3 tables. (tblSeen), (tblProvider), (tblResource). A key field in the tblSeen (EntityID) is used to link the other two tables. In most cases I can get a direct corelation between (tblSeen) and (tblProvider) to pull the "Provider Name" field. If I cannot get a direct corelation between the prior 2 tables, then I can get the remainder of the information when I use a connection between (tblSeen) and (tblResource).

What I'd like to know is can I use a IIF formula to say something like:

IIF({tblSeen.EntityID}={tblProvider.DRID}, {tblProvider.DRName}, {tblResource.DRName})

But the problem with this is that Crystal does not understand which link to use, either (tblSeen)-(tblProvider) or (tblSeen)-(tblResource). Does anyone have a suggestion how I could possibly do this?

Thank you,

glthornton

 

[synapsevampire]

No, and please post your software version with any post.

Join Seen to each of the other 2 tables.

Then in the record selection formula, use not(isnull({provider.drid}))

Then create a formula in Crystal to display the proper choice:

if not(isnull({provider.drid}) then
{tblProvider.DRName}
else
{tblResource.DRName})

Another approach would be to create a Minus Query using a Crystal Coomand object, or a Union, but since you're asking about linking tables and not stating your database type, it's not clear whether you can do this.

Please rememeber to at least post BASIC information about your environment.

You might contact your dba regarding this.

-k

 

[glthornton]

I appreciate your assistance on this. As a reminder from one programmer to another, don't always assume that your users know exactly what is needed when asking questions, unless instructed ahead of time. Just merely ask and you shall receive.

As for my environment, I'm connecting to an Access Database which has an ODBC connector to a vendor based application. I am using Crystal Reports version 10.

I will try your approaches.

Thank you,

glthornton

 

[synapsevampire]

You will likely experience some row inflation.

As for asking questions, as a programmer you must understand that not posting software versions, nor the database used when asking about software and databases will likely mean that you'll need to.

The point it that when one posts about software, as with making a technical call, the first question is invariably what software and version you're using, so again, please always remember to do so.

-k

 

[synapsevampire]

In considering your requirements, try creating a query which links just the first 2 tables.

Then create a Union (not Union All) query of the other table against that query, and you should have everything.

Then expose the final query as the datasource for Crystal.

Always try to let the database do the heavy lifting, use Crystal as your presentation layer.

-k