Query to a

[dubez]

Hi there!

I have received much help here in the past and was hoping I could get an answer to this problem I'm having.

I have records using a select query that I am sending to a make 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.

It may be something very simple I am just overlooking but any insight would be greatly appreciated.

 

[pbaldy]

You can add a date field that your process sets to the current date when it uses a record. Then you can use that field to exclude records where the date is younger than 180 days.

 

[dubez]

Thanks Paul, that really helps! Can you please elaborate on this process though? Would I need to make a query and use an IF statement to gather only the valid records in week 2?

 

[pbaldy]

I don't know your process, so those were just generic thoughts. With a query you'd use a criteria along the lines of

<= Date()-180

 

[dubez]

So, if we assume Table A is where I would store all my ID/Email Data for Week 1 and Table B is my Final Email list to be sent out, what would my next steps be?

In Week 1 when the email list is sent I will have nothing to matchup the records against. However, in Week 2,3,4, etc. will I will need to run a query which looks at Table B and uses <= Date()-180? If so, would this not remove records I may possibly need? Again, I would just need to ensure that if a record (Email address) showed up in Week 1, it does not show up again for another 180 days.

Sorry to sound annoying and repeat myself, I just really need to figure this one out soon.

Thanks again,

 

[pbaldy]

You should be able to add an Or Is Null to the criteria to get the records with no date.

 

[dubez]

Re: Query to avoid using certain date fields

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.