Add Command vs Linking Tables 1

[sardine53]

I am using Crystal XI to connect to an Access database.

There are 21 linked tables (plus 6 in a sub report) that are producing the correct results. I need to add another "TEAM_MEMBER" table and link the tables in such a way so that I only want the results when "OwnerInd"=1

Relevant Tables/Fields:

Table 1: TEAM_MEMBER
Field: CompanyID
Field: OwnerInd
Field: MemberID

Table 2: COMPANY
Field: CompanyID

I have tried different joins with CompanyID and a formula "If {TEAM_MEMBER.OwnerInd}=1 then {TEAM_MEMBER.MemberID}" but this just returns an exponential number of records (23,321 vs 1,955).

I was thinking adding a command would be the way to go but don't know how to accomplish this. Would you need to add the TEAM_MEMBER table but not link it? What would the SQL for the command be?

Any help you could provide would be great. You have always had the solution before!

Thanks

 

[lbass]

Is there a reason you are not just adding another subreport to get this value, e.g., you want to sort by it or something? A subreport would limit the records returned.

If you use a command, you should really use it for the entire main report, as linking on a command causes joins to occur locally and really slows reports. In a command used as the sole datasource, you could specify the condition right in the join, which would limit the records.

Otherwise, if you want to try linking to a command, you could use:

select team_member.company_ID
from team_member
where team_member.owner_ind = 1

I think you'll find it to be slow. Let me know if you want to try replacing your main report with a command.

-LB

 

[sardine53]

It worked great! The only change I made was adding the "member_ID" because that is the value I need:

select team_member.company_ID, team_member.member_ID
from team_member
where team_member.owner_ind = 1

I linked from the Company table to the Command and pulled the member_ID into the report. I left the team_member table unlinked (hoping that's what you're supposed to do).

I couldn't use a sub report because I need to add the field to the sub report as well.

Performance isn't suffering (yet).

Thanks LB - You are the best!