Hi All
I'm a new member and far from an expert on Access so thanks in advance for your patience.
I have a database with 2 tables. One contains customer records and one contains transactions. entries in each table can be linked via a unique number (which is a 'credit card number'), with a one to many relationship between customer/transactions.
I am trying to create a simple select query that will list customer name, card number and the most recent transaction. The transaction date field is in the format dd/mm/yyyy hh:mm:ss
I have created a similar query in a different database (same customer table) without issue by simply adding the fields to the query and setting the date to max - in fact that query returns max, min and a count but on this database it simply doesn't seem to work.
Can anybody suggest what I am doing wrong? I have pasted the sql from the query below in case that helps
SELECT Customers.[Card Number], Customers.[First Name], Customers.[Last Name], Min(Transactions.LOGDATETIME) AS MinOfLOGDATETIME, Max(Transactions.LOGDATETIME) AS MaxOfLOGDATETIME, Count(Transactions.LOGDATETIME) AS CountOfLOGDATETIME, Transactions.LEDGERBAL
FROM Customers INNER JOIN Transactions ON Customers.[Card Number] = Transactions.CARDNUM
GROUP BY Customers.[Card Number], Customers.[First Name], Customers.[Last Name], Transactions.LEDGERBAL;
I'm a new member and far from an expert on Access so thanks in advance for your patience.
I have a database with 2 tables. One contains customer records and one contains transactions. entries in each table can be linked via a unique number (which is a 'credit card number'), with a one to many relationship between customer/transactions.
I am trying to create a simple select query that will list customer name, card number and the most recent transaction. The transaction date field is in the format dd/mm/yyyy hh:mm:ss
I have created a similar query in a different database (same customer table) without issue by simply adding the fields to the query and setting the date to max - in fact that query returns max, min and a count but on this database it simply doesn't seem to work.
Can anybody suggest what I am doing wrong? I have pasted the sql from the query below in case that helps
SELECT Customers.[Card Number], Customers.[First Name], Customers.[Last Name], Min(Transactions.LOGDATETIME) AS MinOfLOGDATETIME, Max(Transactions.LOGDATETIME) AS MaxOfLOGDATETIME, Count(Transactions.LOGDATETIME) AS CountOfLOGDATETIME, Transactions.LEDGERBAL
FROM Customers INNER JOIN Transactions ON Customers.[Card Number] = Transactions.CARDNUM
GROUP BY Customers.[Card Number], Customers.[First Name], Customers.[Last Name], Transactions.LEDGERBAL;