Problem with Max of a date field

L1882

New member
Local time
Today, 13:23
Joined
Oct 12, 2011
Messages
8
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;
 
The first thing I would do is create a query on the transaction table, group by card number, max date.

The results of that query should be just the latest transaction on each card.

Then create the query you are currently trying to use, but using the new query instead of the transaction table.
 
Hi and thanks for the reply.

I have tried that previously, and just tried again, but is still returns multiple entries for each card, the sql looks like this:


SELECT Max(Transactions.LOGDATETIME) AS MaxOfLOGDATETIME, Transactions.CARDNUM, Transactions.LEDGERBAL
FROM Transactions
GROUP BY Transactions.CARDNUM, Transactions.LEDGERBAL;

I'm starting to suspect there must be something in the table itself that is not quite right - or that I don't understand the MAX command, which I thought picked the highest value. Based on what I've posted is it possible it is not recognising the dates correctly? I only ask because if I set the LEDGERBAL to last rather than group it seems to return the balance from one transaction with the date from another....
 
What happens if you remove LedgerBal from the query (regardless of whether you need it in the end result).

If LedgerBal is not the same across all records for that card number then grouping on that field as well as the card number will return more than one record per card.

If it is a static number for each card number then it won't cause a problem.
 
Yep, that sorts the issue. The only problem now is getting the latest ledgerbal is the main purpose of this query.

Is there anyway I can now add the ledger balance field back in?
 
As long as it's not set to group by it, yes.

Try adding it back in but change the group by to where (but leave the criteria blank).

The point is you want to return the field, but you don't want to do anything with it in the initial query.
 
Thank you - that is excellent and I now understand why I was having the problem. I'm using 2007 and it doesn't seem to like 'where' with 'show field' but I'll work on that now - hopefully I can sort it from here, if not I'll come back for help.
 
The normal approach is to join the query with the 2 fields back to the table on those two fields and select all that Fields that you need in a second query.

Brian
 
big thanks to both of you - you've solved in minutes what I've spent (on and off) 2 weeks trying to sort out
 

Users who are viewing this thread

Back
Top Bottom