Solved Most recent records query (1 Viewer)

[KACJR]

Greetings to the well of knowledge...

I'm at a loss on this particular query design.

We have a table of donors that contains the donor's name, the date of the donation, and the amount of the donation (among other fields). A user would like a list of all donors with the date and amount of the donors' most recent donation. A top values query is not what I'm looking for. Can someone offer a solution?

Thanks,
Ken

 

[jdraw]

I think you are looking for the latest/most recent donation.
Would have to Oder by DonationDate descending and group by Donor as a start.
Can you show us your query/sql?
Good luck.

 

[plog]

A subquery. However it sounds like your database isn't structured correctly.

You should have a donors table and a donations table. The donor table would simply list each donor, that table would have an autonumber primary key called DonorID. The donation table would then hold the DonorID value along with all the information about the donation including DonateDate.

Then you would use this query to get the most recent donation per donor:

SELECT DonorID, MAX(DonorDate) AS LastDonation FROM Donations GROUP BY DonorID

Then, you would use the above query and the Donation table in another query to get the data for the most recent donation. I fear you haven't set up your tables like this. Donor Name should not be a field in Donations, if it is I bet you have a lot of dirty data (e.g. Mike Smith and Michael Smith and Micheal Smith, etc.)

 

[KACJR]

Aaaaaand had I looked just below my post, I'd have found the answer. Sorry for clogging up the forum.

 

[KACJR]

A subquery. However it sounds like your database isn't structured correctly.

You should have a donors table and a donations table. The donor table would simply list each donor, that table would have an autonumber primary key called DonorID. The donation table would then hold the DonorID value along with all the information about the donation including DonateDate.

Then you would use this query to get the most recent donation per donor:

SELECT DonorID, MAX(DonorDate) AS LastDonation FROM Donations GROUP BY DonorID

Then, you would use the above query and the Donation table in another query to get the data for the most recent donation. I fear you haven't set up your tables like this. Donor Name should not be a field in Donations, if it is I bet you have a lot of dirty data (e.g. Mike Smith and Michael Smith and Micheal Smith, etc.)

Actually, my DB is set up just as noted. I have a main "people" table that contains salient information about the donor (like name, address, basic demographics). The table I was alluding to is, in fact, a donations table. It contains the date of the donation, the date it was responded to (thank you letter), what the donation was for, how much was donated, and which account it applies to.

 

[Pat Hartman]

The solution you need requires using a subquery.

The outer query selects the donors. The inner query orders the donations in descending order and selects the top 1. The main query and subquery are correlated on the donerID so you get the top 1 donation for each donor.

These types of queries need to be written in SQL. They are too complex for the QBE. I'm in a hurry. If you can't do it from the sescription, perhaps someone will help you out.

 

[theDBguy]

A top values query is not what I'm looking for.

I'd have found the answer.

You might consider posting the solution you ended up using for your problem, in case it helps others in the future. I am guessing it still ended up using a "top values" query.

 

[KACJR]

You might consider posting the solution you ended up using for your problem, in case it helps others in the future. I am guessing it still ended up using a "top values" query.

Solved - Query to Return Most Recent Date

Hello All, I seem to be stuck on this query. A Customer can have multiple AccountTypes which can have multiple entries by Date. I am trying to query the most recent Date for each AccountType per Customer. So, looking at the below sample I would want to return rows 3,4,&5 (row 1 is the field...

www.access-programmers.co.uk

 

[KACJR]

The solution you need requires using a subquery.

The outer query selects the donors. The inner query orders the donations in descending order and selects the top 1. The main query and subquery are correlated on the donerID so you get the top 1 donation for each donor.

These types of queries need to be written in SQL. They are too complex for the QBE. I'm in a hurry. If you can't do it from the sescription, perhaps someone will help you out.

As noted above, I found the solution in another thread on the forum.

 

[Pat Hartman]

I guess you didn't understand my suggestion. Sorry, I didn't have time to code and test an example but I assure you, it would have worked. The Max() works also. The problem with it is you can only bring in one value from that record, whereas using the method I suggested with a subquery, you would have been able to bring in the entire "max" record if you had wanted to.

Thanks for linking to the solution you chose. It always helps people who search the forum to know what actually worked.