asked on

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

jmcmunn

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)

NNiicckk

ASKER

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;

ASKER CERTIFIED SOLUTION

jmcmunn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeffrey Coachman

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

NNiicckk

ASKER

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;

Jeffrey Coachman

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

NNiicckk

ASKER

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

NNiicckk

ASKER

Thanks for the help.