Re: Missing dates?

Tech-Archive recommends: Fix windows errors by optimizing your registry

I get the header of "theDate" and "EID" but with no results. I should
have a result of 7/26/05 and EID of 2045 but it is blank. Heres a copy of
the code..Thanks..Randy

SELECT tblDate.theDate, EMP.EID
FROM tblDate, EMP
WHERE NOT EXIST (SELECT IDRa_ID FROM IDRa
WHERE IDRa.EID = EMP.EID
AND IDRa.Current_Date = tblDate.TheDate);


"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:eQQJHcJmFHA.1232@xxxxxxxxxxxxxxxxxxxxxxx
> You dropped the EXISTS from the start of the WHERE clause.
>
> You probably want to limit the date range in the main query to just one
> period as well. This will dramatically decrease the number of records
> Access has to process. For eample, if you have 2000 dates (5+ years) and
> 100 employees, the Cartesian product generates 200,000 records, whereas if
> you limit it to the dates for last week it has 700 records to process. And
> of course the subquery has to run for each one, so that magnifies the
> difference.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Randy" <kerspi@xxxxxxxxxxx> wrote in message
> news:zuWdnXMTYOlPumzfRVn-qw@xxxxxxxxxxxxxx
>>I tried this as you suggested. the query takes about 30 seconds to run,
>>then there is no result. Heres what I have now.
>>
>> SELECT tblDate.theDate, EMP.EID
>> FROM tblDate, EMP
>> WHERE ((((SELECT IDRa_ID FROM IDRa
>> WHERE IDRa.EID = EMP.EID
>> AND IDRa.Current_Date = tblDate.theDate))=False));
>>
>>
>>
>> "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
>> news:uwTGcQ8lFHA.3304@xxxxxxxxxxxxxxxxxxxxxxx
>>> The cartesian product needs to be on every combination of date and
>>> employee.
>>>
>>> Try:
>>> SELECT tblDate.theDate, EMP.EID
>>> FROM tblDate, EMP
>>> WHERE ...
>>>
>>> "Randy" <kerspi@xxxxxxxxxxx> wrote in message
>>> news:YPKdnQTYvIauOnLfRVn-1Q@xxxxxxxxxxxxxx
>>>> Allen, I tried your suggestion below but I come up with no result in
>>>> the query. Here is a copy of the sql statement. "IDRa" is the
>>>> time*** tbl, primary key is "IDRa_ID". The employee tbl is "EMP"
>>>> where all the employees and employee id numbers [EID] are listed.
>>>> Thanks for your help...Randy
>>>>
>>>> SELECT tblDate.theDate
>>>> FROM tblDate, IDRa
>>>> WHERE NOT EXIST (SELECT IDRa_ID FROM IDRa
>>>> WHERE IDRa.EID = EMP.EID
>>>> AND IDRa.Current_Date = tblDate.TheDate);
>>>>
>>>> "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
>>>> news:upkz9ovlFHA.2916@xxxxxxxxxxxxxxxxxxxxxxx
>>>>> Randy, the missing dates have to come from somewhere, so you need a
>>>>> table of all dates.
>>>>>
>>>>> If you already have a roster of who should be on when, that would
>>>>> work. Or you could create a table of all dates, and then possibly
>>>>> remove the public holidays when noone works.
>>>>>
>>>>> You can then create a query that:
>>>>> - Selects all dates from this table in the date range.
>>>>> - Includes the Employee table (no join, i.e. a cartesian product.)
>>>>> - Uses a subquery to finds the timesheets that don't exist for the
>>>>> employee + date
>>>>>
>>>>> The WHERE clause of your query will include this kind of thing:
>>>>> WHERE NOT EXIST (SELECT TimesheeID FROM tblTime***
>>>>> WHERE tblTime***.EmployeeID = tblEmployee.EmployeeID
>>>>> AND tblTime***.WorkDate = tblDate.TheDate)
>>>>>
>>>>> If subqueries are new, Microsoft has an introductory article:
>>>>> How to Create and Use Subqueries
>>>>> at:
>>>>> http://support.microsoft.com/?id=209066
>>>>>
>>>>> If you need to programmatically fill a table with dates, create a
>>>>> table named (say) "tblDate", with one date/time field named "theDate"
>>>>> as primary key. Then you can add 10 years of dates like this:
>>>>>
>>>>> Function MakeDates()
>>>>> Dim dt As Date
>>>>> Dim rs As DAO.Recordset
>>>>>
>>>>> Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
>>>>> With rs
>>>>> For dt = #1/1/2005# To #12/31/2014#
>>>>> .AddNew
>>>>> !TheDate = dt
>>>>> .Update
>>>>> Next
>>>>> End With
>>>>> rs.Close
>>>>> Set rs = Nothing
>>>>> End Function
>>>>>
>>>>> "Randy" <kerspi@xxxxxxxxxxx> wrote in message
>>>>> news:VaSdndLEoI3I43PfRVn-tA@xxxxxxxxxxxxxx
>>>>>> Access 2000. I have an employee db. where employee timesheets are
>>>>>> entered. I need a query that will return missing dates or if a
>>>>>> time*** from a particular date that is missing. I have a field for
>>>>>> entering the date [Currrent_date] and a field where I enter [CID]
>>>>>> "First Day" and "Last Day" This is the employees first day and last
>>>>>> day in this office. What is the best approach...Thanks..Randy
>
>


.

Quantcast