Solved

Access 2010 query to find missing data/date fields

Posted on 2014-02-23
21
2,308 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

830 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