Solved

Access 2010 query to find missing data/date fields

Posted on 2014-02-23
21
2,438 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:NNiicckk
ID: 39881523
0
 
LVL 50

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 50

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

726 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