CODEWITHSUNDEEP
sqldatems-accessbetween
Justin Roberts
Justin Roberts

Reputation: 21

Access SQL Count all records where date +-60 days

I have a table [Usage] with uniqueness on [Usage.Address & Usage.TransactionDate]. I am attempting to create a query that lists Usage.Address, Usage.TransactionDate, and a field that counts the records with the same Address where TransactionDate is plus or minus 60 days of the TransactionDate. How do I do this?

Upvotes: 0

Views: 83

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can use a correlated subquery:

select u.*,
       (select count(*)
        from usage as u2
        where u2.address = u.address and
              u2.transactionDate >= dateadd("d", -60, u.transactionDate) and
              u2.transactionDate <= dateadd("d", 60, u.transactionDate) 
       ) as new_field
from usage as u;

Upvotes: 1

Related Questions