ACCESS FEED: RE: [MS_AccessPros] How to find missing dates

Jumat, 28 Desember 2012

RE: [MS_AccessPros] How to find missing dates

Hello Ahmed

Try this query:

=============================================================
PARAMETERS [Enter Start Date] DATE, [Enter End Date] DATE;

INSERT INTO tblDates (dtDate)

SELECT DateAdd("d", Thousands.nbr + Hundreds.nbr + Tens.nbr + Ones.nbr,
[Enter Start Date]) AS DateValue
FROM
( SELECT (( COUNT(*) - 1 ) * 1000 ) AS nbr
FROM ( SELECT TOP 10 id FROM MSysObjects ORDER BY id ) AS A
INNER JOIN ( SELECT TOP 10 id FROM MSysObjects ORDER BY id ) AS B
ON ( A.id <= B.id )
GROUP BY A.id ) AS Thousands,

( SELECT (( COUNT(*) - 1 ) * 100 ) AS nbr
FROM ( SELECT TOP 10 id FROM MSysObjects ORDER BY id ) AS C
INNER JOIN ( SELECT TOP 10 id FROM MSysObjects ORDER BY id ) AS D
ON ( C.id <= D.id )
GROUP BY C.id ) AS Hundreds,

( SELECT (( COUNT(*) - 1 ) * 10 ) AS nbr
FROM ( SELECT TOP 10 id FROM MSysObjects ORDER BY id ) AS E
INNER JOIN ( SELECT TOP 10 id FROM MSysObjects ORDER BY id ) AS F
ON ( E.id <= F.id )
GROUP BY E.id ) AS Tens,

( SELECT ( COUNT(*) - 1 ) AS nbr
FROM ( SELECT TOP 10 id FROM MSysObjects ORDER BY id ) AS G
INNER JOIN ( SELECT TOP 10 id FROM MSysObjects ORDER BY id ) AS H
ON ( G.id <= H.id )
GROUP BY G.id ) AS Ones

WHERE [Enter End Date] >= [Enter Start Date]
AND Thousands.nbr + Hundreds.nbr + Tens.nbr + Ones.nbr
BETWEEN 0 AND DateDiff("d", [Enter Start Date], [Enter End Date])

ORDER BY Thousands.nbr, Hundreds.nbr, Tens.nbr, Ones.nbr;
=============================================================

If you just need to list the dates, rather than append them to a table, then
delete the INSERT INTO line to convert it to a SELECT query. You could then
use the SELECT query in another query with a frustrated outer join to list
the missing dates.

Unfortunately I cannot claim the credit for this very clever query - I
modified one I found posted on UtterAccess :)

Best wishes,
Graham

> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Ahmed Hashim
> Sent: Friday, 28 December 2012 18:18
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] How to find missing dates
>
> Dear Graham,
>
> Thanks for your solution...I will definitely try this...
>
> Also can you suggest me a way to fill a table with just one field dtDate
> (Date/Time) with dates automatically by given ranges like 01/12/2012 and
> 31/12/2012 (date format is DD/MM/YYYY)?
>
> Thanks
>
> Ahmed
> On Fri, Dec 28, 2012 at 10:02 AM, Graham Mandeno <graham@mandeno.com>
> wrote:
>
> > **
> >
> >
> > Hello Ahmed
> >
> > The following query will give you the start and the end of each "gap"
> > in the dates for each EmpID. Just change all the occurrences of tblA
> > to the name of your table:
> >
> > SELECT
> > EmpID,
> > TransDate+1 as StartGap,
> > (SELECT MIN(TransDate) - 1 FROM tblA as T2 WHERE T2.EmpID=T1.EmpID and
> > T2.TransDate > T1.TransDate) AS EndGap FROM tblA as T1 WHERE not
> > exists (SELECT TransDate FROM tblA as T3 WHERE T3.EmpID=T1.EmpID and
> > T3.TransDate=T1.TransDate+1) AND T1.TransDate<>(SELECT Max(TransDate)
> > FROM tblA as T4 WHERE T4.EmpID=T1.EmpID);
> >
> > I assumed you would want to find the gaps for each employee, but if
> > not then just delete all the "Tx.EmpID=T1.EmpID" conditions from the
> > WHERE clauses.
> >
> > Best wishes,
> > Graham
> >
> > > -----Original Message-----
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Ahmed
> > Hashim
> > > Sent: Friday, 28 December 2012 05:10
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: Re: [MS_AccessPros] How to find missing dates
> > >
> > > Duane,
> > >
> > > Thank you very much...I will try that...
> > >
> > > Best regards
> > >
> > > Ahmed
> > >
> > > On Thu, Dec 27, 2012 at 8:53 PM, Duane Hookom
> > > <duanehookom@hotmail.com>wrote:
> > >
> > > > **
> > > >
> > > >
> > > > Ahmed,
> > > >
> > > > You can easily create a query of all dates based on simple table
> > > > [tblNum0_9] with a single field [Num] and values 0 - 9. To get a
> > > > query of
> > > > 1,000 dates beginning with the current date and going to 999 days
> > > > into the
> > > > future:
> > > >
> > > > SELECT H![Num]*100+T![Num]*10+O![Num]+Date() AS TheDate FROM
> > > > tblNum0_9 H, tblNum0_9 AS T, tblNum0_9 AS O
> > > >
> > > > I aliased the same table with H for hundreds, T for Tens, and O
> > > > for
> > > Ones.
> > > >
> > > > The results on 12/27/2012 (I expect yours will display like
> > > > 27/12/2012)
> > > >
> > > > TheDate
> > > > 12/27/2012
> > > > 12/28/2012
> > > > 12/29/2012
> > > > 12/30/2012
> > > > ...
> > > > 9/20/2015
> > > > 9/21/2015
> > > > 9/22/2015
> > > >
> > > > You can change Date() to Date()-500 to create a window of 1,000
> > > > dates with the current date in the middle.
> > > >
> > > > Duane Hookom MVP
> > > > MS Access
> > > >
> > > > ----------------------------------------
> > > > > From: ahmedhashim1@gmail.com
> > > > >
> > > > > Duane,
> > > > >
> > > > > Thanks for your reply....I guess that's the only way...Are there
> > > > > anything to fill a table with dates within the specific
> > > > > range...Like for e.g I can restrict user to first add all dates
> > > > > from 01/12/2012 and 31/12/2012 in separate Dates table (let's
> > > > > called it tblDates) and then do the data
> > > > entry
> > > > > in the transaction table so that we can find the missing dates,
> > > > > So for doing this, can we add something through Queries or VBA
> > > > > code to let user just enter the date ranges and it will fill the
> > > > > Dates in
> > > tblDates?
> > > > >
> > > > > Ahmed
> > > > >
> > > > > On Thu, Dec 27, 2012 at 7:23 PM, Duane Hookom
> > > > ><duanehookom@hotmail.com
> > > > >wrote:
> > > > >
> > > > > > **
> > > > > >
> > > > > >
> > > > > > Ahmed,
> > > > > >
> > > > > > I would first create a table or query with every possible date.
> > > > > > You can then use the Find Unmatched query wizard to determine
> > > > > > the missing
> > > > dates.
> > > > > >
> > > > > > Duane Hookom MVP
> > > > > > MS Access
> > > > > >
> > > > > > ----------------------------------------
> > > > > > > From: ahmedhashim1@gmail.com
> > > > > > >
> > > > > > > Hi to all,
> > > > > > >
> > > > > > > I've a database where data entry made in table for Salesman
> > > > > > > expense
> > > > > > recording.
> > > > > > >
> > > > > > > Table structure:
> > > > > > > TransID (Auto)
> > > > > > > Trans Date (Date Time)
> > > > > > > EmpID
> > > > > > > ProductID
> > > > > > > Area
> > > > > > >
> > > > > > > Data entry is made on date-to-date basis, I need a way to
> > > > > > > find if any
> > > > > > particular date entry is missing in table. Like for e.g Data
> > > > > > entry is
> > > > made
> > > > > > for following dates (Date format is DD/MM/YYYY):
> > > > > > >
> > > > > > > 03/12/2012
> > > > > > > 04/12/2012
> > > > > > > 06/12/2012
> > > > > > > 07/12/2012
> > > > > > > 08/12/2012
> > > > > > > 10/12/2012
> > > > > > > 12/12/2012
> > > > > > > 13/12/2012
> > > > > > > 14/12/2012
> > > > > > >
> > > > > > > In the example given following dates are missing:
> > > > > > > 05/12/2012
> > > > > > > 09/12/2012
> > > > > > > 11/12/2012
> > > > > > >
> > > > > > > How can we trace these missing dates? Any suggestions?
> > > Queries/VBA?
> > > > > > >
> > > > > > > I am using Ms.Access 2007.
> > > > > > >
> > > > > > > Thanks and best regards
> > > > > > >
> > > > > > > Ahmed
> > > >
> > > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> > >
> > >
> >
> >
> >
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>