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
NNiicckkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jmcmunnCommented:
You can do this a few different ways, but a sub query something like below would be one appropriate way:

SELECT AccountNumber
FROM Accounts
WHERE Not EXISTS
  (SELECT AccountNumber FROM Accounts where DateDiff("m", StatementDate, Now) = 0)
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:

Accounts.AccountNumber
Statements.StatementDate


Query:

SELECT Accounts.AccountNumber
FROM Accounts;
0
jmcmunnCommented:
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.

http://www.fmsinc.com/microsoftaccess/query/outer-join/

It's just a matter of replacing the tables/fields and it should match what you want, assuming I understand correctly.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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...

JeffCoachman
0
NNiicckkAuthor Commented:
Jeff,

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.

Table:
Statements.StatementDate

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;
0
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...
0
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
Nick092113.accdb
0
NNiicckkAuthor Commented:
Thanks for the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.