Query the most recent transaction for each customer

[Guest]

I have 2 tables: customers and transactions--multiple transactons per customer

I want a query that yields the names of customers whose most recent transaction date is more than 5 years ago.

I'd appreciate ideas on how to do that.

 

[Ken Snell]

Something like this (not fully tested!):

SELECT CustomerName FROM CustomerTableName
WHERE DateDiff("y", Max(TransactionDate), Date()) +
(Month(Max(TransactionDate))*100 + Day(Max(TransactionDate)) <
Month(Date()))*100 + Day(Date())) > 5
GROUP BY CustomerName;

The "roundabout" example above is because the DateDiff function (which would
be an easier approach) calculates the difference in years simply by
comparing the year portions of the dates. Thus, DateDiff says there is a
one-year difference between December 31, 2000 and January 1, 2001. An
alternative would be to use DateDiff to calculate the number of days between
the dates and then assume 365 (or 365.25) days for each year, but it's a
little less accurate than the above expression.


--
Ken Snell
<MS ACCESS MVP>

I have 2 tables: customers and transactions--multiple transactons per customer.

I want a query that yields the names of customers whose most recent

transaction date is more than 5 years ago.

 

[Brian Camire]

You might try a query whose SQL looks something like this:

SELECT
[Customers].[Customer ID],
[Customers].[Customer Name]
FROM
[Customers]
INNER JOIN
[Transactions]
ON
[Customers].[Customer ID]
GROUP BY
[Customers].[Customer ID],
[Customers].[Customer Name]
HAVING
Max([Transactions].[Transaction Date]) < DateAdd("yyyy",-5,Now())

I have 2 tables: customers and transactions--multiple transactons per customer.

I want a query that yields the names of customers whose most recent

transaction date is more than 5 years ago.

 

[Guest]

Ken, thanks

Let me try to be more clear--let's forget the 5-year thing for now

There are 0 to n rows in the transaction table for each row in the customer table, with customer id as tyhe foreign key

I want a query that joins customer rows with the one matching transaction rows with the latest date (if any

I can't make your SQL do that. Am I doing somethinf wrong, or do I need something else


----- Ken Snell wrote: ----

Something like this (not fully tested!)

SELECT CustomerName FROM CustomerTableNam
WHERE DateDiff("y", Max(TransactionDate), Date())
(Month(Max(TransactionDate))*100 + Day(Max(TransactionDate))
Month(Date()))*100 + Day(Date())) >
GROUP BY CustomerName

The "roundabout" example above is because the DateDiff function (which woul
be an easier approach) calculates the difference in years simply b
comparing the year portions of the dates. Thus, DateDiff says there is
one-year difference between December 31, 2000 and January 1, 2001. A
alternative would be to use DateDiff to calculate the number of days betwee
the dates and then assume 365 (or 365.25) days for each year, but it's
little less accurate than the above expression

 

[Brian Camire]

That should be:

SELECT
[Customers].[Customer ID],
[Customers].[Customer Name]
FROM
[Customers]
INNER JOIN
[Transactions]
ON
[Customers].[Customer ID] = [Transactions].[Customer ID]
GROUP BY
[Customers].[Customer ID],
[Customers].[Customer Name]
HAVING
Max([Transactions].[Transaction Date]) < DateAdd("yyyy",-5,Now())

You might try a query whose SQL looks something like this:

SELECT
[Customers].[Customer ID],
[Customers].[Customer Name]
FROM
[Customers]
INNER JOIN
[Transactions]
ON
[Customers].[Customer ID]
GROUP BY
[Customers].[Customer ID],
[Customers].[Customer Name]
HAVING
Max([Transactions].[Transaction Date]) < DateAdd("yyyy",-5,Now())

I have 2 tables: customers and transactions--multiple transactons per customer.

I want a query that yields the names of customers whose most recent

transaction date is more than 5 years ago.

I'd appreciate ideas on how to do that.

 

[Ken Snell]

You're right. I misread your post. I believe that this will be closer to
your goal:

SELECT CustomerName FROM CustomerTableName
INNER JOIN TransacationsTableName ON
CustomerTableName.CustomerID = TransactionTableName.CustomerID
WHERE DateDiff("y", Max(TransactionTableName.TransactionDate), Date()) +
(Month(Max(TransactionTableName.TransactionDate))*100 +
Day(Max(TransactionTableName.TransactionDate)) <
Month(Date()))*100 + Day(Date())) > 5
GROUP BY CustomerName;

--
Ken Snell
<MS ACCESS MVP>

Ken, thanks.

Let me try to be more clear--let's forget the 5-year thing for now.

There are 0 to n rows in the transaction table for each row in the

customer table, with customer id as tyhe foreign key.

I want a query that joins customer rows with the one matching transaction

rows with the latest date (if any)

 

[John Spencer (MVP)]

I would use a Subquery in the where clause. The following should return all
customers that have had a transaction that was over five years ago from the
current date AND have not had any transactions after that calculated date.
Obviously you will have to replace CustomerID and TransActionDate with the names
of your fields and if your table names are different then you will also need to
fix that.


SELECT Customers.*
FROM Customers
WHERE CustomerId IN
(SELECT T.CustomerID
FROM TransActions as T
GROUP BY T.CustomerID
HAVING Max(T.TransActionDate) <
DateSerial(Year(Date())-5,Month(Date()),Day(Date())))

 

[Guest]

Brian, thanks. That got it.

 

[Guest]

If tried a couple of the suggestions, and I get the same results--with the same curiosity: I'm missing one transaction

There are 12 transaction records with dates > 5 years ago today
1 08-Aug-9
2 23-May-9
3 19-Jan-9
4 11-Oct-9
5 07-Apr-9
6 08-Sep-9
7 19-Jan-9
8 01-Jun-9
9 01-Apr-9
10 01-Sep-9
11 01-Jun-9
12 16-Dec-7

The query I code following the directions returns 9 transactions

Numbers 4 and 6 in the list above are omitted correctly: they are not the latest for that customer

However, number 11 is the one and only transaction for the customer, and is clearly more than 5 years ago

Any ideas on how I could miss a record

----- John Spencer (MVP) wrote: ----

I would use a Subquery in the where clause. The following should return al
customers that have had a transaction that was over five years ago from th
current date AND have not had any transactions after that calculated date.
Obviously you will have to replace CustomerID and TransActionDate with the name
of your fields and if your table names are different then you will also need t
fix that


SELECT Customers.
FROM Customer
WHERE CustomerId I
(SELECT T.CustomerI
FROM TransActions as
GROUP BY T.CustomerI
HAVING Max(T.TransActionDate)
DateSerial(Year(Date())-5,Month(Date()),Day(Date()))

Fred wrote

 

[Guest]

Never mind. My problem

It works. Thanks Ken, Brian, and John

----- Fred wrote: ----

If tried a couple of the suggestions, and I get the same results--with the same curiosity: I'm missing one transaction

There are 12 transaction records with dates > 5 years ago today
1 08-Aug-9
2 23-May-9
3 19-Jan-9
4 11-Oct-9
5 07-Apr-9
6 08-Sep-9
7 19-Jan-9
8 01-Jun-9
9 01-Apr-9
10 01-Sep-9
11 01-Jun-9
12 16-Dec-7

The query I code following the directions returns 9 transactions

Numbers 4 and 6 in the list above are omitted correctly: they are not the latest for that customer

However, number 11 is the one and only transaction for the customer, and is clearly more than 5 years ago

Any ideas on how I could miss a record

----- John Spencer (MVP) wrote: ----

I would use a Subquery in the where clause. The following should return al
customers that have had a transaction that was over five years ago from th
current date AND have not had any transactions after that calculated date.
Obviously you will have to replace CustomerID and TransActionDate with the name
of your fields and if your table names are different then you will also need t
fix that


SELECT Customers.
FROM Customer
WHERE CustomerId I
(SELECT T.CustomerI
FROM TransActions as
GROUP BY T.CustomerI
HAVING Max(T.TransActionDate)
DateSerial(Year(Date())-5,Month(Date()),Day(Date()))

Fred wrote