Transaction Date in Range

[bhansen]

I have a employee with transaction date in one table that I want to compare with a start date, end date and value in another table, the problems is that;

1 The employee may not be in the other table at all, but I still want to see the transactions and assign a value;

2 If the employee is in the table I only want to show transactions that occured within the date range and not all ranges that appear in the other table.

3 If the employee is in the other table but the transaction date is higher than any of the records I want to assign a value.

Thanks

 

[kenhamady]

Link table A to Table B using the employee number. If this is a SQL/ODBC table use a left Outer Join. Do not put any selection criteria on table B.


You can now write formulas like:

If {DateA} in {Start} to {End}
then "X"
else "Y"

You can also suppress unwanted details using similar formulas. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[bhansen]

I seem to be having a real problem with this

I am able to get the proper result if it's in the date range, I receive as many records per transaction date as the are in the history table, this would be easy to filter except if my transaction date is higher than anyof the records in the history table I need to return another value.

Thanks Again

 

[kenhamady]

Sorry, I don't follow. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[bhansen]

My report is based on table 1 and I would like it to only take the information from Table 2 if it is in the range of the transaction date, but I get a record for each line that exists in table 2.

Sample Data

Table 1
ID Transaction Date Amount

22 Jan 1, 2002 20
33 Feb 1, 2002 30
44 Mar 1, 2002 40

Table 2

ID Start Date End Date Percent

22 April 1, 2001 April 30, 2001 0
22 May 1, 2001 Dec 31, 2001 16
44 Jan 1, 1987 Jan 1, 1999 10
44 Jan 2, 1999 Jan 31, 2002 15

 

[kenhamady]

And what exactly should the result be for this sample data? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[bhansen]

I would only like to see the transaction amount from table 1 and the percent from table 2 if it falls in the range of the transaction date if not in the range I would like another value.

Appreciate the help

 

[kenhamady]

Can you show what you mean. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com