Latest date in a query!

[Bob V]

What would I need to enter to show the one record of each client by latest
date?
Thanks for any Help..........Bob

 

[KARL DEWEY]

In a Totals query select Client - Group By and action date - Maximum.

 

[Dale Fye]

If you want to show the entire record for each client, but only the most
recent one for each client then your query would look something like:

SELECT tblClients.*
FROM tblClients
WHERE tblClients.DateField = (SELECT MAX(DateField)
FROM tblClients C
WHERE C.ClientID =
tblClients.ClientID)

Another way of doing this would be:

SELECT tblClients.*
FROM tblClients
INNER JOIN (SELECT ClientID, MAX(DateField) as MaxDate
FROM tblClients
GROUP BY ClientID) as LatestDate
ON tblClients.ClientID = LatestDate.ClientID
AND tblClients.DateField = LatestDate.MaxDate

HTH
Dale

 

[Bob V]

Thanks Dale here is my SQL:
SELECT tblOwnerInfo.OwnerFirstName, tblOwnerInfo.OwnerLastName,
tblAccountStatus.BillDate, tblAccountStatus.PaidAmount, QryOverdue.Payable,
IIf(IsNull(tblOwnerInfo.OwnerTitle),'',tblOwnerInfo.OwnerTitle & ' ') &
IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName & '
') & IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName)
AS ClientName, tblAccountStatus.OwnerID
FROM (tblAccountStatus INNER JOIN tblOwnerInfo ON tblAccountStatus.OwnerID =
tblOwnerInfo.OwnerID) INNER JOIN QryOverdue ON tblOwnerInfo.OwnerID =
QryOverdue.OwnerID;

Can this be altered..............Regards Bob

 

[Bob V]

Got It
SELECT
IIf(IsNull([tblOwnerInfo].[OwnerTitle]),'',[tblOwnerInfo].[OwnerTitle] & '
') &
IIf(IsNull([tblOwnerInfo].[OwnerFirstName]),'',[tblOwnerInfo].[OwnerFirstName]
& ' ') &
IIf(IsNull([tblOwnerInfo].[OwnerLastName]),'',[tblOwnerInfo].[OwnerLastName])
AS ClientName, Max(tblAccountStatus.BillDate) AS LastDate,
QryOverdue.Payable, tblAccountStatus.OwnerID, tblOwnerInfo.OwnerFirstName,
tblOwnerInfo.OwnerLastName
FROM (tblAccountStatus INNER JOIN tblOwnerInfo ON tblAccountStatus.OwnerID =
tblOwnerInfo.OwnerID) INNER JOIN QryOverdue ON tblOwnerInfo.OwnerID =
QryOverdue.OwnerID
GROUP BY
IIf(IsNull([tblOwnerInfo].[OwnerTitle]),'',[tblOwnerInfo].[OwnerTitle] & '
') &
IIf(IsNull([tblOwnerInfo].[OwnerFirstName]),'',[tblOwnerInfo].[OwnerFirstName]
& ' ') &
IIf(IsNull([tblOwnerInfo].[OwnerLastName]),'',[tblOwnerInfo].[OwnerLastName]),
QryOverdue.Payable, tblAccountStatus.OwnerID, tblOwnerInfo.OwnerFirstName,
tblOwnerInfo.OwnerLastName
ORDER BY QryOverdue.Payable DESC;

 

[Bob V]

OOPS BUG: If there has never been a payment the name will not show
QryOverdue shows the name and amount but without a PayedAmount been put in
tblAccountStatus the Owner will not show....Thanks if you can help..Bob

Got It
SELECT
IIf(IsNull([tblOwnerInfo].[OwnerTitle]),'',[tblOwnerInfo].[OwnerTitle] & '
') &
IIf(IsNull([tblOwnerInfo].[OwnerFirstName]),'',[tblOwnerInfo].[OwnerFirstName]
& ' ') &
IIf(IsNull([tblOwnerInfo].[OwnerLastName]),'',[tblOwnerInfo].[OwnerLastName])
AS ClientName, Max(tblAccountStatus.BillDate) AS LastDate,
QryOverdue.Payable, tblAccountStatus.OwnerID, tblOwnerInfo.OwnerFirstName,
tblOwnerInfo.OwnerLastName
FROM (tblAccountStatus INNER JOIN tblOwnerInfo ON tblAccountStatus.OwnerID
= tblOwnerInfo.OwnerID) INNER JOIN QryOverdue ON tblOwnerInfo.OwnerID =
QryOverdue.OwnerID
GROUP BY
IIf(IsNull([tblOwnerInfo].[OwnerTitle]),'',[tblOwnerInfo].[OwnerTitle] & '
') &
IIf(IsNull([tblOwnerInfo].[OwnerFirstName]),'',[tblOwnerInfo].[OwnerFirstName]
& ' ') &
IIf(IsNull([tblOwnerInfo].[OwnerLastName]),'',[tblOwnerInfo].[OwnerLastName]),
QryOverdue.Payable, tblAccountStatus.OwnerID, tblOwnerInfo.OwnerFirstName,
tblOwnerInfo.OwnerLastName
ORDER BY QryOverdue.Payable DESC;

Thanks Dale here is my SQL:
SELECT tblOwnerInfo.OwnerFirstName, tblOwnerInfo.OwnerLastName,
tblAccountStatus.BillDate, tblAccountStatus.PaidAmount,
QryOverdue.Payable,
IIf(IsNull(tblOwnerInfo.OwnerTitle),'',tblOwnerInfo.OwnerTitle & ' ') &
IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName &
' ') &
IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName) AS
ClientName, tblAccountStatus.OwnerID
FROM (tblAccountStatus INNER JOIN tblOwnerInfo ON
tblAccountStatus.OwnerID = tblOwnerInfo.OwnerID) INNER JOIN QryOverdue ON
tblOwnerInfo.OwnerID = QryOverdue.OwnerID;

Can this be altered..............Regards Bob

 

[Dale Fye]

Bob,

You are going to have to use a somewhere in here, and may actually need a
subquery.

Assuming that tblOwnerInfo contains info on all owners, then that is the
table you want to start your query with. Because you have indicated that if
they have never made a payment, there will not be an entry in
tblAccountStatus, I recommend that you create outer joins by linking the
OwnerID field from tblOwnerInfo to each of the other two tables. Then edit
the join type to select all from tblOwnerInfo and only the matching records
from each of the other tables. Because tblOwnerInfo is your base table in
this query, you should never have Nulls in the OwnerTitle, OwnerFirstName,
OwnerLastName.
But to use the Max( ) aggregate operator, you will also need to use a
GroupBy

What does qryOverdue look like? I would expect that you would need the
OwnerID, LastPaymentDate, and AmountDue in that query to determine who is
overdue. If so, why do you even need tblAccountStatus in this query?

At any rate, try it like:

SELECT [tblOwnerInfo].[OwnerTitle], _
[tblOwnerInfo].[OwnerFirstName], _
[tblOwnerInfo].[OwnerLastName], _
Max(tblAccountStatus.BillDate) AS LastPaymentDate,
QryOverdue.Payable
FROM (tblOwnerInfo LEFT JOIN tblAccountStatus _
ON tblOwnerInfo.OwnerID = tblAccountStatus.OwnerID)
LEFT JOIN QryOverdue
ON tblOwnerInfo.OwnerID = qryOverDue.OwnerID
GROUP BY [tblOwnerInfo].[OwnerTitle], _
[tblOwnerInfo].[OwnerFirstName], _
[tblOwnerInfo].[OwnerLastName], _
QryOverdue.Payable

HTH
Dale

OOPS BUG: If there has never been a payment the name will not show
QryOverdue shows the name and amount but without a PayedAmount been put in
tblAccountStatus the Owner will not show....Thanks if you can help..Bob

Got It
SELECT
IIf(IsNull([tblOwnerInfo].[OwnerTitle]),'',[tblOwnerInfo].[OwnerTitle] &
' ') &
IIf(IsNull([tblOwnerInfo].[OwnerFirstName]),'',[tblOwnerInfo].[OwnerFirstName]
& ' ') &
IIf(IsNull([tblOwnerInfo].[OwnerLastName]),'',[tblOwnerInfo].[OwnerLastName])
AS ClientName, Max(tblAccountStatus.BillDate) AS LastDate,
QryOverdue.Payable, tblAccountStatus.OwnerID,
tblOwnerInfo.OwnerFirstName, tblOwnerInfo.OwnerLastName
FROM (tblAccountStatus INNER JOIN tblOwnerInfo ON
tblAccountStatus.OwnerID = tblOwnerInfo.OwnerID) INNER JOIN QryOverdue ON
tblOwnerInfo.OwnerID = QryOverdue.OwnerID
GROUP BY
IIf(IsNull([tblOwnerInfo].[OwnerTitle]),'',[tblOwnerInfo].[OwnerTitle] &
' ') &
IIf(IsNull([tblOwnerInfo].[OwnerFirstName]),'',[tblOwnerInfo].[OwnerFirstName]
& ' ') &
IIf(IsNull([tblOwnerInfo].[OwnerLastName]),'',[tblOwnerInfo].[OwnerLastName]),
QryOverdue.Payable, tblAccountStatus.OwnerID,
tblOwnerInfo.OwnerFirstName, tblOwnerInfo.OwnerLastName
ORDER BY QryOverdue.Payable DESC;

Thanks Dale here is my SQL:
SELECT tblOwnerInfo.OwnerFirstName, tblOwnerInfo.OwnerLastName,
tblAccountStatus.BillDate, tblAccountStatus.PaidAmount,
QryOverdue.Payable,
IIf(IsNull(tblOwnerInfo.OwnerTitle),'',tblOwnerInfo.OwnerTitle & ' ') &
IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName &
' ') &
IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName) AS
ClientName, tblAccountStatus.OwnerID
FROM (tblAccountStatus INNER JOIN tblOwnerInfo ON
tblAccountStatus.OwnerID = tblOwnerInfo.OwnerID) INNER JOIN QryOverdue
ON tblOwnerInfo.OwnerID = QryOverdue.OwnerID;

Can this be altered..............Regards Bob

If you want to show the entire record for each client, but only the
most
recent one for each client then your query would look something like:

SELECT tblClients.*
FROM tblClients
WHERE tblClients.DateField = (SELECT MAX(DateField)
FROM tblClients C
WHERE C.ClientID =
tblClients.ClientID)

Another way of doing this would be:

SELECT tblClients.*
FROM tblClients
INNER JOIN (SELECT ClientID, MAX(DateField) as MaxDate
FROM tblClients
GROUP BY ClientID) as LatestDate
ON tblClients.ClientID = LatestDate.ClientID
AND tblClients.DateField = LatestDate.MaxDate

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:



What would I need to enter to show the one record of each client by
latest
date?
Thanks for any Help..........Bob