First call resolution

[Number101]

Hi I'm new to all vba stuff, so I need help creating a code for clients, that have called more than once in 24 hours. Basicly I have data with client number and call date, The problem that I can't solve is, if client called after more than 24 hours I need to start new cycle of repeated calls in a new 24 hour interval.:banghead:

 

[plog]

Can you better describe what you hope to achieve in terms of your data. "start a new cycle of repeated calls" doesn't help much without a frame of reference for your system.

Do you hope to end up with a query? Can you demonstrate your issue and expectations with data? Include table and field names.

 

[Number101]

Yes I hope to end up with a query, My original data is in excel it looks like this:
Call timeClient_number2017.03.10888732712017.03.16888732712017.03.16888732712017.03.17888732712017.04.04888732712017.04.14888732712017.04.19888732712017.04.19888732712017.04.1988873271
I have imported this data to ms access and I hope to achieve query, that would look like:
ClientFirst_call_dateNext_call_dateFCR_168h888732712017.03.102017.03.100888732712017.03.102017.03.160888732712017.03.102017.03.160888732712017.03.102017.03.170888732712017.03.102017.04.040888732712017.03.102017.04.140888732712017.03.102017.04.190888732712017.03.102017.04.190888732712017.03.102017.04.190FCR_168_h should be 1 if call is repeated in 168 hour interval. So It should calculate FCR in 03.10-03.16 (03.17 should not count, because it is last call in 168 hour interval) and then FCR should count again from 04.04 in 168 hour interval, and so on. I wrote explanation in 168 hour FCR because its easier than 24 hours

 

[The_Doc_Man]

The very first thing I see is Excel-think. You are making data records that are shallow but very wide, with many call entries across a single row.

In Access, the normal method is narrow but deep, where you have many data records that identify the customer and the call time in single records. It is almost impossible to manipulate row-oriented data efficiently in Access. Not to mention that with Excel it is hard to know when the row has to stop if you have a particularly persistent caller.

I VERY STRONGLY urge you to review topics on database normalization before trying to go too much deeper into the quicksand you are trying to enter.

 

[Number101]

So I'm attaching the pictures of the original data and what I like to achieve.

 

[Frothingslosh]

You definitely have normalization issues. There's also a concern in why you're saving a 'First Call Date' that apparently never, ever changes, and then ALSO saving a 'next call date'?

Normally you would do something like having one 'Clients' table with all the data unique to the client specifically (name, address, possibly phone number, primary contact, billing info, etc) plus a primary key (ClientID as an Autonumber is a good default unless you already have something else), and a separate 'Calls' table with a date/time field, a CallID field as an Autonumber (primary key), and any data specific to each call - caller name, you can have caller phone number if there's any possibility at all that the calls can come from different numbers, who took the call, etc. Maybe a Call Start and Call End instead of Call Date.

Regardless, instead of saving just a date (function Date() ), you save date and time (function Now() ). You can always screen out the time data later if you just need the date, that format would give you much more control over the call data you pull for reports.

Even if you refuse to save times, a separate primary key will allow you to save each call's data independently, meaning you no longer need to explicitly group calls on one day together. You can do that on the fly when you build queries for reporting.

I *VERY* strongly second the recommendation that you read up on normalization. Don't get caught up in the buzzwords and technobabble (it's easy to do on that topic) - just keep in mind that it's about keeping the focus of each table narrowed to one thing. If you have any questions about normalization, ask here and we'll do our best to explain in normal-people-speak.

 

[plog]

Agree with frothing--you need to fix the big issue you have that underlies what you posted about. You need to properly structure your data.

Also, are your date fields actually dates? In design view of the table are they actually date/time datatypes?

 

[Number101]

Mayby I explaned it wrong. I have a large table (100000 + entries) which have call records. How can I count the instances of re-occurrence within a set number of hours?



My original data is:
1st column: call Date (date and time format) 2nd column: Caller ID; 3rd column person who answered.
My problem is that I cant count it in loop (if a person calls:
1. 2017.05.12 07:00 (Should count as reoccurrence, because after that client called again in168 hour period.)
2. 2017.05.18 17:00 (Should count as reoccurrence, because after that client called again in 168 hour period from the first call)
3. 2017.05.18 19:00 (Shouldn't count as reoccurrence, because client called again but it was after 168 hour period from first call)
4. 2017.05.25 10:15 (Should count as reoccurrence, because after that client called again in 168 hour period(start a new 168 hour cycle))
5. 2017.05.26 15:17 (Shouldn't count as reoccurrence, because client didn't called again))
I need all the help I can get

In some forum I found a code for sql server that looks about right but I cant adapt it to access:

declare @t table(Record_ID int, Customer_ID int, StartDateTime datetime, FinishDateTime datetime)

insert @t values(1 ,123456,'2010-04-24 16:49','2010-04-25 13:37')
insert @t values(3 ,654321,'2010-05-02 12:45','2010-05-03 18:48')
insert @t values(4 ,764352,'2010-03-24 21:36','2010-03-29 14:24')
insert @t values(9 ,123456,'2010-04-28 13:49','2010-04-30 09:45')
insert @t values(10,836472,'2010-03-19 19:05','2010-03-20 14:48')
insert @t values(11,123456,'2010-05-05 11:26','2010-05-06 16:23')

declare @days int
set @days = 7

;with a as (
select record_id, customer_id, startdatetime, finishdatetime,
rn = row_number() over (partition by customer_id order by startdatetime asc)
from @t),
b as (
select record_id, customer_id, startdatetime, finishdatetime, rn, 0 recurrence
from a
where rn = 1
union all
select a.record_id, a.customer_id, a.startdatetime, a.finishdatetime,
a.rn, case when a.startdatetime - @days < b.finishdatetime then recurrence + 1 else 0 end
from b join a
on b.rn = a.rn - 1 and b.customer_id = a.customer_id
)
select record_id, customer_id, startdatetime, recurrence from b
where recurrence > 0

 

[The_Doc_Man]

Using Access, the problem will start by a query that presents your records in a specific order... ORDER BY Customer, StartTime. Whatever else you want in the record is up to you, of course.

Next, since you are talking about what is commonly called a "sliding window" you cannot use any of the Date-related functions that would block these out into week number or such as that. So you have to go through ever one of these frimpin' records one at a time, which won't be fast on the best day of your life.

Finally, you need to better define what you intend to do about the counting situation. Let's take a case where the customer calls three times in the 168-hour window. What numbers should apply to each record?

For example: Does the first call get 2 in the "subsequent calls" column and the second call get 1 in the "subsequent calls" column and the third call has 0? Or perhaps this is reversed and you get 0 for first call, 1 for second call, and 2 for the third call in the "recent calls" column? Or do all three calls get a 2 in the "associated calls" column. See, it makes a difference in how you want to count it.

If your answer is either the first or second of the two options, an SQL solution might be possible, which would allow this dog to run in a reasonable time.

 

[Number101]

Finally, you need to better define what you intend to do about the counting situation. Let's take a case where the customer calls three times in the 168-hour window. What numbers should apply to each record?

For example: Does the first call get 2 in the "subsequent calls" column and the second call get 1 in the "subsequent calls" column and the third call has 0? Or perhaps this is reversed and you get 0 for first call, 1 for second call, and 2 for the third call in the "recent calls" column? Or do all three calls get a 2 in the "associated calls" column. See, it makes a difference in how you want to count it.

If your answer is either the first or second of the two options, an SQL solution might be possible, which would allow this dog to run in a reasonable time.

The idea would be first call gets marked as 1 and second call gets marked as 1 and third call gets marked 0.

 

[The_Doc_Man]

You MIGHT get away with something similar to this (and I'm making up names as I go and YES I am shooting from the hip so don't hold my feet to the fire on this idea). First write an update query or take other measures to reset the Recurrence field to 0. Then something like this.

UPDATE CallList SET Recurrence = 1 
    WHERE EXISTS
         (SELECT * FROM CallList AS CL2
             WHERE ( CL2.Customer = CallList.Customer ) AND
                       ( CL2.StartTime [COLOR="Red"]>[/COLOR] CallList.StartTime ) AND
                       ( CL2.StartTime <= DateAdd( "h", 168, CallList.StartTime ) ) ;

In that code, the ">" sign is crucial and MUST not be ">="; nor can you use the BETWEEN ... AND construct. The reason is that by demanding the inequality you prevent a count for the record under consideration.

This uses a subquery to see if any records exist (the WHERE EXISTS clause) that are within 168 hours of the call in question. If so, the recurrence flag would be set. If you have the actual call times as part of that StartTime field, you might also simplify the final inner WHERE clause to CL2.StartTime <= ( CallList.StartTime + 7.0 ) since date fields are actually in units of days.

 

[StevePupel]

Put your data in a table with a column for Client ID and column for Call Date and Time, define the key as Client ID + Call Date and Time, then add an auto number field (AutoNumber), and also add a Long Integer called "Next Autonumber". Create an update query and update Next Autonumber to the auto number column (AutoNumber) + 1. Finally create a query which joins the table with itself joining on Client ID=Client ID and AutoNumber = Next Autonumber. Then you can create a new column which uses the datediff function to figure out how many hours difference the call date and time field in each joined record are.