Need help displaying records with the latest date

[dholiday]

Here is my situation:

I have 2 tables joined on the customer ID. I want to display information from both tables. The problem I have is if the customer has more than 1 order they are displayed twice. What I would like to do is display the customer information with the appropriate order information for the latest order, even if that customer has not placed an order with us yet.

Basically, what I need to do is find a way to see if my customer has an order, if it does use maximum(fld, condFld) only when there is more than 1 order.

If I use the maximum function on the order_date field then I sucessfully show only the latest order however if there are no orders, the customer information does not display on the report.

Thanks for any help


John

 

[synapsevampire]

You're close...

Grouped by the customer, then in the Report ->Record Selection->Group place:

isnull({table.date})
or
{table.date} = maximum({table.date}, {table.CustID})

Make sure that you have a Left Outer join.

-k

 

[dholiday]

First of all, thank you very much for your prompt and correct answer yesterday. I do still have one issue. I have found a summary function that is distinct count, that gives me the correct count of the records. However, I am also using a sum() that is of course giving me incorrect results. Is there someway of having a distinct sum()?

Thanks in advance

John

 

[Madawc]

Running totals include the option to add just once for each group. Does that help?

Madawc Williams (East Anglia, UK)

 

[synapsevampire]

A distinct sum isn't enough information, you need to identify what you are summing and at what level (groups?).

You're generally better served to start new threads when your topic changes.

-k

 

[dholiday]

Madawc you figured out exactly what I needed thank you very much. I feel bad that I did not figure that one out on my own. It is amazing what one forgets when the boss wants it done yesterday. Thanks again everyone.

John