Formula to combine amount fld from 2 diff tables into 1 field 1

[CarpalT]

I'm using CR 8.5 against ABRA payroll Foxpro tables. The connection is a proprietary ADA (Abra Data Access) connection that will not allow SQL queries. (Otherwise I would try a union query.)

I have two tables.
People has three fields
people.id
people.EffectiveDate
people.Salary

History has three fields:
History.id
History.EffectiveDate
History.Salary

An employee's entire history is in "history" except for the last salary amount and effective date. I'm trying to pull a list for a date range that includes everything from both tables if the dates fall within the range. I'm trying to construct two formulas that will pull in the salary and effective date out of people into the bottom of the list from History.
I have one work-around that almost works but not quite. Here it is but don't let it prejudice you:
group on People.PersonNumber, and put People.salary in the group footer. This makes it LOOK like it's part of the list but I can't get the date range selection criteria right to apply to both dates so that only the records in people and history that are within the date range are selected.
Any suggestions will be very much appreciated.

The world is full of good people.

 

[lbass]

You should have a left join from the people table to the history table, since new employees would not have a history record. Then try a record selection formula like:

(
(
isnull({history.effectivedate}) and
{people.effectivedate} in {?daterange}
) or
{history.effectivedate} in {?daterange}
or
{people.effectivedate} in {?daterange}
)

Not sure what kind of display you would like. Are you trying to show the people date and the history date in the same column?

-LB

 

[CarpalT]

Thank you lbass (I was HOPING you'd see my post! ) for bringing to my attention the possibility of a null in the history table. That may be what I need to make my little work around report give me what I need.

To answer your question, yes I need all the effective dates in one column, and all the corresponding salaries in another. This is for an export to Excel for someone who wants to analyze all salary changes in a fiscal year. In my mind it's like creating another record is history where none exists.
I'm going to try your approach today and I'll let you know if that works.

The world is full of good people.

 

[lbass]

Try grouping on the person number and then placing the person date and salary in detail_a and the corresponding history fields in detail_b.

-LB