In MS Access how could I determine the days between the phone numbers in column A of the attached worksheet?

In the attached document I have phone numbers in column A and dates in column C.

There are two tabs in the example. The second tab is the result I am seeking.

How can I get MS Access to check the phone number against the dates in column B of the example result and provide the number of days since the phone number last called?
Example_Days_Between_Sessions.xlsx
Ryan SimmonsBusiness Analyst IIIAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
Are you trying to calculate the days from TODAY to the date next to each phone number?

And you want to do this in Access, right?  So you have a table with those columns (phone, date) and you want a query to calculate the days before TODAY of that date?


»bp
0
Gustav BrockCIOCommented:
Just link the sheet as a table and run:

Select 
    originatordn, 
    Max([Date]) As LatestCall, 
    DateDiff("d", Max([Date]), Date()) As DaysSinceLastCall
From 
    YourLinkedTable
Group By
    originatordn

Open in new window

1
Ryan SimmonsBusiness Analyst IIIAuthor Commented:
That query gets me part of the way there. I need every call that a caller made and the days since last call for every call they made. This is what makes it difficult. This is shown in the example.

For instance:
Caller      Date      DaysSinceLastCall
8708556091      1/1/2017      0
8708556091      1/10/2017      9
8708556091      3/31/2017      80
8708556091      4/25/2017      25
8708556091      4/26/2017      1
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Gustav BrockCIOCommented:
That would be:

Select 
    originatordn, 
    [Date], 
    DateDiff("d", 
            (Select Top 1 [Date] 
            From YourLinkedTable As T 
            Where T.originatordn = YourLinkedTable.originatordn And T.Date < YourLinkedTable.Date
            Order By YourLinkedTable.Date Desc), 
        [Date] As DaysSincePreviousCall
From 
    YourLinkedTable
Order By
    originatordn,
    [Date]

Open in new window

1
Ryan SimmonsBusiness Analyst IIIAuthor Commented:
It doesn't work. I get a error that states, "Syntax error (missing operator) in query expression 'DateDiff("d", (Select Top 1 [Date] From Caller1 As T  Where T.originatordn = Caller1.originatordn And T.Date < Caller1.Date Order By Caller1.Date Desc), [Date] As DaysSincePreviousCall From Caller1 Order By originatordn, [Date]'.

When I isolate the subquery Access states it cannot see the Caller1 table. I suspect this is because the query renames the Caller1 table T and there is no join to the Caller1 table.

I am studying it. It is a very interesting approach.
0
Gustav BrockCIOCommented:
It's here:

[Date]) As DaysSincePreviousCall

Open in new window

0
Ryan SimmonsBusiness Analyst IIIAuthor Commented:
There is a new error. It says, "invalid argument to function."
0
Gustav BrockCIOCommented:
It could be Nulls, so try this:

Select 
    originatordn, 
    [Date], 
    DateDiff("d", 
            Nz((Select Top 1 [Date] 
            From YourLinkedTable As T 
            Where T.originatordn = YourLinkedTable.originatordn And T.Date < YourLinkedTable.Date
            Order By YourLinkedTable.Date Desc), 
        YourLinkedTable.Date), YourLinkedTable.Date)  As DaysSincePreviousCall
From 
    YourLinkedTable
Order By
    originatordn,
    [Date]

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan SimmonsBusiness Analyst IIIAuthor Commented:
Same error... Attached is a complete dataset of the data I am working with. Thanks for your help.
ExampleDataset.xlsx
0
Gustav BrockCIOCommented:
Well, if you remove the empty lines in the sheet, it works - but it is very very slow. My patience is too limited to what for it to finish.

You may have to simply loop the sorted table in VBA and then, record by record, find the count of days between this and the previous record.
Then append the record to a new table.
0
Ryan SimmonsBusiness Analyst IIIAuthor Commented:
Thank you! I will give VBA a shot as well.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.