Getting only the last transaction for each customer

N

Nir N

I have a simple query (where the tables are of external database) that lists
all customers and their transaction dates.
How can I modify the query to get only the LAST transaction of each customer?
(Please note that the DATE field is a text field- this was decided by the
external company)

M2000 is the external table of the customer's details, "sessions" is the
external table with all the transaction details.

SELECT M2000.LASTNAME, M2000.FIRSTNAME, M2000.PIN, M2000.ADDRESS1,
M2000.ADDRESS2, M2000.CITY, M2000.ZIP, Left([copy of session]![date],8) AS
DATE1, Left([copy of SESSION]![DATE],4) AS [year], Mid([copy of
SESSION]![DATE],5,2) AS [Month], Mid([copy of SESSION]![DATE],7,2) AS [Day]
FROM M2000 INNER JOIN [Copy Of SESSION] ON M2000.RECNUM = [Copy Of
SESSION].LINKNUM
ORDER BY M2000.LASTNAME, Left([copy of session]![date],8);

Thanks!
 
P

PieterLinden via AccessMonster.com

Nir said:
I have a simple query (where the tables are of external database) that lists
all customers and their transaction dates.
How can I modify the query to get only the LAST transaction of each customer?
(Please note that the DATE field is a text field- this was decided by the
external company)

M2000 is the external table of the customer's details, "sessions" is the
external table with all the transaction details.

SELECT M2000.LASTNAME, M2000.FIRSTNAME, M2000.PIN, M2000.ADDRESS1,
M2000.ADDRESS2, M2000.CITY, M2000.ZIP, Left([copy of session]![date],8) AS
DATE1, Left([copy of SESSION]![DATE],4) AS [year], Mid([copy of
SESSION]![DATE],5,2) AS [Month], Mid([copy of SESSION]![DATE],7,2) AS [Day]
FROM M2000 INNER JOIN [Copy Of SESSION] ON M2000.RECNUM = [Copy Of
SESSION].LINKNUM
ORDER BY M2000.LASTNAME, Left([copy of session]![date],8);

Thanks!

Summarize your sessions first... build a totals query that returns the last
SessionDate for each Customer...

SELECT Session.SessionCustomer, Max(Session.SessionDate) AS MaxOfSessionDate
FROM [Session]
GROUP BY Session.SessionCustomer;

then join that back to the customer table.
 
N

Nir N

Thanks- it worked great.

Nir

PieterLinden via AccessMonster.com said:
Nir said:
I have a simple query (where the tables are of external database) that lists
all customers and their transaction dates.
How can I modify the query to get only the LAST transaction of each customer?
(Please note that the DATE field is a text field- this was decided by the
external company)

M2000 is the external table of the customer's details, "sessions" is the
external table with all the transaction details.

SELECT M2000.LASTNAME, M2000.FIRSTNAME, M2000.PIN, M2000.ADDRESS1,
M2000.ADDRESS2, M2000.CITY, M2000.ZIP, Left([copy of session]![date],8) AS
DATE1, Left([copy of SESSION]![DATE],4) AS [year], Mid([copy of
SESSION]![DATE],5,2) AS [Month], Mid([copy of SESSION]![DATE],7,2) AS [Day]
FROM M2000 INNER JOIN [Copy Of SESSION] ON M2000.RECNUM = [Copy Of
SESSION].LINKNUM
ORDER BY M2000.LASTNAME, Left([copy of session]![date],8);

Thanks!

Summarize your sessions first... build a totals query that returns the last
SessionDate for each Customer...

SELECT Session.SessionCustomer, Max(Session.SessionDate) AS MaxOfSessionDate
FROM [Session]
GROUP BY Session.SessionCustomer;

then join that back to the customer table.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top