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

Kamis, 27 Desember 2012

RE: [MS_AccessPros] How to find missing dates

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
>
>
>