Select Max Date from 1 Table

  • Thread starter Thread starter nbaker
  • Start date Start date
N

nbaker

Help Please!

I have the table 'Planned_Givers_No_Dups that contains multiple rows
per ID. i would like to return all fields where the date is most
recent.

SELECT Planned_Givers_No_Dups.[Constituent ID],
Planned_Givers_No_Dups.[Gift Amount], Planned_Givers_No_Dups.[Gift
Date], Planned_Givers_No_Dups.[Gift Type], Planned_Givers_No_Dups.[Gift
Instalment Frequency], Planned_Givers_No_Dups.[Gift Payment Type]
FROM Planned_Givers_No_Dups;

any help would be much apprecitated.

Regards
 
Assuming that GiftDate is actually a Date/Time field and not text:

SELECT TOP 1
Planned_Givers_No_Dups.[Constituent ID],
Planned_Givers_No_Dups.[Gift Amount],
Planned_Givers_No_Dups.[GiftDate],
Planned_Givers_No_Dups.[Gift Type],
Planned_Givers_No_Dups.[GiftInstalment Frequency],
Planned_Givers_No_Dups.[Gift Payment Type]
FROM Planned_Givers_No_Dups
ORDER BY Planned_Givers_No_Dups.[GiftDate] DESC;

TOP 1 gives the top value; however, if there is a tie it allows in all the
equal values. Sorting in descending order makes sure that you see the most
recent date.
 
Try this, where we use a subquery in the WHERE clause:

SELECT Planned_Givers_No_Dups.[Constituent ID],
Planned_Givers_No_Dups.[Gift Amount], Planned_Givers_No_Dups.[Gift
Date], Planned_Givers_No_Dups.[Gift Type], Planned_Givers_No_Dups.[Gift
Instalment Frequency], Planned_Givers_No_Dups.[Gift Payment Type]
FROM Planned_Givers_No_Dups
WHERE Planned_Givers_No_Dups.[Gift Date] =
(SELECT Max(T.[Gift Date])
FROM Planned_Givers_No_Dups AS T);
 
hi Jerry

i have trie the query you have suggested and the query returns only 2
rows where the date is the highest.


below is a snapshot of the dataset. i need all rows but only where the
Gift Date is the Highest i.e. for constituent ID 164045 i require the
row where the date is 17/11/2001.

ConstituentID GiftAmount GiftDate GiftType GiftInstalmentFrequency
GiftPayment Type
164045 £0.50 11/12/1996 Recurring Gift Monthly Standing Order
164045 £8.00 17/11/2001 Recurring Gift Monthly Standing Order
164055 £75.00 01/03/1999 Recurring Gift Annually Standing Order
164061 £100.00 16/06/1993 Recurring Gift Annually Standing Order
164095 £8.00 16/05/2001 Recurring Gift Monthly Standing Order
164136 £8.00 15/09/1999 Recurring Gift Monthly Standing Order
164136 £2.50 20/02/1995 Recurring Gift Semi-Annually Standing Order
164159 £5.00 08/12/1996 Recurring Gift Monthly Standing Order
164256 £4.00 07/01/2003 Recurring Gift Monthly Standing Order
164287 £5.00 19/05/1988 Recurring Gift Monthly Standing Order
164287 £5.00 02/10/1992 Recurring Gift Monthly Standing Order
164287 £4.00 01/04/2001 Recurring Gift Monthly Standing Order
164326 £6.00 20/12/1996 Recurring Gift Monthly Standing Order


thank you

N
 
Or, if you're looking for the most recent record for each giver in the
table:

SELECT Planned_Givers_No_Dups.[Constituent ID],
Planned_Givers_No_Dups.[Gift Amount], Planned_Givers_No_Dups.[Gift
Date], Planned_Givers_No_Dups.[Gift Type], Planned_Givers_No_Dups.[Gift
Instalment Frequency], Planned_Givers_No_Dups.[Gift Payment Type]
FROM Planned_Givers_No_Dups
WHERE Planned_Givers_No_Dups.[Gift Date] =
(SELECT Max(T.[Gift Date])
FROM Planned_Givers_No_Dups AS T
WHERE T.[Constituent ID]=Planned_Givers_No_Dups.[Constituent ID]);

--

Ken Snell
<MS ACCESS MVP>

Ken Snell (MVP) said:
Try this, where we use a subquery in the WHERE clause:

SELECT Planned_Givers_No_Dups.[Constituent ID],
Planned_Givers_No_Dups.[Gift Amount], Planned_Givers_No_Dups.[Gift
Date], Planned_Givers_No_Dups.[Gift Type], Planned_Givers_No_Dups.[Gift
Instalment Frequency], Planned_Givers_No_Dups.[Gift Payment Type]
FROM Planned_Givers_No_Dups
WHERE Planned_Givers_No_Dups.[Gift Date] =
(SELECT Max(T.[Gift Date])
FROM Planned_Givers_No_Dups AS T);

--

Ken Snell
<MS ACCESS MVP>

Help Please!

I have the table 'Planned_Givers_No_Dups that contains multiple rows
per ID. i would like to return all fields where the date is most
recent.

SELECT Planned_Givers_No_Dups.[Constituent ID],
Planned_Givers_No_Dups.[Gift Amount], Planned_Givers_No_Dups.[Gift
Date], Planned_Givers_No_Dups.[Gift Type], Planned_Givers_No_Dups.[Gift
Instalment Frequency], Planned_Givers_No_Dups.[Gift Payment Type]
FROM Planned_Givers_No_Dups;

any help would be much apprecitated.

Regards
Click to expand...
Click to expand...
 
Hi Ken

i am trying to run the query again based on a larger table containing
648,086 records and access seems to be falling over. could you give any
suggestions for a quicker run of the query.

Cheers

Neil
 
Is Constituent_ID an indexed field in your source table? If not, make it
one.

If it is, then you might have to use some programming to write the Max
values for each Constituent_ID to a temporary table, and then join that
table in your query. Using an actual table instead of a subquery is almost
always faster if you cannot index the "search" field in the original table
from which the subquery pulls its data.
 
Back
Top