Ryan Simmons
asked on
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
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
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
originatordnASKER
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
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
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]ASKER
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.
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.
It's here:
[Date]) As DaysSincePreviousCallASKER
There is a new error. It says, "invalid argument to function."
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Same error... Attached is a complete dataset of the data I am working with. Thanks for your help.
ExampleDataset.xlsx
ExampleDataset.xlsx
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.
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.
ASKER
Thank you! I will give VBA a shot as well.
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