Revision as of 12:03, 26 July 2010
Dallr (Talk | contribs)
← Previous diff |
Current revision
Jleach (Talk | contribs)
|
| Line 1: |
Line 1: |
| - | This article was originally created by Dane Miller, aka dallr [http://www.utteraccess.com/forum/dallr-m67878.html] and the sample application with the code can be found here: | + | {{Article}} |
| | + | {{GeneralCleanup}} |
| | | | |
| - | [[Media:Find Missing Dates or Numbers within a Range.zip]] | + | I have also attached a sample application with the code and it can be found here: [[Media:Find Missing Dates or Numbers within a Range.zip]] |
| | | | |
| | It shows how to use SQL to identifying missing dates or numbers within a range. | | It shows how to use SQL to identifying missing dates or numbers within a range. |
| Line 14: |
Line 15: |
| | ''So if your data is like this.'' | | ''So if your data is like this.'' |
| | | | |
| - | 100 | + | 100<br /> |
| - | | + | 101<br /> |
| - | 101 | + | 106<br /> |
| - | | + | 107<br /> |
| - | 106 | + | 109<br /> |
| - | | + | |
| - | 107 | + | |
| - | | + | |
| - | 109 | + | |
| - | | + | |
| | 115 | | 115 |
| - | | |
| | | | |
| | ''The SQL will return this Missing Data:'' | | ''The SQL will return this Missing Data:'' |
| | | | |
| - | 102-105 | + | 102-105<br /> |
| - | | + | 108<br /> |
| - | 108 | + | |
| - | | + | |
| | 110-114 | | 110-114 |
| - | | |
| | | | |
| | Number Example Code: | | Number Example Code: |
| | | | |
| | To use the code all you need to adjust is the inner query in the FROM Clause with the subquery. | | To use the code all you need to adjust is the inner query in the FROM Clause with the subquery. |
| | + | |
| | Note: If you have extremely large amounts of records then it might be best to use the inner query and make a table then use that table as the source for the outer query. | | Note: If you have extremely large amounts of records then it might be best to use the inner query and make a table then use that table as the source for the outer query. |
| | | | |
| - | [code] | + | [sql] |
| | | | |
| | SELECT T3.YourNumber | | SELECT T3.YourNumber |
| Line 54: |
Line 47: |
| | WHERE ((([NextNum]-[YourNumber])>1)); | | WHERE ((([NextNum]-[YourNumber])>1)); |
| | | | |
| - | [/code] | + | [/sql] |
| | | | |
| | | | |
| Line 63: |
Line 56: |
| | ''So if your data is like this.'' | | ''So if your data is like this.'' |
| | | | |
| - | 8/1/09 | + | 8/1/09<br /> |
| - | | + | 8/2/09<br /> |
| - | 8/2/09 | + | 8/5/09<br /> |
| - | | + | 8/10/09 <br /> |
| - | 8/5/09 | + | |
| - | | + | |
| - | 8/10/09 | + | |
| - | | + | |
| | 8/14/09 | | 8/14/09 |
| | | | |
| Line 76: |
Line 65: |
| | ''The SQL will return this Missing Data:'' | | ''The SQL will return this Missing Data:'' |
| | | | |
| - | 8/3/09 - 8/4/09 | + | 8/3/09 - 8/4/09<br /> |
| - | | + | 8/6/09 - 8/9/09 <br /> |
| - | 8/6/09 - 8/9/09 | + | |
| - | | + | |
| | 8/11/09 - 8/13/09 | | 8/11/09 - 8/13/09 |
| | | | |
| | Date Example 1: | | Date Example 1: |
| | | | |
| - | This version is not in the sample db but it is easier to understand. | |
| | To use the code all you need to adjust is the inner query in the FROM Clause with the subquery. | | To use the code all you need to adjust is the inner query in the FROM Clause with the subquery. |
| | + | |
| | Note: If you have extremely large amounts of records then it might be best to use the inner query and make a table then use that table as the source for the outer query. | | Note: If you have extremely large amounts of records then it might be best to use the inner query and make a table then use that table as the source for the outer query. |
| | | | |
| - | [code] | + | [sql] |
| | SELECT D3.YourDate | | SELECT D3.YourDate |
| | , D3.NextDate | | , D3.NextDate |
| Line 98: |
Line 85: |
| | WHERE (((DateDiff("d",[YourDate],[NextDate]))>1)); | | WHERE (((DateDiff("d",[YourDate],[NextDate]))>1)); |
| | | | |
| - | [/code] | + | [/sql] |
| | | | |
| | | | |
| | Date Example 2: | | Date Example 2: |
| | | | |
| - | [code] | + | [sql] |
| | SELECT DATEADD("d",1,a.theDate) AS GapStart | | SELECT DATEADD("d",1,a.theDate) AS GapStart |
| | | | |
| Line 121: |
Line 108: |
| | | | |
| | WHERE DATEDIFF("d",a.theDate,DATEADD("d",-1,b.theDate))<>0; | | WHERE DATEDIFF("d",a.theDate,DATEADD("d",-1,b.theDate))<>0; |
| - | [/code] | + | [/sql] |
| | | | |
| | ---- | | ---- |
| Line 131: |
Line 118: |
| | | | |
| | --[[User:Dallr|Dallr]] 08:47, 24 July 2010 (EDT) | | --[[User:Dallr|Dallr]] 08:47, 24 July 2010 (EDT) |
| - | [[Category:Table of Contents]] [[Category:Access Wiki Index]] | |
Current revision
Find Missing Dates or Numbers within a Range
|
|  |
This page requires general cleanup in formatting or text to better fit the UA Wiki Guidelines |
I have also attached a sample application with the code and it can be found here: Media:Find Missing Dates or Numbers within a Range.zip
It shows how to use SQL to identifying missing dates or numbers within a range.
Identifying Missing Numbers
The table structure:
Table Name : Tbl_Numbers
Field name : InvoiceNo
So if your data is like this.
100
101
106
107
109
115
The SQL will return this Missing Data:
102-105
108
110-114
Number Example Code:
To use the code all you need to adjust is the inner query in the FROM Clause with the subquery.
Note: If you have extremely large amounts of records then it might be best to use the inner query and make a table then use that table as the source for the outer query.
SQL
SELECT T3.YourNumber
, T3.NextNum
, IIf([NextNum]-[YourNumber]=2,[YourNumber]+1,[YourNumber]+1 & "-" & [NextNum]-1) AS MissingNumbers
FROM
(SELECT Tbl_Numbers.InvoiceNo AS YourNumber
,(SELECT MIN(InvoiceNo) FROM Tbl_Numbers T1 WHERE T1.InvoiceNo >Tbl_Numbers.InvoiceNo) AS NextNum
FROM Tbl_Numbers) AS T3
WHERE ((([NextNum]-[YourNumber])>1));
Identifying Missing Dates
So if your data is like this.
8/1/09
8/2/09
8/5/09
8/10/09
8/14/09
The SQL will return this Missing Data:
8/3/09 - 8/4/09
8/6/09 - 8/9/09
8/11/09 - 8/13/09
Date Example 1:
To use the code all you need to adjust is the inner query in the FROM Clause with the subquery.
Note: If you have extremely large amounts of records then it might be best to use the inner query and make a table then use that table as the source for the outer query.
SQL
SELECT D3.YourDate
, D3.NextDate
, IIf(DateDiff("d",[YourDate],[NextDate])=2,DateAdd("d",1,[YourDate]),DateAdd("d",1,[YourDate]) & " - " & DateAdd("d",-1,[NextDate])) AS MissingDates
FROM
(SELECT tbl_Dates.TheDate As YourDate
, (SELECT Min(TheDate) FROM Tbl_Dates D1 WHERE D1.TheDate >Tbl_Dates.TheDate) As NextDate
FROM tbl_Dates) AS D3
WHERE (((DateDiff("d",[YourDate],[NextDate]))>1));
Date Example 2:
SQL
SELECT DATEADD("d",1,a.theDate) AS GapStart
, DATEADD("d",-1,b.theDate) AS GapEnd
FROM ( SELECT tbl_Dates.TheDate , (SELECT COUNT(*) FROM Tbl_Dates D1 WHERE D1.TheDate <= tbl_Dates.TheDate) AS ID
FROM tbl_Dates ) AS a
INNER JOIN
( SELECT tbl_Dates.TheDate, (SELECT COUNT(*) FROM Tbl_Dates D1 WHERE D1.TheDate <= tbl_Dates.TheDate) AS ID
FROM tbl_Dates ) AS b
ON a.ID=b.ID-1
WHERE DATEDIFF("d",a.theDate,DATEADD("d",-1,b.theDate))<>0;
--Dallr 08:47, 24 July 2010 (EDT)
Access wiki - Access Help and How-to - Microsoft Office > Viewing Find Missing Dates or Numbers within a Range
Find Missing Dates or Numbers within a Range
