# Date Average

• ### Question

• Good Day,

I am trying to write a query to help me find the Frequency between dates.
I am having a hard time figuring out how to minus the first date from the 2nd & the 2nd from the 3rd & so on...  And somehow stop @ the last record so that 39907 won't mess up the Frequency/Average.
Need to do this in each change of ACCOUNT_NUM.

I have well over a million records to go through.

Thanks!
Nigel

See below for example:

 ACCOUNT_NUM INVOICE_DATE DATE_FREQUENCY 017004524 07-Mar-12 37 017004524 30-Jan-12 12 017004524 18-Jan-12 20 017004524 29-Dec-11 15 017004524 14-Dec-11 26 017004524 18-Nov-11 18 017004524 31-Oct-11 205 017004524 09-Apr-11 31 017004524 09-Mar-11 23 017004524 14-Feb-11 18 017004524 27-Jan-11 8 017004524 19-Jan-11 14 017004524 05-Jan-11 21 017004524 15-Dec-10 21 017004524 24-Nov-10 21 017004524 03-Nov-10 208 017004524 09-Apr-10 35 017004524 05-Mar-10 18 017004524 15-Feb-10 18 017004524 28-Jan-10 9 017004524 19-Jan-10 15 017004524 04-Jan-10 20 017004524 15-Dec-09 22 017004524 23-Nov-09 24 017004524 30-Oct-09 183 017004524 30-Apr-09 39907 Count Date Records = 26 SUM(FREQUENCY)/"Count Date Records" = 40

xNH

• Edited by Thursday, May 24, 2012 1:46 AM
Thursday, May 24, 2012 1:32 AM

• SELECT a.account_num, a.invoice_date, a.invoice_date-MAX(b.invoice_date) AS dateDiff
FROM tableNameHere AS a LEFT JOIN tableNameHere AS b
ON a.invoice_date > b.invoice_date
GROUP BY a.account_num, a.invoice_date

should supply the difference of successive dates.  The last record, not finding any previous date, will return a NULL as dateDiff. You can add an HAVING clause to remove exessive date span:

HAVING  a.invoice_date-MAX(b.invoice_date)  < 1000

The query may take a very long time to run (mostly if you have millions of record).

• Marked as answer by Tuesday, June 5, 2012 4:06 PM
Thursday, May 24, 2012 4:58 PM

### All replies

• There may be a convoluted SQL to do this with a subselect to get the max date less than the current record, but I would do this using an ordinary recordset. Sort the data, then loop forward, keeping track of the previous date and the previous AccountNum in local variables, and doing the date math using DateDiff.

-Tom. Microsoft Access MVP

Thursday, May 24, 2012 1:57 AM
• I am not following your terminology.   Your Date_Frequency does not look like 'frequency' but just the number of days from one date to the other.

What is the 'Frequency/Average'?

Thursday, May 24, 2012 1:43 PM
• Can you post your SQL Statement? I think in MS Access you may be able to just add an IIF condition on Date_Frequency.

HTH

Thursday, May 24, 2012 4:34 PM
• SELECT a.account_num, a.invoice_date, a.invoice_date-MAX(b.invoice_date) AS dateDiff
FROM tableNameHere AS a LEFT JOIN tableNameHere AS b
ON a.invoice_date > b.invoice_date
GROUP BY a.account_num, a.invoice_date

should supply the difference of successive dates.  The last record, not finding any previous date, will return a NULL as dateDiff. You can add an HAVING clause to remove exessive date span:

HAVING  a.invoice_date-MAX(b.invoice_date)  < 1000

The query may take a very long time to run (mostly if you have millions of record).

• Marked as answer by Tuesday, June 5, 2012 4:06 PM
Thursday, May 24, 2012 4:58 PM
Vanderghast,  in the next day or 2 i will try what you suggested.

Imran, my sql statement is very straight forward right now.  This was done in Excel.
But here it is:

```SELECT ACCOUNT.ACCOUNT_NUM, INVOICE.INVOICE_NUM, INVOICE.INVOICE_DATE
FROM INVOICE INNER JOIN ACCOUNT ON INVOICE.ACCOUNT_NUM = ACCOUNT.ACCOUNT_NUM
WHERE (((ACCOUNT.ACCOUNT_NUM)="017004524"))
ORDER BY ACCOUNT.ACCOUNT_NUM, INVOICE.INVOICE_NUM DESC;```

Thanks! Nigel

• Edited by Thursday, May 24, 2012 6:32 PM
Thursday, May 24, 2012 6:31 PM