Getting intervals between time from all succeeding rows? (Time is in text data type) NAME    DATE         TIME      INTERVAL
a      10/19/2015    5:00 PM    
a      10/20/2015    5:00 PM    86400
a      10/20/2015    5:30 PM    1800


This is easy to do in Excel. But considering we have a data of 2 million+ rows, we can only do it to the only  DB resource we have which is Access.

How do we do it on Access? We need the interval of seconds between all the time each row per NAME.. So for all "A", we need the second interval between its first time, and second time, the second time, to third time, and so on. 

So for "B", its all the same too. 

What makes it extra hard is i stored all the data types as TEXT. So I cant just use a datediff or timediff function. :(
You can create a date-time Access type from text using the format() function, so you CAN use datediff.  You should consider creating an query that adds a new column that is date formatted.   The SQL I include here uses that repeatedly which makes it hard to read.   

Assume your table is called Table1 with just those three columns, and grouped on Name with no duplicate times (e.g. interval between current row and next row is never zero).  

NAME DATE       TIME
a   10/19/2015  5:00 PM
a   10/20/2015  5:00 PM
a   10/20/2015  5:30 PM
b   10/20/2015  4:00 PM
b   10/21/2015  3:00 PM
b   10/21/2015  5:00 PM


The following query unfortunately does not show that first line (for each group) that has no interval, only subsequent rows with the current time, and the interval between the current time and the previous time.    The results look like this: 

NAME    DATE        TIME    INTERVAL
a     10/19/2015    5:00 PM 86400
a     10/20/2015    5:00 PM 1800
b     10/20/2015    4:00 PM 82800
b     10/21/2015    3:00 PM 7200


Create a new query, do not add any tables, switch to SQL view,  and Copy and paste the SQL below.  change "Table1" in the code below to your actual table name which you don't provide.  

 SELECT table1.NAME,future.DATE,future.TIME, 
        Min(DateDiff('s',
              Format([Table1].[TIME] &amp; " " &amp; [Table1][DATE]),
              Format([future].[TIME] &amp; " " &amp; [future].[DATE]))) 
        AS [INTERVAL]
 FROM Table1 INNER JOIN Table1 AS future ON Table1.NAME = future.NAME
 WHERE (((
   Format([future].[TIME] &amp; " " &amp; [future].[DATE]))
     &gt;Format([Table1].[TIME] &amp; " " &amp; [Table1].[DATE]
   )))
 GROUP BY table1.NAME, future.DATE,future.TIME;


The key is to use a self join (add your data table twice to your query).  When you do that Access creates a table alias with _1 added to the end.  Instead I select the query properties and add an alias for the table in design view.    Join on the NAME field only, and limit on the date-time to select the minimum date-time that is just greater than the current row. 

Let me know if that works for you