Solved

Access 2010 query to find missing data/date fields

Posted on 2014-02-23
21
2,210 Views
Last Modified: 2014-03-16
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
0
Comment
Question by:NNiicckk
  • 8
  • 8
  • 2
  • +2
21 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39881363
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
 

Author Comment

by:NNiicckk
ID: 39881496
How is this?
Nick022314.accdb
0
 
LVL 22

Expert Comment

by:Flyster
ID: 39881514
Nope, the tables are still linked and we're unable to view them.

Flyster
0
 

Author Comment

by:NNiicckk
ID: 39881523
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39881880
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39882549
try this, see the table MissingStatements populated by running  sub FindMissingStatements in module1
Database101-missingStatements.accdb
0
 

Author Closing Comment

by:NNiicckk
ID: 39882784
Great. Thank You.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39887932
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39888049
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39888593
@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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:NNiicckk
ID: 39892437
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39897553
I'm not following.  Can you provide more details? Which version are you using? What was the data and the expected results?
0
 

Author Comment

by:NNiicckk
ID: 39904010
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39904261
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
 

Author Comment

by:NNiicckk
ID: 39905326
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39905344
Are you running the process again after you make changes in your tables?  You need to run it to see the new results.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39931563
NNiicckk,
So did it work now?  Some feedback would really be appreciated.

Ron
0
 

Author Comment

by:NNiicckk
ID: 39931715
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39931717
You should have studied my article - link above. It works.

/gustav
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39931853
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
 

Author Comment

by:NNiicckk
ID: 39932395
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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now