Deduping daily list

[cmcreynolds]

Hello ye helpful people!

A new task has been given to me and I am to complete it in Access only (with a final output to Excel). I can conceive of what to do if I could use Power Query, but I am not able to right now in my position. Here's my problem and I'll save my "powerquery" comparison for later, if it would even help at all.

Every day I'm pulling from our database a list of all the calls logged into our customer service center from the day before. We are going to be sending out a survey every day asking about their experience. HOWEVER, we don't want to send out a survey to anyone who has called twice in the past 7 days. (We have member IDs and I use those as unique identifiers for those who call in.)

Right now, my Access query grabs yesterday's data and I append it to table (as opposed to making a table for every day?).

How can I pull the data, check that a person hasn't already received a survey in the past 7 days, and output an Excel spreadsheet to go to our survey vendor?

Thoughts?

Thank you so much.

 

[Joe4]

Create a new query and add the Member ID field twice, an the Call Date field once.
In the Query Builder, click on the Totals button, which looks like a Sigma. This will add a Totals row, with the words "Group By" under each field.
Under the SECOND Member ID, change "Group By" to Count.
Under the Call Date field, change "Group By" to "Where" and enter this under the Criteria for that field:

>Date()-7

This will limit the list to the users who called within the past 7 days

If you want to limit it to those who have only called once within that time frame, enter:

=1

under the second Member ID field where you are returning the counts for each member.

 

[cmcreynolds]

Cool, so I don't have to even append to an existing table any more because this will pull and filter the "only 1s"!

Awesome! This exactly how I was going to do it in Power Query, just wasn't sure where to apply my thoughts in Access.

Thank you, Joe4.

 

[Joe4]

You are welcome.

Yes, dynamic aggregate queries are really useful little things!

I have never used Power Query before, but I have used SQL and Access, and I imagine that a lot of the query tools have similar functionality, though the syntax is a little different.
So I would be willing to bet that most things you can do in one query tool, you can do in others. Its just a matter of getting the syntax right.

 

[cmcreynolds]

Okay, I ran into a glitch, but maybe I'm misunderstanding where to apply Joe4's query:

If I use this additional query to take out those who have called twice (the #2 option in the Join Properties), it won't differentiate between a person calling on two separate days vs. twice in one day (which happens somewhat frequently). Do I use a different join, maybe?

Or is it just use your query to take them out if they "called another day" and lump together all their calls for one day.

 

[Joe4]

Does your date field contain a time component? If so, you may need an intermediate query (which you can actually do in the same query as a nested query, but that can get a little tricky for noobs).

Do that by adding just the Member ID field and then the following calculated field, which removes the time part of the date:
DateOnly:Int([DateField])

Now, click on the Totals button so that it Groups those records by both of these fields.
Then, use this new query as the basis/data source for your other query.

 

[cmcreynolds]

Yeah, I'm thinking it's a separate issue when people call in twice in one day.

The time stamp is truncated when I pull it in from our system, which leaves me with listing people twice with only a date. There IS, however, a sequence number that I can bring it to distinguish between the two phone calls. Is there a way to use MAX(SequenceID) or something to restrict to just one of the calls in one day?

 

[Joe4]

It shouldn't be necessary. If there is no time piece tied to the Date field, then simply do what I suggested in the previous post, only you do not need the Calculated Field. Just add the Date Field and group on that instead.

So that query will just list each member and the days they called on (with only one record per day, regardless of how many times they called).
Then, you can use this query as the basis/control source for the original query I explained to you.

 

[cmcreynolds]

Got it. I just changed my main query use Totals and Maxed the sequence number.

Thank you for your help again!

 

[Joe4]

You are welcome.

Got it. I just changed my main query use Totals and Maxed the sequence number.

That would work too, though it may not really be necessary since don't really need the sequence number for anything.
Essentially, you are just trying to collapse it down to no more than one record per member per day. So you could do it that way, or the way I mentioned in my previous post.