• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 28
  • Last Modified:

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
0
Ryan Simmons
Asked:
Ryan Simmons
  • 5
  • 5
1 Solution
 
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now