Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

TRANSLATE ACCESS JOINT TYPES TO CRXI LINK OPTIONS 1

Status
Not open for further replies.

Evil8

MIS
Mar 3, 2006
313
US
I am recreating a report originally built in MS Access using CRXI. I have 2 tables and one querry. I'm not as familar with Crystal Reports as I'd like to be. Can someone help me interpret how to properly translate the link options from Access to Crystal Reports?


One of the links is

Left Table Name: tbl_Client Right Table Name: tbl_ClInsured
Left Column Name: ClientNum Right Column Name: ClientNum

I want to Only include rows where the joined fields from both tables are equal.

The second link is

Left Table Name: tbl_ClInsured Right Table Name: qry_Insured_Spouse
Left Column Name: ClientInsuredID Right Column Name: ClientInsuredID

I want to Include ALL records from 'tbl_ClInsured' and only those records from 'qry_Insured_Spouse' where the joined fields are equal.
 
Use an inner equal join from Client to ClInsured, and a left outer join from ClInsured to Qry_Insured_Spouse.

Do not use any selection criteria based on the Qry_Insured_Spouse, as that will effectively "undo" the left join.

-LB
 
That worked really well thank you, but now I have another problem since I can't use selection criteria based on the qry_Insured_Spouse. I only need Insured and/or Spouses that turn 65 within the given date range, an example; this month the Customer Service Rep would run this report for the month of April - Beginning Date 04/01/2010 Ending Date 04/30/2010.

How would you suggest I set that up? Your help is greatly appreciated.
 
Are the date range parameters always based on one-month periods or could the user enter a range that included multiple months?

-LB
 
I just checked back with the user, it is only run for a month at a time.
 
First create a formula like this:

//{@spousebdate}:
if isnull({spouse.bdate}) then
date(0,0,0) else
{spouse.bdate}

Then create a second formula like this:

//{@inmonth}:
if
(
month({@spousebdate}) = month({?Start}) and
year({@spousebdate}) = year({?Start})-65
) or
(
month({clientbdate}) = month({?Start}) and
year({clientbdate}) = year({?Start})-65
) then 1

Then you can use a selection formula of:

{@inmonth} = 1

This will be slow though, because of all the local calculations.

-LB
 
Thank you so much for your help! I would have never thought to do it that way at all. Your help may be a solution I was having on other reports where I couldn't pick up employees and employees with dependent on the same report. I think that solution lies in the table links.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top