SELECT
 digi_client.ClientNumber,
 digi_client.ClientName,
 digi_client.Nextfollowup,
 digi_client.DateAdded,
 digi_client_status.ClientNumber,
 digi_client_status.Status,
 digi_client_status.ChangeNumber
FROM
 digi_client_status
 INNER JOIN digi_client ON (digi_client_status.ClientNumber = digi_client.ClientNumber)
WHERE
  (digi_client_status.ChangeDate BETWEEN :low and :high)
ORDER BY
 digi_client.DateAdded,
 digi_client.ClientName,
 digi_client_status.ChangeNumber DESC  top 1

This works, but all I get is one record.

When I am trying to do is to get the largest change number for EACH client and return that only

Thus (ignoring other fields)

Clients-

Borland
Net Nanny

Status-

Borland 1/1/06  Contacted  Change number 1
Borland 1/15/06 Signed contract Change number 3
Weatherunderground 1/1/06 Contacted Change number 2
Weatherunderground 1/15/06 Change number 4

What I want is:

Borland 1/15/06
Weatherunderground 1/15/06

But I am just getting Change number 4 only

So I am trying to go through the entire table and then pick the highest status for each master field.

Sorry. This is obviously a really dumb question. I may be missing where in the SQL to do it entirely.

Herb

"Herb (Kraft)" <herb@examprep.us> wrote in message
news:9FCAA4B1-6CE1-4C9D-9551-67C3C1788F8E@news.elevatesoft.com...
> SELECT
>   digi_client.ClientNumber,
>   digi_client.ClientName,
>   digi_client.Nextfollowup,
>   digi_client.DateAdded,
>   digi_client_status.ClientNumber,
>   digi_client_status.Status,
>   digi_client_status.ChangeNumber
> FROM
>   digi_client_status
>   INNER JOIN digi_client ON (digi_client_status.ClientNumber =
digi_client.ClientNumber)
> WHERE
>    (digi_client_status.ChangeDate BETWEEN :low and :high)
> ORDER BY
>   digi_client.DateAdded,
>   digi_client.ClientName,
>   digi_client_status.ChangeNumber DESC  top 1
>
> This works, but all I get is one record.
>
> When I am trying to do is to get the largest change number for EACH client
and return that only
>
> Thus (ignoring other fields)
>
> Clients-
>
> Borland
> Net Nanny
>
> Status-
>
> Borland 1/1/06  Contacted  Change number 1
> Borland 1/15/06 Signed contract Change number 3
> Weatherunderground 1/1/06 Contacted Change number 2
> Weatherunderground 1/15/06 Change number 4
>
> What I want is:
>
> Borland 1/15/06
> Weatherunderground 1/15/06
>
> But I am just getting Change number 4 only
>
> So I am trying to go through the entire table and then pick the highest
status for each master field.
>
> Sorry. This is obviously a really dumb question. I may be missing where in
the SQL to do it entirely.
>
> Herb
>
>
>

You need to set up a master detail relationship, where the master key is a
parameter

INNER JOIN digi_client ON (digi_client_status.ClientNumber = :ClientNumber)

and the detail query's mastersource is the datasource for the master table.
Then the detail gets refreshed on each change of the master.

Robert




"Herb (Kraft)" <herb@examprep.us> wrote in message
news:9FCAA4B1-6CE1-4C9D-9551-67C3C1788F8E@news.elevatesoft.com...
> SELECT
>   digi_client.ClientNumber,
>   digi_client.ClientName,
>   digi_client.Nextfollowup,
>   digi_client.DateAdded,
>   digi_client_status.ClientNumber,
>   digi_client_status.Status,
>   digi_client_status.ChangeNumber
> FROM
>   digi_client_status
>   INNER JOIN digi_client ON (digi_client_status.ClientNumber =
digi_client.ClientNumber)
> WHERE
>    (digi_client_status.ChangeDate BETWEEN :low and :high)
> ORDER BY
>   digi_client.DateAdded,
>   digi_client.ClientName,
>   digi_client_status.ChangeNumber DESC  top 1
>
> This works, but all I get is one record.
>
> When I am trying to do is to get the largest change number for EACH client
and return that only
>
> Thus (ignoring other fields)
>
> Clients-
>
> Borland
> Net Nanny
>
> Status-
>
> Borland 1/1/06  Contacted  Change number 1
> Borland 1/15/06 Signed contract Change number 3
> Weatherunderground 1/1/06 Contacted Change number 2
> Weatherunderground 1/15/06 Change number 4
>
> What I want is:
>
> Borland 1/15/06
> Weatherunderground 1/15/06
>
> But I am just getting Change number 4 only
>
> So I am trying to go through the entire table and then pick the highest
status for each master field.
>
> Sorry. This is obviously a really dumb question. I may be missing where in
the SQL to do it entirely.
>
> Herb
>
>
>