Access 2010 query to find missing data/date fields

I currently hold records for every account in the database based on a statement date.

Fields:
AccountID
StatementDate

How would I locate when a statement date is missing?

An example is:  Account 123456 received a 11/01/13, 12/01/13, 02/01/14 statement.  Is there a way to identify this account has not received the 01/01/14 statement?
(Although this example was using the 1st, statements are sent at different times during the month based on the account number)
Nick022314.accdb
NNiicckkAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
try this, see the table MissingStatements populated by running  sub FindMissingStatements in module1
Database101-missingStatements.accdb
0
 
IrogSintaCommented:
The tables in the database you attached are linked tables so we cannot see their contents.  Can you provide one with local tables? However, please do not include any actual account numbers as that would be a breach of privacy information.

Ron
0
 
NNiicckkAuthor Commented:
How is this?
Nick022314.accdb
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
FlysterCommented:
Nope, the tables are still linked and we're unable to view them.

Flyster
0
 
NNiicckkAuthor Commented:
0
 
Gustav BrockCIOCommented:
Yes, a nice article explains exactly this here:

Find and Generate Missing Values in an Access Table

Look for the downloadable demo on page 2.

/gustav
0
 
NNiicckkAuthor Commented:
Great. Thank You.
0
 
IrogSintaCommented:
Rey, I just tried removing the record 02/01/14 in of Account 123456 and running your procedure.  It did not detect that both the January and February 1st accounts were missing so something is still not quite right there.

Ron
0
 
IrogSintaCommented:
In this version I attached, with the record 02/01/14 in of Account 123456  removed, It comes up with these missing statements for that particular account:
123456      1/1/2014
123456      2/1/2014

It also shows these others as missing:
125555      1/1/2014
125555      2/1/2014
345678      2/1/2014
457689      12/5/2013
457689      2/5/2014
567891      2/10/2014
678912      2/18/2014
789123      2/12/2014
Database101-rev1.accdb
0
 
Rey Obrero (Capricorn1)Commented:
@Ron
< It did not detect that both the January and February 1st accounts were missing so something is still not quite right there.>

how did you assume that there is something wrong?
are you sure that the account is still active ? or the balance have not been settled?
do you still need to send an statement after December ?


the question was to find the missing statements..
0
 
NNiicckkAuthor Commented:
Sorry, I tried to add a couple of statements and run it but I got the same result as I started with before the update.  AccountID is the field with the account numbers.  

Can you look at it again?

It is ok if you start it from today's date showing all Feb statements not received assuming the accounts were all active.

I do have another table [Accounts] that links to the [Statements].  The [Accounts] table has a "Status" field showing Active or Closed".
0
 
IrogSintaCommented:
I'm not following.  Can you provide more details? Which version are you using? What was the data and the expected results?
0
 
NNiicckkAuthor Commented:
Can you double check that the results will provide any statement dates not entered?  If you can tie it to the Active accounts only, that would help.
Database101-0227.accdb
0
 
IrogSintaCommented:
The results are fine when I run the process.  It empties the Missing Statements table and appends it with the missing statements as of today's date.  I modified the procedure to only include the Active accounts.

Ron
Database101-0227.accdb
0
 
NNiicckkAuthor Commented:
When I changed (added) a statement date or I changed an "active" to "closed", the result in the Missing Statements table did not change.  I closed the database and reopened it.  Can it be in query format instead of a module?
0
 
IrogSintaCommented:
Are you running the process again after you make changes in your tables?  You need to run it to see the new results.
0
 
IrogSintaCommented:
NNiicckk,
So did it work now?  Some feedback would really be appreciated.

Ron
0
 
NNiicckkAuthor Commented:
It looks fine initially but when I add statement 01/01/2014 account 125555, close the database and reopen..... the statement is in the Statements table but it is still in the Missing Statements table.  I cannot get it to update.

Is there no way to create a query instead of a module?
0
 
Gustav BrockCIOCommented:
You should have studied my article - link above. It works.

/gustav
0
 
IrogSintaCommented:
So let me get this straight.  You don't have a form where you can view as well as add your statement records?  You just want to enter them directly in the table and then see the other table automatically updated?  It doesn't work that way.  If you had a form as I mentioned, we can add an Event handler to run the procedure to update the MissingStatements table automatically.   Without a form, your only option is to run a macro that calls the procedure anytime you want to get your current list of missing statements.  Here's a revised db with a macro called ShowMissingStatements.  Just run it every time you update the other table:

Ron
Database101-0315.accdb
0
 
NNiicckkAuthor Commented:
Ron,

That works great, thanks.  I am not opposed to using a form, I just did not have one in the example I had sent you originally.
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.