Pulling Current Month Data

I have the same account numbers receiving statements every month but at different days.  When statements are received there is a StatementDate field that is entered in.  

I have a query that captures all account numbers (qryAccounts).  

I would like to create a query that identifies which accounts have not had statements come in for the current month.

Thanks for any help
Who is Participating?
jmcmunnConnect With a Mentor Commented:
If the statement date is in another table, the query would indeed change, but you can follow the same general idea.

Try reading this page, in particular you should check the section titled "Finding Records that Exist in One Table, but Not in a Query (subset of another table)" about half way down the page.  It has an example of something very similar to what you are trying to do which is explained pretty clearly.


It's just a matter of replacing the tables/fields and it should match what you want, assuming I understand correctly.
You can do this a few different ways, but a sub query something like below would be one appropriate way:

SELECT AccountNumber
FROM Accounts
  (SELECT AccountNumber FROM Accounts where DateDiff("m", StatementDate, Now) = 0)
NNiicckkAuthor Commented:
Sorry, I tried to create a sub query but had zero results.

Here are the related items, The StatementDate records as Short Date.

Table and field:



SELECT Accounts.AccountNumber
FROM Accounts;
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Jeffrey CoachmanMIS LiasonCommented:
Presuming you are only comparing dates in the current month and the previous month...

SELECT YourTable.ID, YourTable.AccountNumber, YourTable.StatementDate, IIf(Month([StatementDate]) & Year([StatementDate])<>Month(Date()) & Year(Date()),"No Statement this Month","Other Condition") AS NoStatementthisMonth
FROM YourTable
WHERE (((IIf(Month([StatementDate]) & Year([StatementDate])<>Month(Date()) & Year(Date()),"No Statement this Month","Other Condition"))="No Statement this Month"));

A bit heavy handed, but I tried to show you the logic, ...
Obviously you will have to adjust the object names to match yours...

NNiicckkAuthor Commented:

That did work but I do have a large amount of data which would keep showing up.  I created an Accounts query to show only the account numbers.  (They never change month to month) That is why the query works off of the Accounts table, as it will only pull the accounts once where the Statements table pulls the account number every time a statement date would be entered.

I guess I need a sub query where Accounts is joined with my Statement table.


Query:  (qryAccounts)
SELECT Accounts.AccountNumber
FROM Accounts;
Your query:
SELECT Statements.AccountID, Statements.StatementDate, IIf(Month([StatementDate]) & Year([StatementDate])<>Month(Date()) & Year(Date()),"No Statement this Month","Other Condition") AS NoStatementthisMonth
FROM Statements
WHERE (((IIf(Month([StatementDate]) & Year([StatementDate])<>Month(Date()) & Year(Date()),"No Statement this Month","Other Condition"))="No Statement this Month"))
ORDER BY Statements.AccountID;
Jeffrey CoachmanMIS LiasonCommented:
Then lets keep this simple.

1. Please post a simple sample database, that approximates your data.

2. Then post a clear graphical example of the *exact* output you want, based on that sample data...
NNiicckkAuthor Commented:
I have attached an example.   The accounts table and qryAccounts have the account numbers that are serviced monthly.  

The Statements table has the production tracking.  In this example, 4 of the 7 accounts have been received (StatementDate) for September.  I would like to show the 3 that have not been received by the bank as of yet.

I should see:  
Account 123456 StatementDate 8/1/2013
Account 234567 StatementDate 8/15/2013
Account 789123 StatementDate 7/12/2013
NNiicckkAuthor Commented:
Thanks for the help.
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.