By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,066 Members | 1,826 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,066 IT Pros & Developers. It's quick & easy.

Filtering to only show last balance

P: 1
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
Oct 23 '06 #1
Share this Question
Share on Google+
2 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 23 '06 #2

NeoPa
Expert Mod 15k+
P: 31,429
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 :-
Expand|Select|Wrap|Line Numbers
  1. SELECT subQ.* FROM (SELECT * FROM DataSet) AS subQ
Which is equivalent to
Expand|Select|Wrap|Line Numbers
  1. 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 :-
Expand|Select|Wrap|Line Numbers
  1. SELECT [Customer ID], Sale, Max([Date]) AS LastDate
  2. FROM Accounts
  3. 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.
Oct 24 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.