Perform lookup type of operation in formula

[Meloncolly]

I'm trying to perform a lookup type of operation on a table. The table is tblEmpHistory. Two fields i'm interested in: DateChange, TeamID. The DateChange field records the date an employee changed team, and TeamID records the NEW team ID at that date. Using a parameter date field, can someone come up with a formula that would give the teamID for ANY given date? For example, dateChange is 22/12/03, TeamId is 5. On 21/12 and prior, give either "No Team" or the team for previous DateChange, and for 22/12/03 onwards, give "Team 5" (unless there is a later changedate, of course)

Here's how I'd do it in Access/other database query - I just can't seem to get this into a formula. Apologies, Crystal still quite new to me.

SELECT TOP 1 tblEmpHistory.TEamID
FROM tblEmpHistory
WHERE (((tblEmpHistory.DateChange)<=[Enter Report Date])
ORDER BY tblEmpHistory.DateChange DESC;

Many thanks in advance.

 

[synapsevampire]

Try:

Group by employee

Report->Edit Selection Formula->Group

(
{table.datechange} <= {?dateaprm}
and
{table.datechange}=maximum({table.datechange},{table.eomployee})
)

You should get the most recent datechange for each employee.

Sorry, can't test right now, but the theory seems sound.

-k

 

[lbass]

I think I would adapt SV's solution and use the following in report->edit selection formula->RECORD:

{table.datechange} <= {?dateparm}

And then use the following in edit selection formula->GROUP:

{table.datechange} = maximum({table.datechange},{table.employee})

-LB

 

[Meloncolly]

Thanks all for the replies - maybe I didn't explain my final goal correctly, but I need to know the TeamID, not dateChanged.

synapsevampire, thanks for your reply to my other post, by the way.

 

[synapsevampire]

What both of LB and I suggested provides what you want. He breaks it out over 2 areas, but in either case you'll get the maximum date for that range, hence placing the team id on the report canvas should be what you seek.

If someone is kind enough to post, either ignore or test. If you've tested and it fails, state why it falied and include any errors.

Details such as the version of your software and the database/connectivity, table layout, example data and expected output are all things you need to know to design a report, none of which you shared.

I made the assumption that your query really ISN'T what you wanted as it goes against part of the rest of your attempt at providing a spec using a text overview. Mine returns the most recent for each employee, your query would only return 1 row for the report.

Keep in mind that you can paste in SQL for CR 9 and above using the Add Command.

-k

 

[lbass]

Meloncolly,

Place {table.teamID} on the report canvas and then follow the suggestion above.

SV-

By using both the parm and the maximum date in the group selection, I think your approach would exclude employees whose maximum date was greater than the parm. If the parm is used to define the initial record set, then the maximum is limited to those records within the record set, not to the maximum of all records.

-LB