query for the most recent date

[Guest]

Hello and thanks for the help. I am using Access 2003. I posted this same
question last week but I could not understand the answers. So please baby me
along if you put any code in the answer. I do NOT do code and have no idea
where or how to type the information in the answers I received.

I have a table that holds credit card payments. The name of the table is
VISA. I record the date the payment was made and the amount of the payment
(along with about 7 other fields). I would like to run a query that ALWAYS
gives me the most recent payment based on the most current date. The field
holding the dates is named DATE.

Thanks again for the time and the help ! I love this newsgroup!

 

[Guest]

You can create a query sort by the [Date] field, Desc, that way you'll get
all the recent payment first in the resault of the query. and you can add the
Top Numbers to get only a certain amount of records.

e.g - theis query the 50 recent payment

Select Top 50 VISA.* From VISA Order By [Date] Desc

I would change the [Date] field name, because it a reserved name in Access.
===================================================

 

[peregenem]

please baby me
along if you put any code in the answer. I do NOT do code

I have a table that holds credit card payments. The name of the table is
VISA. I record the date the payment was made and the amount of the payment
(along with about 7 other fields). I would like to run a query that ALWAYS
gives me the most recent payment based on the most current date. The field
holding the dates is named DATE.

If you have a mental block then don't think of the following as SQL,
think of it as a strange dialect of English

SELECT MAX([Date]) FROM Visa

[Date] is the column (escaped in brackets because it is a reserved
word) in your table named Visa, so the above will find the value of the
highest [Date].

However, you specified that is should relate to the current date. You
can get the current date from the system using NOW()

SELECT MAX([Date]) FROM Visa WHERE [Date] <= NOW()

Again, this will return a single value. This time, future dates (as at
time of execution) will not be considered (the expression '<=' means
'less than or equal to').

You then need to relate this back to your original table

SELECT T1.* FROM Visa AS T1 WHERE T1.[Date] = (
SELECT MAX([Date]) FROM Visa WHERE [Date] <= NOW()
)

Using the table a second time means you must specify a correlation
name; I've used T1. Note the above could return more than one row.

SELECT T1.* returns all columns but it is not to be used in production
ocde. Replace it with a list of the desired columns.

 

[John Spencer (MVP)]

You need to give us a bit more information.

Do you want the most recent payment in the entire table or
do you want the most recent payment for each individual in the table?