sql question (using First, not working)

M

Mark Andrews

I must be brain dead today, here is a sql question maybe someone can help.

I have two tables (tblContact and tblDonation) and I'm trying to calculate
the
- firstdonationdate
- lastdonationdate
- LastDonationAmount

Using Min and max for first and last donation dates (works fine)
Trying to use First (with a DESC sort by Donationdate) to get the first
donation amount (which would be the LAST donation made by the contact).
This part is NOT working.
Example: Contact Joe Smith has 3 donations
July 3, 2009 $500
Aug 5, 2009 $600
Sep 7, 2009 $700
The last donation was $700


-------------------------
First try at sql:
-------------------------
SELECT tblContact.ContactID, Min(tblDonation.DonationDate) AS
FirstDonationDate, Max(tblDonation.DonationDate) AS LastDonationDate,
Nz(First([DonationAmount]),0) AS LastDonationAmount
FROM tblContact LEFT JOIN tblDonation ON tblContact.ContactID =
tblDonation.ContactID
GROUP BY tblContact.ContactID
ORDER BY Max(tblDonation.DonationDate) DESC;


-------------------------
second try qryDonationSortedByDate is tblDonation.* sorted desc by
Donationdate
-------------------------
SELECT tblContact.ContactID, Min(qryDonationSortedByDate.DonationDate) AS
FirstDonationDate, Max(qryDonationSortedByDate.DonationDate) AS
LastDonationDate, Nz(First([DonationAmount]),0) AS LastDonationAmount
FROM tblContact LEFT JOIN qryDonationSortedByDate ON tblContact.ContactID =
qryDonationSortedByDate.ContactID
GROUP BY tblContact.ContactID;

Thanks in advance,
Mark
 
R

Roger Carlson

M

Mark Andrews

Thanks. Got it working.

Mark

Douglas J. Steele said:
You need to use a subquery. While I don't think he covers your particular
situation, take a look at what Allen Browne has at
http://www.allenbrowne.com/subquery-01.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Mark Andrews said:
I must be brain dead today, here is a sql question maybe someone can help.

I have two tables (tblContact and tblDonation) and I'm trying to
calculate the
- firstdonationdate
- lastdonationdate
- LastDonationAmount

Using Min and max for first and last donation dates (works fine)
Trying to use First (with a DESC sort by Donationdate) to get the first
donation amount (which would be the LAST donation made by the contact).
This part is NOT working.
Example: Contact Joe Smith has 3 donations
July 3, 2009 $500
Aug 5, 2009 $600
Sep 7, 2009 $700
The last donation was $700


-------------------------
First try at sql:
-------------------------
SELECT tblContact.ContactID, Min(tblDonation.DonationDate) AS
FirstDonationDate, Max(tblDonation.DonationDate) AS LastDonationDate,
Nz(First([DonationAmount]),0) AS LastDonationAmount
FROM tblContact LEFT JOIN tblDonation ON tblContact.ContactID =
tblDonation.ContactID
GROUP BY tblContact.ContactID
ORDER BY Max(tblDonation.DonationDate) DESC;


-------------------------
second try qryDonationSortedByDate is tblDonation.* sorted desc by
Donationdate
-------------------------
SELECT tblContact.ContactID, Min(qryDonationSortedByDate.DonationDate) AS
FirstDonationDate, Max(qryDonationSortedByDate.DonationDate) AS
LastDonationDate, Nz(First([DonationAmount]),0) AS LastDonationAmount
FROM tblContact LEFT JOIN qryDonationSortedByDate ON tblContact.ContactID
= qryDonationSortedByDate.ContactID
GROUP BY tblContact.ContactID;

Thanks in advance,
Mark
Click to expand...
Click to expand...
 
M

Mark Andrews

Thanks Roger already fixed it before I saw your post.

Note: I did look at your example and I couldn't do it exactly like either of
the ways you have in your example.
I ended up with a query with a subquery (that shows tblContacts.* and one
extra column of LastDonationdate) to replace my tblContact in my main query.

Mark

Roger Carlson said:
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "MaxQueryProblem.mdb" which illustrates how to do this. You
can download it for free here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=326

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Mark Andrews said:
I must be brain dead today, here is a sql question maybe someone can help.

I have two tables (tblContact and tblDonation) and I'm trying to
calculate the
- firstdonationdate
- lastdonationdate
- LastDonationAmount

Using Min and max for first and last donation dates (works fine)
Trying to use First (with a DESC sort by Donationdate) to get the first
donation amount (which would be the LAST donation made by the contact).
This part is NOT working.
Example: Contact Joe Smith has 3 donations
July 3, 2009 $500
Aug 5, 2009 $600
Sep 7, 2009 $700
The last donation was $700


-------------------------
First try at sql:
-------------------------
SELECT tblContact.ContactID, Min(tblDonation.DonationDate) AS
FirstDonationDate, Max(tblDonation.DonationDate) AS LastDonationDate,
Nz(First([DonationAmount]),0) AS LastDonationAmount
FROM tblContact LEFT JOIN tblDonation ON tblContact.ContactID =
tblDonation.ContactID
GROUP BY tblContact.ContactID
ORDER BY Max(tblDonation.DonationDate) DESC;


-------------------------
second try qryDonationSortedByDate is tblDonation.* sorted desc by
Donationdate
-------------------------
SELECT tblContact.ContactID, Min(qryDonationSortedByDate.DonationDate) AS
FirstDonationDate, Max(qryDonationSortedByDate.DonationDate) AS
LastDonationDate, Nz(First([DonationAmount]),0) AS LastDonationAmount
FROM tblContact LEFT JOIN qryDonationSortedByDate ON tblContact.ContactID
= qryDonationSortedByDate.ContactID
GROUP BY tblContact.ContactID;

Thanks in advance,
Mark
Click to expand...
Click to expand...
 

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

Similar Threads

Query/Iff/Max not working right 1
Previous Value Subquery not working as needed 2
First() won't work - subquery question 4
SQL to determine Start Date and End Date for consecutive dates 1
translating Access sql to mysql 3
Best athletes and not best scores. Problem with SQL command 2
using NZ() function to return ''0'' value when query is null 1
Running Total in SQL query not working as needed 5

Top