Filtering to only show last balance

[Rlcohen70]

Hello,
I have two tables that have a relationship based on a customer ID. The first table Customer has:

Customer ID
Name
Address
etc

Accounts Table has:
Customer ID
Sale
Payment
Balance
Date
Notes
etc

The Accounts table has all the transactions for all sales. So for customer 116, I could have 5 entries for sale, payment and balance.


I need to build a query for a report that looks at the table and does the following
Sort by customer name
Show the last balance for the customer based on the last or most recent date to the current date.

I know how to link tables, but I would need guidance on where to put this code if I can do this.

Thanks

[MMcCarthy]

Open a new query in design view. Don't add any tables then switch the view to sql. Copy and paste in the code below. Check the table names as I assumed Customer and Accounts. Also checkthe field names are correct. Then save and run the query.


SELECT C.Name, A.Sale, A.Payment, A.Balance, Max(A.[Date]) As LastDate FROM Customer C INNER JOIN Accounts A ON C.CustomerID=A.CustomerID GROUP BY C.Name, A.Sale, A.Payment, A.Balance;

Once this query is done you will be able to view it in access.

[NeoPa]

If you want the items associated with the latest date (Payment; Balance; etc) you will need a subquery (at least that's the only way I can think of that you can).
Subqueries always look complicated if done within SQL but they needn't be if the basic structure is understood.
The simplest form of subquery in SQL is :-

SELECT subQ.* FROM (SELECT * FROM DataSet) AS subQ

Which is equivalent to

SELECT * FROM DataSet

Anyway, another easier way to do it is to create another query, saved in the database like any other. I always cheat anyway and design it in the graphic window then copy / paste it into the SQL where it's required.

For this problem I think you need a subquery going something like :-

SELECT [Customer ID], Sale, Max([Date]) AS LastDate
FROM Accounts
GROUP BY [Customer ID], Sale

The main query should have an INNER JOIN link from Accounts to this subquery to select only those records which match the last date.