Can any 1 please help me with this query?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Can any 1 please help me with this query??

Author

Topic

babyface
Starting Member

4 Posts

Posted - 2004-03-19 : 15:41:46

Hi there,

I hope that some database guru in this forum can shed some light into this one. I really need some advice so here it goes.

I have 2 tables in MS access (contact) and (pledges). These 2 tables linked by a common field (contactID). The question is: I need to find all records that contribute or pledge in 2001 and 2002 but not in 2003. I have worked on this query since this morning and still not getting the right result (that why I turn to you).

Here is the query that I had:

SELECT DISTINCT TblContact.ContactID, Pledges.[Deposit Date]
FROM Pledges RIGHT JOIN TblContact ON Pledges.ContactID = TblContact.ContactID
WHERE ((TblContact.DonationContributor=Yes) AND ((Pledges.[Deposit Date]) Between #1/1/2001# And #12/31/2002#))
AND NOT ((Pledges.[Deposit Date]) Between #1/1/2003# And #12/31/2003#)
ORDER BY TblContact.ContactID;

Does any 1 in here has any idea that can share with me or pointing out my real problem in the query ??????

Some results from the query:

I ran a different query just for 2003 for checking purposes and get this.

ContactID Date Pledged
1 12/31/2003
7 12/9/2003

When run the query above; I've got this:

ContactID Date Pledged
7 6/17/2002
7 9/5/2002
20 12/17/2001

This is not right, record 7 should not be display at all because it already contribute in 2003.

Thanks in advance and hope that some kind soul can give me some light into this one because I am stuck.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-03-19 : 15:59:32

try:

SELECT DISTINCT TblContact.ContactID, Pledges.[Deposit Date]
FROM Pledges RIGHT JOIN TblContact ON Pledges.ContactID = TblContact.ContactID
WHERE ((TblContact.DonationContributor=Yes) AND ((Pledges.[Deposit Date]) Between #1/1/2001# And #12/31/2002#))
and tblcontact.contactid not in (
SELECT DISTINCT TblContact.ContactID
FROM Pledges JOIN TblContact ON Pledges.ContactID = TblContact.ContactID
WHERE ((TblContact.DonationContriutor=Yes) AND ((Pledges.[Deposit Date]) Between #1/1/2003# And #12/31/2003#))
ORDER BY TblContact.ContactID;

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-03-19 : 16:02:13

Or maybe this depending on your knowledge of the data in the tables.

SELECT DISTINCT TblContact.ContactID, MAX(Pledges.[Deposit Date])
FROM Pledges RIGHT JOIN TblContact ON Pledges.ContactID = TblContact.ContactID
WHERE ((TblContact.DonationContributor=Yes)
GROUP BY TblContact.ContactID
HAVING MAX(Pledges.[Deposit Date]) < 1/1/2003

babyface
Starting Member

4 Posts

Posted - 2004-03-19 : 22:03:25

Dear Drymchaser,

Thank you so much for lend me a hand on this. It works like a charm. Thanks again for the tip.

bf

quote:
Originally posted by drymchaser

try:

SELECT DISTINCT TblContact.ContactID, Pledges.[Deposit Date]
FROM Pledges RIGHT JOIN TblContact ON Pledges.ContactID = TblContact.ContactID
WHERE ((TblContact.DonationContributor=Yes) AND ((Pledges.[Deposit Date]) Between #1/1/2001# And #12/31/2002#))
and tblcontact.contactid not in (
SELECT DISTINCT TblContact.ContactID
FROM Pledges JOIN TblContact ON Pledges.ContactID = TblContact.ContactID
WHERE ((TblContact.DonationContriutor=Yes) AND ((Pledges.[Deposit Date]) Between #1/1/2003# And #12/31/2003#))
ORDER BY TblContact.ContactID;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources