Terry,
My sample db named Query_GrpSequentialsAndMissing might be of interest to you. It is in access 2000 file format and is available at Rogers Access Library. Link:
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45
It demonstrates identification of data blocks as well as missing portions in a sequential series. Three types of source data are covered:
(a) Number series.
(b) Alpha-numeric series.
(c) Date series.
Subform on left shows all records, duly highlighting the start of each new block of sequential series. Subform at right depicts group-wise gist (for each PersonID) of sequential blocks as well as missing portions, duly indicating the start and end values for each set. Missing blocks are highlighted in light grey.
For identifying the start and end values for available as well as missing blocks in the given sequential series, an interesting technique has been used, eliminating the need for any driver or temporary table. Instead, sliding comparison with projected next and previous values has been adopted through self joins based upon source data.
Note:
If at any stage, you need to fill-in missing sequential values, companion sample db named Query_FillMissingValues demonstrates the process. This too is available at above mentioned site.
Best wishes,
A.D. Tejpal
------------
----- Original Message -----
From: Terry Olsen
To: MS_Access_Professionals@yahoogroups.com
Sent: Friday, September 23, 2011 23:36
Subject: [MS_AccessPros] Query for Missing Data
How can I construct a query(ies) that will tell me if each Block in BlockRun below doesn't contain Run data for runs 88, 89, and 90?
Block (Left [BlockRun], 3) contains Run (Right [BlockRun],2]
BlockRun data example
99801
99888
99889
Missing 99890
99901
Missing 99988
99989
99990
Thanks,
Terryomsn
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar