How do I find the last record ?

[Roger]

I have Access 2002 SP3 on XP. I have a table of client names. Linked to that
table is another table of payments made by client (client nr, amount, date).
I have a query to find the last payment made by a client ... the
payment-amount and payment-date but only if the payment was made more than 1
month ago (that is, no payments made in the last month).

I have used the Group By "Last" option in my query for the date field
payment-date, and put a criteria in >(Now())-28, but for a couple of clients
I get strange results ... some payment-date results are within a month and
in 2 cases I have two "Last" records ... obviously I have done something
wrong. Can someone help me please

thanks heaps ... Roger

 

[Stefan Hoffmann]

hi Roger,

I have used the Group By "Last" option in my query for the date field
payment-date, and put a criteria in >(Now())-28, but for a couple of clients
I get strange results ... some payment-date results are within a month and
in 2 cases I have two "Last" records ... obviously I have done something
wrong. Can someone help me please

First of all, there is no "last" record without any order criteria.
Strange results? Are you using the LAST() function?


Something like

SELECT TOP 1 *
FROM [yourTable]
WHERE [dateField] > (Now() - 28)
ORDER BY [dateField] DESC

should return what you want.

btw, can you post you statements?


mfG
--> stefan <--

 

[Roger]

Thanks for your reply, but I am totally lost. I am in the design view of a
query ... should I enter what you have below somewhere else as vb code ?

Roger

hi Roger,

I have used the Group By "Last" option in my query for the date field
payment-date, and put a criteria in >(Now())-28, but for a couple of
clients I get strange results ... some payment-date results are within a
month and in 2 cases I have two "Last" records ... obviously I have done
something wrong. Can someone help me please

First of all, there is no "last" record without any order criteria.
Strange results? Are you using the LAST() function?


Something like

SELECT TOP 1 *
FROM [yourTable]
WHERE [dateField] > (Now() - 28)
ORDER BY [dateField] DESC

should return what you want.

btw, can you post you statements?


mfG
--> stefan <--

 

[vanderghast]

In SQL view of a query.



Vanderghast, Access MVP

Thanks for your reply, but I am totally lost. I am in the design view of a
query ... should I enter what you have below somewhere else as vb code ?

Roger

hi Roger,

I have used the Group By "Last" option in my query for the date field
payment-date, and put a criteria in >(Now())-28, but for a couple of
clients I get strange results ... some payment-date results are within a
month and in 2 cases I have two "Last" records ... obviously I have done
something wrong. Can someone help me please

First of all, there is no "last" record without any order criteria.
Strange results? Are you using the LAST() function?


Something like

SELECT TOP 1 *
FROM [yourTable]
WHERE [dateField] > (Now() - 28)
ORDER BY [dateField] DESC

should return what you want.

btw, can you post you statements?


mfG
--> stefan <--

 

[Roger]

Yes, I can see that now, thanks. It seems this way I can only get the one
field in the query. Am I approaching this the wrong way ? Should I be
looking at a report, in otherwords I use the Query to get the data that I
need from the table, ie Client, Payment, Payment Date and then in a report
based on the query find the last payment made ?

In SQL view of a query.



Vanderghast, Access MVP

Thanks for your reply, but I am totally lost. I am in the design view of
a query ... should I enter what you have below somewhere else as vb code
?

Roger

hi Roger,

Roger wrote:
I have used the Group By "Last" option in my query for the date field
payment-date, and put a criteria in >(Now())-28, but for a couple of
clients I get strange results ... some payment-date results are within
a month and in 2 cases I have two "Last" records ... obviously I have
done something wrong. Can someone help me please
First of all, there is no "last" record without any order criteria.
Strange results? Are you using the LAST() function?


Something like

SELECT TOP 1 *
FROM [yourTable]
WHERE [dateField] > (Now() - 28)
ORDER BY [dateField] DESC

should return what you want.

btw, can you post you statements?


mfG
--> stefan <--

 

[Roger]

Thanks for your help, but I'm afraid I can't get it to work ... so I have
made a report grouping each clients payments under the client name, and will
try to work from there.

Thanks again, .. Roger

Do you want the latest payment per client, or the latest of all payments?
If
the former you'll need to use a subquery to get the latest payment date
for
each client and restrict the query by this, e.g.

SELECT [ClientName], [PaymentDate], [Amount]
FROM [Clients] INNER JOIN [Payments] AS P1
ON [Clients].[Client Nr] = P1.[Client Nr]
WHERE [PaymentDate] <= DATEADD("d",-28,DATE())
AND [PaymentDate] =
(SELECT MAX([PaymentDate])
FROM [Payments] as P2
WHERE P2.[Client Nr] = P1.[Client Nr]);

Note how the two instances of the Payments table are differentiated by the
use of the aliases P1 and P2, allowing the subquery to be correlated with
the
outer query. The subquery will return the latest payment date for the
client
with the same client number as the outer query's current client. The
outer
query returns those rows where the payment date is 28 days ago or more,
and
where it equals the date returned by the subquery. Consequently if the
date
returned by the subquery is later than 28 days ago the outer query won't
return any row for that client, but otherwise will return the row with the
date which matches the date returned by the subquery.

Ken Sheridan
Stafford, England

Yes, I can see that now, thanks. It seems this way I can only get the one
field in the query. Am I approaching this the wrong way ? Should I be
looking at a report, in otherwords I use the Query to get the data that I
need from the table, ie Client, Payment, Payment Date and then in a report
based on the query find the last payment made ?

In SQL view of a query.

[quoted text clipped - 29 lines]

mfG
--> stefan <--