Access 2007: Avoid records for 180 days

[dubez]

Hi there!

I have records that are being appended to a new table. I would
like to have those records excluded from being used again for 180 days, at which
point they can be used again. Essentially, I have an ID and an email address
which gets stored in the Make Table. I would need to ensure that if we send an
email out in Week 1, we do not send an email again for another 180 days if there
is activity from that same ID. On day 181, the ID/email address can then be
resent.

Would I need an IF statement? If i have say, Table A where I store my records for week 1, how do I compare this with Week 2's table where there may be the same IDs? Furthermore, a record used in any week would need to be added back to main Table on day 181.

Please let me know if anyone can help.
Thanks!

 

[krausr79]

You may want to have a date field representing date of last contact for each email address. You could then query everything with a date more than 180 days old and use that as a starting point for whom you're emailing. This would be if email addresses are unique in the list.

It sounds like you may be maintaing a list of emails sent out with the address and date being a new record for each email sent. If this is what you have, then run a query that returns all unique IDs (Subquery 1) and a query that returns all unique IDs where any date is less than 180 days ago (Subquery 2) and join the two subqueries together on ID. Any record that has an ID, but not a 180 day ID is something that has not recieved an email in 180 days or more.

 

[dubez]

Thanks Krausr79!

I am still a bit confused though. What i have right now is the the Activity Date, ID and Email address. What I will do on a weekly basis is pull all the Activity Dates that occurred in the previous week, Week 1 will not be a problem as there are no records to match them to. But when Week 2, 3, 4 etc. comes I will need to map the ID and/or email address back to something. So, say I have ID- 25 in Week 1 as Activity occurred during that time period and ID-25 again in Week 2, my query or table will need to know that 25 should be excluded from the final list. On day 181 though, (if there's activity), ID- 25 should appear back in the list.

I know it may sound confusing and sorry for the trouble.

Thanks again!

 

[krausr79]

Ok, so if I follow, this is a list that is added to weekly. Each account gets 1 entry per week if there was activity. If there was activity this week, but no other activity for 180 days then also add it to a list of accounts to send email to.

I'm assuming you're working in Access. First, create a query that finds all accounts that did have activity in the last 180 days. Somthing like where date>certain date (and if the new week's data is already there: and date<>this week). You can make it a grouping query and only return the ID so that each id is mentioned once.

Next, create a query that gets the ID of all entries made this week. Something like where date=this week.

Next, make a query that joins those queries together. Use all ids from the second query left joined to all ids from the first query. Then ignore all lines where there is a result from the first query, like where ID1 is null. This should be a list of all IDs from last week that didn't have history anywhere 180 days before.

 

[dubez]

Thanks for the reply!

Ok, I dont know if im doing a good job explaining this so Ill show the tables and queries Im using.


Table 1:
Date Name Email ID
9/12/2012 Joe Blow Blank 3024-2n9k
9/13/2012 John Smith Blank 3077-1556
9/14/2012 Jane Doe Blank 3077-1556
9/15/2012 Bob Smith Blank 9917-17511


Table 1 is my raw data which I then query on using a parameter query to obtain the previous weeks Date criteria data.


Table 2:


Date Name Email ID Next Update
9/12/2012 Joe Blow Blank 3024-2n9k 27-Aug-15
9/12/2012 John Smith Blank 3077-1556 27-Aug-15
9/12/2012 Jane Doe Blank 3077-1556 27-Aug-15
9/12/2012 Bob Smith Blank 9917-17511 27-Aug-15


Table 2 is where I use the Dateadd function (displayed in the Next Update column) to determine when I can use these IDs again in my distribution. The Dateadd function is querying by adding 6 months to the day the query is run.


So, now the last part missing is where I compare Table 1 and Table 2 in finding the final email distribution list I need to send out, which excludes IDs that have been used in the last 6 months.


I was thinking to run a parameter query to pull the previous weeks data but I need to ensure the ID records from Table 2 are excluded (in this case until 27-Aug-15).


Any help would be greatly appreciated.