probably a basic question 1

[psimon1]

Hello

I'm more of an Access guy but I know that you can do this.

If I have a table:

EMPLOYEE, CHECK DATE, CHECK AMOUNT
123, 1/1/07, 100
123, 2/1/07, 200
123, 3/1/07, 300
456, 1/1/07, 100
789, 4/1/07, 100

and I want to return each employee's maximum check date, such that I receive:

123, 3/1/07, 300
456, 1/1/07, 100
789, 4/1/07, 100

In Access, I'd do:

select EMPLOYEE, max(CHECK DATE) as max, CHECK AMOUNT
FROM TABLE
group by EMPLOYEE

In Crystal, I know of the maximum function but kept getting an error.

Any ideas?

Thanks.

ps

 

[lbass]

You could just use group selection (report->selection criteria->GROUP) where you would enter:

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

...assuming you have inserted a group on the employee field. Alternatively, you could use a command where you set the date equal to the maximum, like:

select table.`EMPLOYEE`, table.`CHECK DATE`, table.`CHECK AMOUNT`
FROM TABLE
where table.`check date` = (select max(A.`check date`) from table A where A.`employee` = table.`employee`)

-LB

 

[psimon1]

I can't just use a formula in Crystal.

select expert on CHECK DATE

Maximum (CHECK DATE)?

Thanks. That seems easiest to me but I think like an Access man.

 

[lbass]

Try my first suggestion then--this is a selection on the maximum date per employee. Note that if you wanted to then insert summaries, you would need to use running totals, since non-group-selected records still contribute to inserted summaries. My second suggestion would only return the maximum date records to the report, so inserted summaries would work fine.

-LB

 

[psimon1]

I had to add the group first (by employee). After that, it worked like a charm. I still like the Access way more, but this will suffice. Thanks!