Access 2010 Query to locate most recent item only, when 1 is expected each month

I receive statements each month and I am trying to determine if a statement did not come in.  Example: account 12345 last statement was November 2013, so I am missing a December statement.  

I am trying to locate the newest statement received only but my query gives me all of the statement dates, not just the newest.

My Fields:
AccountNumber
StatementDate
ClientName
NNiicckkAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ButlerTechnologyConnect With a Mentor Commented:
I think you nee to make a minor change to sub query:
(Select max(StatementDate) FROM Statements where AccountID= Accounts.AccountNumber)

Open in new window

instead of accountnumber = accountnumber
0
 
ButlerTechnologyCommented:
Try this:
Select * 
from table a
where StatementDate = (Select max(Statmentdate) from table b where accountnumber = a.accountnumber)

Open in new window


The results should be the last statement for each customer.
0
 
Rey Obrero (Capricorn1)Commented:
try this


select a.*
from NameOfTable as a
inner join (select max(b.StatementDate) as MaxDate, b.AccountNumber
           from NameOftable as b
           group by b.AccountNumber ) as c
on a.StatementDate=c.maxDate and  a.AccountNumber =  c.AccountNumber
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
NNiicckkAuthor Commented:
Sorry with regard to field name sin the query,

The fields AccountNumber and Client Name come from the table Accounts.  The field StatementDate comes from the table Statements.
0
 
ButlerTechnologyCommented:
Try this one with the new table names:
Select *
from Accounts a inner join Statements S on a.accountNumber = s.AccountNumber
where s.statementDate = (Select max(StatementDate) from statements where accountNumber = a.Accountnumber

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
try this


select a.*
from Accounts as a
inner join (select max(b.StatementDate) as MaxDate, b.AccountNumber
           from Statements as b
           group by b.AccountNumber ) as c
on a.AccountNumber =  c.AccountNumber
0
 
NNiicckkAuthor Commented:
The max(StatementDate) gives me the max statement date in the system. Example: 12/20

I need the max statement date per each account #.

This is my SQL:

SELECT Accounts.AccountNumber AS Account, Statements.StatementDate AS [Last Statement], Accounts.ClientName AS Client, Accounts.Payee, Accounts.Status
FROM Accounts INNER JOIN Statements ON Accounts.[AccountNumber] = Statements.[AccountID]
WHERE Statements.[StatementDate] = (Select max(StatementDate) FROM Statements where AccountNumber = Accounts.AccountNumber)


EXAMPLE OF OUTPUT:  This would show me missing 2 December statements

Account      Last Statement      Client      Payee      Status
12345      11-Nov-13      aaaa      10101      Active
45678      30-Nov-13      bbbb      10102      Active
159623      15-Dec-13      cccc      10103      Closed
111422      30-Dec-13      dddd      10104      Active
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this


select a.AccountNumber as Account, c.MaxDate as [Last Statement], a.ClientName AS Client, a.Payee, a.Status
from Accounts as a
inner join (select max(b.StatementDate) as MaxDate, b.AccountID
           from Statements as b
           group by b.AccountID ) as c
on a.AccountNumber =  c.AccountID
0
 
NNiicckkAuthor Commented:
Thank You.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.