Getting only the last transaction for each customer

[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!

 

[PieterLinden via AccessMonster.com]

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.

 

[Nir N]

Thanks- it worked great.

Nir

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.