I used to know the answer to this but my mind is drawing a blank.

Master Table:

Client Number
& various other information about clients

Detail table:

Client Number
Status change number (Auto Inc)
Status

&
various other information about the status of the client (prospect, sold, closed etc.)

-----------------------------------------------------------

I need to write a query that will match each client with its status. Easy enough.
But I only want to return the latest change for each client. The last entered status for each client will be the highest autoinc.

In pseudo code:

For each client number, determine its which status is the highest numbered (which will be the last one) for each client and then return that.

I'm sure it's obvious but my brain is seized shut.

Thanks
Herb

"Herb (Kraft)" <herb@examprep.us> wrote in message
news:DED26644-171E-4F7F-99B0-A314630FBB38@news.elevatesoft.com...
> I used to know the answer to this but my mind is drawing a blank.
>

You can order the detail descending and just display the first record found
(not a grid, just db fields), it will be the latest.

Robert

"Robert" <rkaplan@AdvantSoft.com> wrote:


"Herb (Kraft)" <herb@examprep.us> wrote in message
news:DED26644-171E-4F7F-99B0-A314630FBB38@news.elevatesoft.com...
> I used to know the answer to this but my mind is drawing a blank.
>

You can order the detail descending and just display the first record found
(not a grid, just db fields), it will be the latest.

Robert

-------------
That's what I planned to do. Problem is that the report I am creating shows all of the statuses.

For example:

Client-     Borland

Status records in detail table for Borland-

Auto Inc  1   1/1/06   Contacted
Auto Inc  2   1/13/06  Executed contract

I just want the last record, but the report is showing them all.
Simply getting a grid to just show the last doesn't mean that it translates nicely into a report. I need to be able to make PDFs of these reports for
easy sharing etc. so I am not 100% comfortable just dumping the grid.

It's like I need

select (highest) or something like that

Herb

"Herb (Kraft)" <herb@examprep.us> wrote in message
news:245B050B-8F03-43A9-86D2-C7597992E055@news.elevatesoft.com...

select field from table order by field desc top 1

Robert

"Robert" <rkaplan@AdvantSoft.com> wrote:


"Herb (Kraft)" <herb@examprep.us> wrote in message
news:DED26644-171E-4F7F-99B0-A314630FBB38@news.elevatesoft.com...
> I used to know the answer to this but my mind is drawing a blank.
>

You can order the detail descending and just display the first record found
(not a grid, just db fields), it will be the latest.

Robert

-------------
That's what I planned to do. Problem is that the report I am creating shows all of the statuses.

For example:

Client-     Borland

Status records in detail table for Borland-

Auto Inc  1   1/1/06   Contacted
Auto Inc  2   1/13/06  Executed contract

I just want the last record, but the report is showing them all.
Simply getting a grid to just show the last doesn't mean that it translates nicely into a report. I need to be able to make PDFs of these reports for
easy sharing etc. so I am not 100% comfortable just dumping the grid.

It's like I need

select (highest) or something like that, but I can't seem to form the SQL

Herb

Thanks.
I don't know why I didn't think of that

By the way, too many of us working Sunday nights!