How do I find the last record ?

R

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
 
S

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 <--
 
R

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

Stefan Hoffmann said:
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 <--
 
V

vanderghast

In SQL view of a query.



Vanderghast, Access MVP


Roger said:
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

Stefan Hoffmann said:
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 <--
 
R

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 ?

vanderghast said:
In SQL view of a query.



Vanderghast, Access MVP


Roger said:
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

Stefan Hoffmann said:
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 <--
 
K

KenSheridan via AccessMonster.com

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]
 
R

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

KenSheridan via AccessMonster.com said:
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 <--
 
K

KenSheridan via AccessMonster.com

Roger:

There is another way, which is much the same in principle as using a subquery,
but can be done entirely via the query design interface rather than writing
an SQL statement:

1. Create a 'totals' query based on the payments table which returns the
Client Nr and Payment Date columns, grouping by Client Nr and select Max as
the 'Total' for the Payment Date column, and in its 'criteria' row enter
<=DateAdd("d",-28,Date()). This will create a query with a HAVING clause.
Unlike a WHERE clause a HAVING clause operates after the grouping so this
will return only those clients whose latest payment date is 28 or more days
ago.

2. Create another query in which you join the Payments table to the above
query on both the Client Nr and Payment Date columns. Join the Payments
table to the Clients table on Client Nr in the usual way and include whatever
columns you need from Clients and Payments. By virtue of the join between
payments and the query created in 1 above, only those rows will be returned
where there is a match, i.e. those clients, along with their latest payment,
where the latest payment is 28 or more days ago. Base your report on this
query.

Ken Sheridan
Stafford, England
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
[quoted text clipped - 40 lines]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top