last entry in a field(Max) problem

[Sulaiman]

Hi All

How do I get the last entry in a field.


As I was reading the forums they said I should use the Max value it worked kinda but the problem is the corresponding amount banked is not the corresponding data to Max(date banked). How do I resolve the problem. How can I manipulate amount banked so it shows the correct values.
SELECT DonorDetailsIndividual.DonorName, DonorDetailsIndividual.DonorSurname, DonorDetailsIndividual.Visitor, DonorHistoryIndividual.BankedAmount, Max(DonorHistoryIndividual.DateBanked) AS MaxOfDateBanked
FROM DonorDetailsIndividual INNER JOIN DonorHistoryIndividual ON DonorDetailsIndividual.ID = DonorHistoryIndividual.ID
GROUP BY DonorDetailsIndividual.DonorName, DonorDetailsIndividual.DonorSurname, DonorDetailsIndividual.Visitor
HAVING (((DonorDetailsIndividual.Visitor)="Elections"));

[JoeM]

Do this in a two-step process (either two queries, or a nested subquery).

First query: Create an Aggregate Query to group the records and return the Max Date value.
Second query: Link the first query to your original table (be sure to also include the Date field in your linking) to return the amount associated with that particular date.

[Sulaiman]

Do this in a two-step process (either two queries, or a nested subquery).

First query: Create an Aggregate Query to group the records and return the Max Date value.
Second query: Link the first query to your original table (be sure to also include the Date field in your linking) to return the amount associated with that particular date.

I am still having difficulty and the data still not correct.

My problem is that I have three tables.
I did groupby first and second table and made the third table last.
In the second table I made the max (date) leaving the groupby amount banked.

I then created a a second query with which table.Do I link it the new query table with table one as it is the parent table and must I link with the id.

[JoeM]

In the first query, you should NOT return the amount field at all. You just want to return the fields that you are grouping by, and the date field.
The second query links back the amounts pertaining to the particular dates determined in the first query.

If you still need help figuring it out, posting some small examples of your data tables and expected results may be helpful.

[Sulaiman]

test1.mdbHI

I have attached some data. Still looking for the last AmountBanked and corresponding amount.

It will be highly appreciative if you can help me.

[JoeM]

I was actually hoping for screen prints, rather than file attachments. From my current location, corporate policy prohibits me from downloading any files from the internet, for security reasons.