CODEWITHSUNDEEP
sqlms-access
Christopher Warrington
Christopher Warrington

Reputation: 767

Calculating the difference in days from two records in an Access Database

I am creating an Access Database from a very complex Excel Spreadsheet. The process has been going well until I got to this problem. The solution is easy in Excel, but I cannot figure out how to do it in Access.

Here is what I had before in Excel.

I had a list of Customers on one sheet with multiple fields. I then had another sheet act as a report that would run a VBA macro to search through the table of all customers and list out every customer by name that was an inbound call from our contact center (Que Call), when that call came and then would calculate a third column for the number of days between calls. This last column is where I am running into difficulties translating to Access. In Excel, I would just have it do something like in cell C3 =SUM(B3-B2). Given that the table looked like this: 

          Column A         Column B      Column C

Row 1     Name             Date          Time Lapse

Row 2     Customer 1       7/1/2019      ----------

Row 3     Customer 2       7/2/2019      =SUM(B3-B2)     <-- 1 day

Row 4     Customer 3       7/4/2019      =SUM(B4-B3)     <-- 2 days

In Access:

I have a report that goes through my table of customers and lists off only those from our contact center (Que Call), but I can't figure out how to put in the calculation of time between calls as the design only allows me to affect one row. How do I make this calculation? Is it a SQL query that I need to do? I would prefer to not have to have a separate table for call center calls or a separate column in my customers table to calculate this as some customers are not from the call center. Can I just run a report or a query. Any advise or help would be greatly appreciated.

Current SQL Code:

SELECT 
     [Customers].FullName, 
     [Customers].ID, 
     [Customers].QueCall, 
     [Customers].Status, 
     [Customers].InterestLevel, 
     [Customers].State, 
     [Customers].Product, 
     [Customers].Created, 
     [Customers].LastContact, 
     [Customers].PrimaryNote 
FROM 
     Customers 
WHERE 
     ((([Customers].QueCall)=True)); 
ORDER BY 
     [Customers].Created;

Describe exactly how it isn't working (error message, unexpected results, etc...)

It just lists out the customers and does not allow me to calculate the difference between when the records were created (ie when they were first contacted). I have found many things online about how to calculate the difference between two columns of the same record, but not between two different records; nor two different records that may not be sequentially after each other as there may be other non Que Call customers between records in the customer table.

Describe the desired results

I would like to have a column in the end report that shows how many days lapsed between records that were que calls.

Thank you in advance for any input that you may have.

Upvotes: 1

Views: 725

Answers (1)

Parfait
Parfait

Reputation: 107567

Consider a correlated aggregate subquery where an inner query from same source, Customer, is correlate with outer query by same ID (assumed to be unique identifier) with date comparison (assumed to be Created field). Notice the use of table alias, c and sub for the correlation.

Use DateDiff for difference between dates. To use this query, place below query into the SQL mode of Query Designer and save the object to be used as recordsources to forms, reports, opened on its own, or used in application code as recordsets.

SELECT 
     c.FullName, 
     c.ID, 
     c.QueCall, 
     c.Status, 
     c.InterestLevel, 
     c.State, 
     c.Product, 
     c.Created, 
     c.LastContact, 
     c.PrimaryNote,

     (SELECT TOP 1 SUM(DateDiff("d", sub.Created, c.Created))
      FROM Customer sub
      WHERE sub.ID = c.ID
        AND sub.Created < c.Created
      GROUP BY sub.Created
      ORDER BY sub.Created DESC) AS TimeElapsed

FROM 
     Customers c
WHERE 
     (((c.QueCall)=True)); 
ORDER BY 
     c.Created;

Do be aware for large tables this correlated subquery can be taxing in time and performance. Allow time to complete and look into storing output in a temp table with a Make-Table Query to avoid re-run.

Upvotes: 1

Related Questions