Hello, I'm hoping you guys can help me out with what seems to be something easy in theory, I just don't know how to do it.
Let me start by saying I'm pretty new with access, I know how to edit some things and do some basics, but I don't know all the syntax and the ins and outs.
Example of what I have and what I need:
I have two tables, one with contributor names, and another with donation amounts. Each has other info also such as address, etc.
I have a report that runs a query that pulls all names, info, and donation history and puts the info on a printable 4x6 card format.
We change "campaigns" twice a year.
I need to have my report ONLY pull info between two dates, and completely disregard any and everything else, regardless of other contribution dates.
Example of what pulls and prints:
Jane Doe
123 Main St.
DateSold DateRecd Amount CampaignName AgentName
6/1/2012 6/5/2012 50 CampaignTwo2012 Bob
1/1/2012 1/5/2012 50 CampaignOne2012 Bob
6/1/2011 6/5/2011 50 CampaignTwo2011 Bob
1/1/2011 1/5/2011 50 CampaignOne2011 Bob
My current report works fine for the end of a campaign when I pull and print all records. What I need to do now though, is I need to pull and print the inbetweens, i.e. the ones who were sold at the end of CampaignOne but didn't pay until CampaignTwo, and as such did not get printed.
So I need to be able to pull from dates "1/1/2012 through 2/1/2012" while disregarding ALL records that have a sale AFTER 2/1/2012 and/or BEFORE 1/1/2012.
When I run the report, currently it will cutoff from the start date properly, but it isn't cutting off at my end date, resulting in me having records that are "too new".
Assuming I have these 3 records, I want to ONLY be able to pull record 1 (jane doe) and NOT 2 (john doe) or 3 (will smith). I only want to print records that were received between 1/3/2012 and 1/6/2012. Even though will smith was received also on 1/5/2012, he was also received again on 6/5/2012 so he needs to be omitted completely. John doe needs to be filtered out too because he also has history outside of my date range. I don't need to just filter out those other dates, I need the entire record to be filtered if it isn't a single time sale with a received date between my range.e
Jane Doe
123 Main St.
DateSold DateRecd Amount CampaignName AgentName
1/1/2012 1/3/2012 50 CampaignOne2012 Bob
John Doe
456 Main Rd.
DateSold DateRecd Amount CampaignName AgentName
1/2/2012 1/6/2012 50 CampaignOne2012 Bob
6/1/2011 6/5/2011 50 CampaignTwo2011 Bob
1/1/2011 1/5/2011 50 CampaignOne2011 Bob
Will Smith
789 Main Dr.
DateSold DateRecd Amount CampaignName AgentName
6/1/2012 6/5/2012 50 CampaignTwo2012 Bob
1/1/2012 1/5/2012 50 CampaignOne2012 Bob
6/1/2011 6/5/2011 50 CampaignTwo2011 Bob
1/1/2011 1/5/2011 50 CampaignOne2011 Bob
Here's my current query:
SELECT tblContributorsLead.PhoneNumber, tblContributorsLead.FirstName, tblContributorsLead.LastName, tblContributorsLead.Address1, tblContributorsLead.Address2, tblContributorsLead.ZipCode, tblPledgesLead.DispositionTime, tblPledgesLead.DateRecd, tblContributorsLead.CityName, tblContributorsLead.StateName, tblContributorsLead.ZipCode, tblContributorsLead.Comment, tblPledgesLead.Custom1, tblPledgesLead.Custom20, tblPledgesLead.PledgeAmountRecd, tblContributorsLead.EmailAddress, QPledgeOrder.DialingListOrder, tblPledgesLead.CampaignName
FROM (tblContributorsLead INNER JOIN tblPledgesLead ON tblContributorsLead.PhoneNumber = tblPledgesLead.PhoneNumber) INNER JOIN QPledgeOrder ON tblContributorsLead.PhoneNumber = QPledgeOrder.PhoneNumber
WHERE (((tblPledgesLead.DateRecd) Between #1/1/2012# And #2/1/2012#) AND ((tblPledgesLead.Custom1) Is Not Null) AND ((tblPledgesLead.PledgeAmountRecd)>"0"))
ORDER BY tblPledgesLead.DateRecd DESC , QPledgeOrder.DialingListOrder DESC;
I hope I've made this as clear as possible and would more than greatly appreciate any help so I can get this done. Thanks!
Let me start by saying I'm pretty new with access, I know how to edit some things and do some basics, but I don't know all the syntax and the ins and outs.
Example of what I have and what I need:
I have two tables, one with contributor names, and another with donation amounts. Each has other info also such as address, etc.
I have a report that runs a query that pulls all names, info, and donation history and puts the info on a printable 4x6 card format.
We change "campaigns" twice a year.
I need to have my report ONLY pull info between two dates, and completely disregard any and everything else, regardless of other contribution dates.
Example of what pulls and prints:
Jane Doe
123 Main St.
DateSold DateRecd Amount CampaignName AgentName
6/1/2012 6/5/2012 50 CampaignTwo2012 Bob
1/1/2012 1/5/2012 50 CampaignOne2012 Bob
6/1/2011 6/5/2011 50 CampaignTwo2011 Bob
1/1/2011 1/5/2011 50 CampaignOne2011 Bob
My current report works fine for the end of a campaign when I pull and print all records. What I need to do now though, is I need to pull and print the inbetweens, i.e. the ones who were sold at the end of CampaignOne but didn't pay until CampaignTwo, and as such did not get printed.
So I need to be able to pull from dates "1/1/2012 through 2/1/2012" while disregarding ALL records that have a sale AFTER 2/1/2012 and/or BEFORE 1/1/2012.
When I run the report, currently it will cutoff from the start date properly, but it isn't cutting off at my end date, resulting in me having records that are "too new".
Assuming I have these 3 records, I want to ONLY be able to pull record 1 (jane doe) and NOT 2 (john doe) or 3 (will smith). I only want to print records that were received between 1/3/2012 and 1/6/2012. Even though will smith was received also on 1/5/2012, he was also received again on 6/5/2012 so he needs to be omitted completely. John doe needs to be filtered out too because he also has history outside of my date range. I don't need to just filter out those other dates, I need the entire record to be filtered if it isn't a single time sale with a received date between my range.e
Jane Doe
123 Main St.
DateSold DateRecd Amount CampaignName AgentName
1/1/2012 1/3/2012 50 CampaignOne2012 Bob
John Doe
456 Main Rd.
DateSold DateRecd Amount CampaignName AgentName
1/2/2012 1/6/2012 50 CampaignOne2012 Bob
6/1/2011 6/5/2011 50 CampaignTwo2011 Bob
1/1/2011 1/5/2011 50 CampaignOne2011 Bob
Will Smith
789 Main Dr.
DateSold DateRecd Amount CampaignName AgentName
6/1/2012 6/5/2012 50 CampaignTwo2012 Bob
1/1/2012 1/5/2012 50 CampaignOne2012 Bob
6/1/2011 6/5/2011 50 CampaignTwo2011 Bob
1/1/2011 1/5/2011 50 CampaignOne2011 Bob
Here's my current query:
SELECT tblContributorsLead.PhoneNumber, tblContributorsLead.FirstName, tblContributorsLead.LastName, tblContributorsLead.Address1, tblContributorsLead.Address2, tblContributorsLead.ZipCode, tblPledgesLead.DispositionTime, tblPledgesLead.DateRecd, tblContributorsLead.CityName, tblContributorsLead.StateName, tblContributorsLead.ZipCode, tblContributorsLead.Comment, tblPledgesLead.Custom1, tblPledgesLead.Custom20, tblPledgesLead.PledgeAmountRecd, tblContributorsLead.EmailAddress, QPledgeOrder.DialingListOrder, tblPledgesLead.CampaignName
FROM (tblContributorsLead INNER JOIN tblPledgesLead ON tblContributorsLead.PhoneNumber = tblPledgesLead.PhoneNumber) INNER JOIN QPledgeOrder ON tblContributorsLead.PhoneNumber = QPledgeOrder.PhoneNumber
WHERE (((tblPledgesLead.DateRecd) Between #1/1/2012# And #2/1/2012#) AND ((tblPledgesLead.Custom1) Is Not Null) AND ((tblPledgesLead.PledgeAmountRecd)>"0"))
ORDER BY tblPledgesLead.DateRecd DESC , QPledgeOrder.DialingListOrder DESC;
I hope I've made this as clear as possible and would more than greatly appreciate any help so I can get this done. Thanks!
