What happened to the EMP.LastDay and EMP.FirstDay fields? From our
previous thread, I was understanding that you were using those values,
not the ones in IDRa table? My understanding from before is that only one
of the LastDay and FirstDay fields in the IDRa tablemight be populated in
a single record, and that both would be empty if the record isn't the one
for the first day or the last day?
This is the "qryAll" query that we last discussed:
SELECT EMP.EID, Dates.D
FROM EMP, Dates
WHERE Dates.D Between [Enter Beginning Date] And
[Enter Ending Date] AND (EMP.[LastDay] Is Null OR
EMP.[LastDay] >= [Enter Beginning Date]) AND
EMP.[FirstDay]<=[Enter Ending Date];
Using this as the starting point, it should be changed to this:
SELECT EMP.EID, EMP.FirstDay, EMP.LastDay, Dates.D
FROM EMP, Dates
WHERE Dates.D Between [Enter Beginning Date] And
[Enter Ending Date] AND (EMP.[LastDay] Is Null OR
EMP.[LastDay] >= [Enter Beginning Date]) AND
EMP.[FirstDay]<=[Enter Ending Date];
Then I believe that the qryGaps that you've posted in this thread should
work as you desire.
--
Ken Snell
<MS ACCESS MVP>
Randy said:
As an example, Lets say I have employee # 2045 whose first day is
8/1/05 and a last day of 9/15/05. If I query dates of 7/15 to 9/30 for
missing dates, this employee # 2045 shows up as missing dates from 7/15
to 7/31 and from 9/16 to 9/30 even though they have not worked during
these dates. Your queries work great if every employee has a first day
of 1/1/05 and a last day of 12/31/05. Thanks..Randy
The criterion in qryGaps as you've now got it structured -- doing an
additional filter based on the dates "LastDay" and "FirstDay" that are
in the IDRa table -- seems unnecessary? qryAll already filters for
those dates based on the dates that you enter as parameters.
What are you wanting to accomplish by the additional criteria in the
second query?
--
Ken Snell
<MS ACCESS MVP>
Thanks Ken..Randy
"qryAll" Below
SELECT IDRa.Eid, Dates.D, IDRa.LastDay, IDRa.FirstDay, Emp.Ename
FROM Dates, IDRa INNER JOIN Emp ON IDRa.Eid = Emp.EID
WHERE (((Dates.D) Between [Enter Beginning Date] And [Enter Ending
Date]) AND ((IDRa.LastDay) Is Null Or (IDRa.LastDay)>=[Enter Beginning
Date]) AND ((IDRa.FirstDay)<=[Enter Ending Date]));
"qryGaps" Below
SELECT Q.Eid, Q.D, Q.FirstDay, Q.LastDay, Q.Ename
FROM qryAll AS Q LEFT JOIN IDRa AS W ON (Q.Eid = W.Eid) AND (Q.D =
W.Current_Date)
GROUP BY Q.Eid, Q.D, Q.FirstDay, Q.LastDay, Q.Ename, W.Eid
HAVING (((Q.D) Between [Q].[FirstDay] And [Q].[LastDay] Or
(Q.D)>=[Q].[FirstDay]) AND ((W.Eid) Is Null))
ORDER BY Q.Eid, Q.D;
Randy -
To be sure that we're starting from the same point, post the query
(queries) exactly as you're now using it (them).
--
Ken Snell
<MS ACCESS MVP>
I have a query that Ken Snell helped me with. It detects missing
dates in
my employee timesheet db. My query has a field of [D] form tbl "Q",
a field
of [FirstDay] where a date was entered indicating the employees
first day of
work for the season, and [LastDay] for the date the employee was
laid off
for the season, both from tbl "Q". I also have two fields of [EID]
one from
tbl "Q" and one from tbl "W" I need to restrict the query to the
[FirstDay] up until I have entered the [LastDay] I have created a
criteria
entered in the field [D]. (Dates). >=[Q].[FirstDay] And
<=[Q].[LastDay]
It does not give results. It only give the headers with no data.
If I enter >=[Q].[FirstDay] I do get the dates from the first day
forward which is great, but I need the query to stop at the point
when the [LastDay] is entered. Thanks...Randy
