Time of Day Query

[Pugs320]

I work for a call center that does not make cold calls, but does call existing customers to remind them that they are due for a reorder. All contact info is put into a history_table including a Time/Date stamp. Is there any way to query this table to extract a time of day when successful contacts occur with respective customers on an historical basis, ie. after 3 or more successful contacts. We are trying to increase the success rate of contacting customers which will have many bottom line implications.

 

[cheerio]

The following query gives me a count for each hour of the day of the number of entries I created in a particular database table. It should give you a starting point. A key point to note is that I have used the Access Hour function to break the times down into 1 hour bands - you could use a custom function or an IF function for other groupings. Obviously you cannot count the times themselves as no two actions will be at exactly the same time.

Ken

SELECT
Hour([POSN_Create]) AS HourActioned,
Count([POSN_ID]) AS NumberDone
FROM Positions
GROUP BY POSN_Author, Hour([POSN_Create])
HAVING [POSN_Author]="Ken"
ORDER BY Hour([POSN_Create]);

 

[Pugs320]

Thanks for the reply, Cheerio. Your code is well beyond me at this point. I am still learning as I am sure we all are. I will keep on plugging away and try your suggestion. I will let you know of my success or failures. At this point I am not having too much success.

 

[cheerio]

ok before you try to solve your real world problem it is worth trying to solve something much simpler to prove the idea.

Create a table in a new Access database called Positions with the following fields:

POSN_ID      Autonumber
POSN_Create  Date/Time  - set the default value to =Now()
POSN_Author  Text (10 characters say)

Now populate the table with some dummy data. Each time you add a new row the default value of Now() will put in the current date/time - you can overtype to get different times of day. Add random names but include a few for Ken

Now go to the query window. Start a new query in design view. You can select the Positions table.

Now switch by going to SQL view and paste in my code above. You can then return to design view to see how it was created.

Now Run the query and you should get a profile of times for Ken.

 

[Pugs320]

This works just as you said it would. THANKS a lot. I think I can get this a few steps further and set it up to query all customers who were contacted over a given period of say six months. That should give me a sampling of customers contacted during that time period, how many times, and what hour of the day. I can build a table with that info and break the data down even further.
Thanks again Ken.

 

[cheerio]

I am glad I could help

Ken