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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/