Help again from Ken Snell (Query)

Ken you helped me last week with a query to find missing dates from my
employee db. It works great except I have found that it returns all dates
from my employee database which contains approx 1000 employees. This is a
statewide employee table. I need it to return only currently working
employees (About 25). When I return an employee from furlough or re-hire
them I enter data of "First Day" which has a coresponding code of 50 into my
table of "IDRb" which is a table used for the subform of my "IDR" form.,
for the first day back to work and I enter "Last Day' which has a
coresponding code of 60 for the last day worked for the season. The codes
50 and 60 are selected from a table of "CommCode" by a combo box. This is
my last hurdle before I can distribute the db to other offices...Thank
you..Randy
qryAll query:
-------------
SELECT EMP.EID, Dates.D
FROM EMP, Dates
WHERE (((Dates.D) Between [Enter Beginning Date] And
[Enter Ending Date]));


qryGaps query:
---------------
SELECT Q.EID, Q.D
FROM qryAll AS Q LEFT JOIN IDRa AS W
ON (Q.EID = W.Eid) AND (Q.D = W.Current_Date)
WHERE (((Q.EID) Like 2045) AND ((W.Eid) Is Null))
ORDER BY Q.EID, Q.D;

--

Ken Snell
MS ACCESS MVP




"Randy" wrote in message
...


IDRa_ID Eid Current_Date District_Id TotalHours TotalMiles TotalFees
tblMonth StateMiles StateLicense Expenses AccountID
865 2045 7/18/2005 764 8 48 $0.00 7 0 $0.00
866 2045 7/19/2005 764 8 142 $0.00 7 0 $0.00
867 2045 7/20/2005 764 8 38 $0.00 7 0 $0.00
869 2045 7/22/2005 764 8 0 $0.00 7 0 $0.00
870 2045 7/23/2005 764 0 0 $0.00 7 0 $0.00


D
2/18/2005
2/19/2005
2/20/2005
2/21/2005
2/22/2005
2/23/2005
2/24/2005
2/25/2005
2/26/2005
2/27/2005
2/28/2005


Emp_ID EID Ename Payroll HQ_Dist
1 2045 John Smith P 764


I hope this comes out ok..


"Ken Snell [MVP]" wrote in message
...
No, what I'm asking is that you post the actual data from the tables for
records that contain a value of 2045 for the EID and/or the value of
2/21/05 for the Current_Dat. I need to see the actual data so that I can
see how the query is selecting the records. As I noted, I expect the
problem is because the query is not designed quite right to match your
data, and seeing your actual data records will help me identify what I
need to change in the query structure that I suggested.
--

Ken Snell
MS ACCESS MVP


"Randy" wrote in message
...
The query returns 11 times EID with data of 2045, and Current_Date with
data of 2/21/05 which is the actual missing date. Thanks again.

"Ken Snell [MVP]" wrote in message
...
Also show the data for the specific example that you say is returning
11 records of the same employee/date combination. I need to see the
data from all the tables that relate to that specific employee and
date.

--

Ken Snell
MS ACCESS MVP

"Randy" wrote in message
...
Here are the three tables used. I really appreciate your help..Randy
1st table: "IDRa" With primary key of [IDRa_ID] (AutoNumber) and
[EID] (Number), [Current_Date] (Date/Time), [District_ID] (Number),
[TotalHours] (Number), [TotalMiles] (Number), [TotalFees] (Currency)
2nd table: "EMP" with primary key of [EMP_ID] (Autonumber), [EID]
(Number), [EName] (Text), [Payroll] (Text), [HQ_Dist] (Number)
3rd table: "Dates" with one field [D] (Date/Time)


"Ken Snell [MVP]" wrote in message
...
I'd need to see your data to provide a suggestion. My initial
thought is that we need one more join in the second query, but I
don't know that until I see what your entire table structure and
data values are. Can you post that information?
--

Ken Snell
MS ACCESS MVP


"Randy" wrote in message
...
I'm getting closer, I made a few changes to the sql. I now get the
missing date, but the result is 11 records of the same missing
date..Any thoughts..Thanks..Ken....Randy

SELECT [EMP].[EID], [Current_Date]
FROM EMP, IDRa
WHERE Current_Date Between [Enter Beginning Date] And [Enter Ending
Date];

SELECT Q.EID, Q.Current_Date
FROM qryAll AS Q LEFT JOIN IDRa AS W ON (Q.EID = W.Eid) AND
(Q.Current_Date = W.Current_Date)
WHERE (((Q.EID) Like 2045) AND ((W.Eid) Is Null));

I am not understanding the first day and last day data in terms of how it
relates to the tables that we were using. Are you entering a value of 50
into a field in a table? Which field and which table? How is that table
related to the employee table? Same questions for the 60 value. How do these
values of 50 and 60 relate to the [Enter Beginning Date] and the [Enter
Ending Date] parameters?

I need to have a better understanding of the data so that we can identify
how to properly select what you want.
--

Ken Snell
MS ACCESS MVP




"Randy" wrote in message
...
Ken you helped me last week with a query to find missing dates from my
employee db. It works great except I have found that it returns all dates
from my employee database which contains approx 1000 employees. This is a
statewide employee table. I need it to return only currently working
employees (About 25). When I return an employee from furlough or re-hire
them I enter data of "First Day" which has a coresponding code of 50 into
my table of "IDRb" which is a table used for the subform of my "IDR"
form., for the first day back to work and I enter "Last Day' which has a
coresponding code of 60 for the last day worked for the season. The codes
50 and 60 are selected from a table of "CommCode" by a combo box. This is
my last hurdle before I can distribute the db to other offices...Thank
you..Randy
qryAll query:
-------------
SELECT EMP.EID, Dates.D
FROM EMP, Dates
WHERE (((Dates.D) Between [Enter Beginning Date] And
[Enter Ending Date]));


qryGaps query:
---------------
SELECT Q.EID, Q.D
FROM qryAll AS Q LEFT JOIN IDRa AS W
ON (Q.EID = W.Eid) AND (Q.D = W.Current_Date)
WHERE (((Q.EID) Like 2045) AND ((W.Eid) Is Null))
ORDER BY Q.EID, Q.D;

--

Ken Snell
MS ACCESS MVP




"Randy" wrote in message
...


IDRa_ID Eid Current_Date District_Id TotalHours TotalMiles
TotalFees
tblMonth StateMiles StateLicense Expenses AccountID
865 2045 7/18/2005 764 8 48 $0.00 7 0 $0.00
866 2045 7/19/2005 764 8 142 $0.00 7 0 $0.00
867 2045 7/20/2005 764 8 38 $0.00 7 0 $0.00
869 2045 7/22/2005 764 8 0 $0.00 7 0 $0.00
870 2045 7/23/2005 764 0 0 $0.00 7 0 $0.00


D
2/18/2005
2/19/2005
2/20/2005
2/21/2005
2/22/2005
2/23/2005
2/24/2005
2/25/2005
2/26/2005
2/27/2005
2/28/2005


Emp_ID EID Ename Payroll HQ_Dist
1 2045 John Smith P 764


I hope this comes out ok..


"Ken Snell [MVP]" wrote in message
...
No, what I'm asking is that you post the actual data from the tables
for
records that contain a value of 2045 for the EID and/or the value of
2/21/05 for the Current_Dat. I need to see the actual data so that I
can
see how the query is selecting the records. As I noted, I expect the
problem is because the query is not designed quite right to match your
data, and seeing your actual data records will help me identify what I
need to change in the query structure that I suggested.
--

Ken Snell
MS ACCESS MVP


"Randy" wrote in message
...
The query returns 11 times EID with data of 2045, and Current_Date
with
data of 2/21/05 which is the actual missing date. Thanks again.

"Ken Snell [MVP]" wrote in message
...
Also show the data for the specific example that you say is returning
11 records of the same employee/date combination. I need to see the
data from all the tables that relate to that specific employee and
date.

--

Ken Snell
MS ACCESS MVP

"Randy" wrote in message
...
Here are the three tables used. I really appreciate your
help..Randy
1st table: "IDRa" With primary key of [IDRa_ID] (AutoNumber) and
[EID] (Number), [Current_Date] (Date/Time), [District_ID] (Number),
[TotalHours] (Number), [TotalMiles] (Number), [TotalFees] (Currency)
2nd table: "EMP" with primary key of [EMP_ID] (Autonumber), [EID]
(Number), [EName] (Text), [Payroll] (Text), [HQ_Dist] (Number)
3rd table: "Dates" with one field [D] (Date/Time)


"Ken Snell [MVP]" wrote in
message
...
I'd need to see your data to provide a suggestion. My initial
thought is that we need one more join in the second query, but I
don't know that until I see what your entire table structure and
data values are. Can you post that information?
--

Ken Snell
MS ACCESS MVP


"Randy" wrote in message
...
I'm getting closer, I made a few changes to the sql. I now get
the
missing date, but the result is 11 records of the same missing
date..Any thoughts..Thanks..Ken....Randy

SELECT [EMP].[EID], [Current_Date]
FROM EMP, IDRa
WHERE Current_Date Between [Enter Beginning Date] And [Enter
Ending
Date];

SELECT Q.EID, Q.Current_Date
FROM qryAll AS Q LEFT JOIN IDRa AS W ON (Q.EID = W.Eid) AND
(Q.Current_Date = W.Current_Date)
WHERE (((Q.EID) Like 2045) AND ((W.Eid) Is Null));

The tables used in "QryAll" query are "EMP" and "Dates". The "qryGaps"
query is joined with "QryAll" query but also includes the table "IDRa". In
my "IDRb" table (Sub to my form IDR) which is related to "IDRa" table via
"IDRa_ID" primary key, I have a field of [CID] which is where the code of
"50" for "First Day" and "60" for "Last Day" is entered. "50" or "60" is
selected from the table "CommCode" field of [CID] via combobox. [CID]
corresponds with the field of [Desc]. Example: [CID] of 50 = [Desc] First
Day. Another example: The [Current_Date] of 8/15/05, [EID] 2045 or what
ever employee I use, is entered in the table "IDRa". The table "IDRb" is
where I enter [CID] 50 or 60 and other employee timesheet info. Does this
help?..Thanks..Randy

"Ken Snell [MVP]" wrote in message
...
I am not understanding the first day and last day data in terms of how it
relates to the tables that we were using. Are you entering a value of 50
into a field in a table? Which field and which table? How is that table
related to the employee table? Same questions for the 60 value. How do
these values of 50 and 60 relate to the [Enter Beginning Date] and the
[Enter Ending Date] parameters?

I need to have a better understanding of the data so that we can identify
how to properly select what you want.
--

Ken Snell
MS ACCESS MVP




"Randy" wrote in message
...
Ken you helped me last week with a query to find missing dates from my
employee db. It works great except I have found that it returns all
dates from my employee database which contains approx 1000 employees.
This is a statewide employee table. I need it to return only currently
working employees (About 25). When I return an employee from furlough or
re-hire them I enter data of "First Day" which has a coresponding code of
50 into my table of "IDRb" which is a table used for the subform of my
"IDR" form., for the first day back to work and I enter "Last Day' which
has a coresponding code of 60 for the last day worked for the season.
The codes 50 and 60 are selected from a table of "CommCode" by a combo
box. This is my last hurdle before I can distribute the db to other
offices...Thank you..Randy
qryAll query:
-------------
SELECT EMP.EID, Dates.D
FROM EMP, Dates
WHERE (((Dates.D) Between [Enter Beginning Date] And
[Enter Ending Date]));


qryGaps query:
---------------
SELECT Q.EID, Q.D
FROM qryAll AS Q LEFT JOIN IDRa AS W
ON (Q.EID = W.Eid) AND (Q.D = W.Current_Date)
WHERE (((Q.EID) Like 2045) AND ((W.Eid) Is Null))
ORDER BY Q.EID, Q.D;

--

Ken Snell
MS ACCESS MVP




"Randy" wrote in message
...


IDRa_ID Eid Current_Date District_Id TotalHours TotalMiles
TotalFees
tblMonth StateMiles StateLicense Expenses AccountID
865 2045 7/18/2005 764 8 48 $0.00 7 0 $0.00
866 2045 7/19/2005 764 8 142 $0.00 7 0 $0.00
867 2045 7/20/2005 764 8 38 $0.00 7 0 $0.00
869 2045 7/22/2005 764 8 0 $0.00 7 0 $0.00
870 2045 7/23/2005 764 0 0 $0.00 7 0 $0.00


D
2/18/2005
2/19/2005
2/20/2005
2/21/2005
2/22/2005
2/23/2005
2/24/2005
2/25/2005
2/26/2005
2/27/2005
2/28/2005


Emp_ID EID Ename Payroll HQ_Dist
1 2045 John Smith P 764


I hope this comes out ok..


"Ken Snell [MVP]" wrote in message
...
No, what I'm asking is that you post the actual data from the tables
for
records that contain a value of 2045 for the EID and/or the value of
2/21/05 for the Current_Dat. I need to see the actual data so that I
can
see how the query is selecting the records. As I noted, I expect the
problem is because the query is not designed quite right to match your
data, and seeing your actual data records will help me identify what I
need to change in the query structure that I suggested.
--

Ken Snell
MS ACCESS MVP


"Randy" wrote in message
...
The query returns 11 times EID with data of 2045, and Current_Date
with
data of 2/21/05 which is the actual missing date. Thanks again.

"Ken Snell [MVP]" wrote in message
...
Also show the data for the specific example that you say is
returning
11 records of the same employee/date combination. I need to see the
data from all the tables that relate to that specific employee and
date.

--

Ken Snell
MS ACCESS MVP

"Randy" wrote in message
...
Here are the three tables used. I really appreciate your
help..Randy
1st table: "IDRa" With primary key of [IDRa_ID] (AutoNumber) and
[EID] (Number), [Current_Date] (Date/Time), [District_ID] (Number),
[TotalHours] (Number), [TotalMiles] (Number), [TotalFees]
(Currency)
2nd table: "EMP" with primary key of [EMP_ID] (Autonumber), [EID]
(Number), [EName] (Text), [Payroll] (Text), [HQ_Dist] (Number)
3rd table: "Dates" with one field [D] (Date/Time)


"Ken Snell [MVP]" wrote in
message
...
I'd need to see your data to provide a suggestion. My initial
thought is that we need one more join in the second query, but I
don't know that until I see what your entire table structure and
data values are. Can you post that information?
--

Ken Snell
MS ACCESS MVP


"Randy" wrote in message
...
I'm getting closer, I made a few changes to the sql. I now get
the
missing date, but the result is 11 records of the same missing
date..Any thoughts..Thanks..Ken....Randy

SELECT [EMP].[EID], [Current_Date]
FROM EMP, IDRa
WHERE Current_Date Between [Enter Beginning Date] And [Enter
Ending
Date];

SELECT Q.EID, Q.Current_Date
FROM qryAll AS Q LEFT JOIN IDRa AS W ON (Q.EID = W.Eid) AND
(Q.Current_Date = W.Current_Date)
WHERE (((Q.EID) Like 2045) AND ((W.Eid) Is Null));

Let me see if I am understanding what you want to do.

Your Dates table contains all possible dates that can / should be the dates
on the time records for each employee.

Each employee (characterized by each unique EID value) has a start date and
an end date.

For each employee, the "gap" analysis should be based only on the date range
covered by the start date and the end date.

Is this what you're asking?
--

Ken Snell
MS ACCESS MVP



"Randy" wrote in message
...
The tables used in "QryAll" query are "EMP" and "Dates". The "qryGaps"
query is joined with "QryAll" query but also includes the table "IDRa".
In my "IDRb" table (Sub to my form IDR) which is related to "IDRa" table
via "IDRa_ID" primary key, I have a field of [CID] which is where the code
of "50" for "First Day" and "60" for "Last Day" is entered. "50" or "60"
is selected from the table "CommCode" field of [CID] via combobox. [CID]
corresponds with the field of [Desc]. Example: [CID] of 50 = [Desc] First
Day. Another example: The [Current_Date] of 8/15/05, [EID] 2045 or
what ever employee I use, is entered in the table "IDRa". The table
"IDRb" is where I enter [CID] 50 or 60 and other employee timesheet info.
Does this help?..Thanks..Randy

"Ken Snell [MVP]" wrote in message
...
I am not understanding the first day and last day data in terms of how it
relates to the tables that we were using. Are you entering a value of 50
into a field in a table? Which field and which table? How is that table
related to the employee table? Same questions for the 60 value. How do
these values of 50 and 60 relate to the [Enter Beginning Date] and the
[Enter Ending Date] parameters?

I need to have a better understanding of the data so that we can identify
how to properly select what you want.
--

Ken Snell
MS ACCESS MVP




"Randy" wrote in message
...
Ken you helped me last week with a query to find missing dates from my
employee db. It works great except I have found that it returns all
dates from my employee database which contains approx 1000 employees.
This is a statewide employee table. I need it to return only currently
working employees (About 25). When I return an employee from furlough or
re-hire them I enter data of "First Day" which has a coresponding code
of 50 into my table of "IDRb" which is a table used for the subform of
my "IDR" form., for the first day back to work and I enter "Last Day'
which has a coresponding code of 60 for the last day worked for the
season. The codes 50 and 60 are selected from a table of "CommCode" by a
combo box. This is my last hurdle before I can distribute the db to
other offices...Thank you..Randy
qryAll query:
-------------
SELECT EMP.EID, Dates.D
FROM EMP, Dates
WHERE (((Dates.D) Between [Enter Beginning Date] And
[Enter Ending Date]));


qryGaps query:
---------------
SELECT Q.EID, Q.D
FROM qryAll AS Q LEFT JOIN IDRa AS W
ON (Q.EID = W.Eid) AND (Q.D = W.Current_Date)
WHERE (((Q.EID) Like 2045) AND ((W.Eid) Is Null))
ORDER BY Q.EID, Q.D;

--

Ken Snell
MS ACCESS MVP




"Randy" wrote in message
...


IDRa_ID Eid Current_Date District_Id TotalHours TotalMiles
TotalFees
tblMonth StateMiles StateLicense Expenses AccountID
865 2045 7/18/2005 764 8 48 $0.00 7 0 $0.00
866 2045 7/19/2005 764 8 142 $0.00 7 0 $0.00
867 2045 7/20/2005 764 8 38 $0.00 7 0 $0.00
869 2045 7/22/2005 764 8 0 $0.00 7 0 $0.00
870 2045 7/23/2005 764 0 0 $0.00 7 0 $0.00


D
2/18/2005
2/19/2005
2/20/2005
2/21/2005
2/22/2005
2/23/2005
2/24/2005
2/25/2005
2/26/2005
2/27/2005
2/28/2005


Emp_ID EID Ename Payroll HQ_Dist
1 2045 John Smith P 764


I hope this comes out ok..


"Ken Snell [MVP]" wrote in message
...
No, what I'm asking is that you post the actual data from the tables
for
records that contain a value of 2045 for the EID and/or the value of
2/21/05 for the Current_Dat. I need to see the actual data so that I
can
see how the query is selecting the records. As I noted, I expect the
problem is because the query is not designed quite right to match
your
data, and seeing your actual data records will help me identify what
I
need to change in the query structure that I suggested.
--

Ken Snell
MS ACCESS MVP


"Randy" wrote in message
...
The query returns 11 times EID with data of 2045, and Current_Date
with
data of 2/21/05 which is the actual missing date. Thanks again.

"Ken Snell [MVP]" wrote in
message
...
Also show the data for the specific example that you say is
returning
11 records of the same employee/date combination. I need to see the
data from all the tables that relate to that specific employee and
date.

--

Ken Snell
MS ACCESS MVP

"Randy" wrote in message
...
Here are the three tables used. I really appreciate your
help..Randy
1st table: "IDRa" With primary key of [IDRa_ID] (AutoNumber) and
[EID] (Number), [Current_Date] (Date/Time), [District_ID]
(Number),
[TotalHours] (Number), [TotalMiles] (Number), [TotalFees]
(Currency)
2nd table: "EMP" with primary key of [EMP_ID] (Autonumber), [EID]
(Number), [EName] (Text), [Payroll] (Text), [HQ_Dist] (Number)
3rd table: "Dates" with one field [D] (Date/Time)


"Ken Snell [MVP]" wrote in
message
...
I'd need to see your data to provide a suggestion. My initial
thought is that we need one more join in the second query, but I
don't know that until I see what your entire table structure and
data values are. Can you post that information?
--

Ken Snell
MS ACCESS MVP


"Randy" wrote in message
...
I'm getting closer, I made a few changes to the sql. I now get
the
missing date, but the result is 11 records of the same missing
date..Any thoughts..Thanks..Ken....Randy

SELECT [EMP].[EID], [Current_Date]
FROM EMP, IDRa
WHERE Current_Date Between [Enter Beginning Date] And [Enter
Ending
Date];

SELECT Q.EID, Q.Current_Date
FROM qryAll AS Q LEFT JOIN IDRa AS W ON (Q.EID = W.Eid) AND
(Q.Current_Date = W.Current_Date)
WHERE (((Q.EID) Like 2045) AND ((W.Eid) Is Null));

Yes, but I need results for only employee's that are currently working.
Employees that are currently working have a data entry of "50" which
corresponds to "First Day" in the "IDRb" table. I need to see any missing
[Current_Date] up to now, with employees with "50" or "First Day" . This
would show me only employees with the code [CID] of "50" with missing dates.

[EID] = 2045 [CID] = 50 [Desc] = First Day [Current_Date] = 8/1/05

8/2/05

8/4/05

[EID] = 3040 [CID] = 50 [Desc] = First Day [Current_Date] = 8/1/05

8/3/05

8/4/05

Thie missing date is 8/3/05 for employee 2045 and 8/2/05 for employee 3040
The other 1000 employees should not be listed. Thanks a lot for your help.

"Ken Snell [MVP]" wrote in message
...
Let me see if I am understanding what you want to do.

Your Dates table contains all possible dates that can / should be the
dates on the time records for each employee.

Each employee (characterized by each unique EID value) has a start date
and an end date.

For each employee, the "gap" analysis should be based only on the date
range covered by the start date and the end date.

Is this what you're asking?
--

Ken Snell
MS ACCESS MVP



"Randy" wrote in message
...
The tables used in "QryAll" query are "EMP" and "Dates". The "qryGaps"
query is joined with "QryAll" query but also includes the table "IDRa".
In my "IDRb" table (Sub to my form IDR) which is related to "IDRa" table
via "IDRa_ID" primary key, I have a field of [CID] which is where the
code of "50" for "First Day" and "60" for "Last Day" is entered. "50" or
"60" is selected from the table "CommCode" field of [CID] via combobox.
[CID] corresponds with the field of [Desc]. Example: [CID] of 50 =
[Desc] First Day. Another example: The [Current_Date] of 8/15/05,
[EID] 2045 or what ever employee I use, is entered in the table "IDRa".
The table "IDRb" is where I enter [CID] 50 or 60 and other employee
timesheet info. Does this help?..Thanks..Randy

"Ken Snell [MVP]" wrote in message
...
I am not understanding the first day and last day data in terms of how it
relates to the tables that we were using. Are you entering a value of 50
into a field in a table? Which field and which table? How is that table
related to the employee table? Same questions for the 60 value. How do
these values of 50 and 60 relate to the [Enter Beginning Date] and the
[Enter Ending Date] parameters?

I need to have a better understanding of the data so that we can
identify how to properly select what you want.
--

Ken Snell
MS ACCESS MVP




"Randy" wrote in message
...
Ken you helped me last week with a query to find missing dates from my
employee db. It works great except I have found that it returns all
dates from my employee database which contains approx 1000 employees.
This is a statewide employee table. I need it to return only currently
working employees (About 25). When I return an employee from furlough
or re-hire them I enter data of "First Day" which has a coresponding
code of 50 into my table of "IDRb" which is a table used for the
subform of my "IDR" form., for the first day back to work and I enter
"Last Day' which has a coresponding code of 60 for the last day worked
for the season. The codes 50 and 60 are selected from a table of
"CommCode" by a combo box. This is my last hurdle before I can
distribute the db to other offices...Thank you..Randy
qryAll query:
-------------
SELECT EMP.EID, Dates.D
FROM EMP, Dates
WHERE (((Dates.D) Between [Enter Beginning Date] And
[Enter Ending Date]));


qryGaps query:
---------------
SELECT Q.EID, Q.D
FROM qryAll AS Q LEFT JOIN IDRa AS W
ON (Q.EID = W.Eid) AND (Q.D = W.Current_Date)
WHERE (((Q.EID) Like 2045) AND ((W.Eid) Is Null))
ORDER BY Q.EID, Q.D;

--

Ken Snell
MS ACCESS MVP




"Randy" wrote in message
...


IDRa_ID Eid Current_Date District_Id TotalHours TotalMiles
TotalFees
tblMonth StateMiles StateLicense Expenses AccountID
865 2045 7/18/2005 764 8 48 $0.00 7 0 $0.00
866 2045 7/19/2005 764 8 142 $0.00 7 0 $0.00
867 2045 7/20/2005 764 8 38 $0.00 7 0 $0.00
869 2045 7/22/2005 764 8 0 $0.00 7 0 $0.00
870 2045 7/23/2005 764 0 0 $0.00 7 0 $0.00


D
2/18/2005
2/19/2005
2/20/2005
2/21/2005
2/22/2005
2/23/2005
2/24/2005
2/25/2005
2/26/2005
2/27/2005
2/28/2005


Emp_ID EID Ename Payroll HQ_Dist
1 2045 John Smith P 764


I hope this comes out ok..


"Ken Snell [MVP]" wrote in message
...
No, what I'm asking is that you post the actual data from the tables
for
records that contain a value of 2045 for the EID and/or the value of
2/21/05 for the Current_Dat. I need to see the actual data so that I
can
see how the query is selecting the records. As I noted, I expect the
problem is because the query is not designed quite right to match
your
data, and seeing your actual data records will help me identify what
I
need to change in the query structure that I suggested.
--

Ken Snell
MS ACCESS MVP


"Randy" wrote in message
...
The query returns 11 times EID with data of 2045, and Current_Date
with
data of 2/21/05 which is the actual missing date. Thanks again.

"Ken Snell [MVP]" wrote in
message
...
Also show the data for the specific example that you say is
returning
11 records of the same employee/date combination. I need to see
the
data from all the tables that relate to that specific employee and
date.

--

Ken Snell
MS ACCESS MVP

"Randy" wrote in message
...
Here are the three tables used. I really appreciate your
help..Randy
1st table: "IDRa" With primary key of [IDRa_ID] (AutoNumber)
and
[EID] (Number), [Current_Date] (Date/Time), [District_ID]
(Number),
[TotalHours] (Number), [TotalMiles] (Number), [TotalFees]
(Currency)
2nd table: "EMP" with primary key of [EMP_ID] (Autonumber),
[EID]
(Number), [EName] (Text), [Payroll] (Text), [HQ_Dist] (Number)
3rd table: "Dates" with one field [D] (Date/Time)


"Ken Snell [MVP]" wrote in
message
...
I'd need to see your data to provide a suggestion. My initial
thought is that we need one more join in the second query, but I
don't know that until I see what your entire table structure and
data values are. Can you post that information?
--

Ken Snell
MS ACCESS MVP


"Randy" wrote in message
...
I'm getting closer, I made a few changes to the sql. I now get
the
missing date, but the result is 11 records of the same missing
date..Any thoughts..Thanks..Ken....Randy

SELECT [EMP].[EID], [Current_Date]
FROM EMP, IDRa
WHERE Current_Date Between [Enter Beginning Date] And [Enter
Ending
Date];

SELECT Q.EID, Q.Current_Date
FROM qryAll AS Q LEFT JOIN IDRa AS W ON (Q.EID = W.Eid) AND
(Q.Current_Date = W.Current_Date)
WHERE (((Q.EID) Like 2045) AND ((W.Eid) Is Null));

Randy -- I'm tied up on a couple of things at the moment, but I will get
back to you as quickly as I can!

--

Ken Snell
MS ACCESS MVP

"Randy" wrote in message
news
Yes, but I need results for only employee's that are currently working.
Employees that are currently working have a data entry of "50" which
corresponds to "First Day" in the "IDRb" table. I need to see any missing
[Current_Date] up to now, with employees with "50" or "First Day" . This
would show me only employees with the code [CID] of "50" with missing
dates.

[EID] = 2045 [CID] = 50 [Desc] = First Day [Current_Date] = 8/1/05

8/2/05

8/4/05

[EID] = 3040 [CID] = 50 [Desc] = First Day [Current_Date] = 8/1/05

8/3/05

8/4/05

Thie missing date is 8/3/05 for employee 2045 and 8/2/05 for employee 3040
The other 1000 employees should not be listed. Thanks a lot for your
help.

Right now tbl "EID" is linked to "IDRa" vis [EID] Both have [EID] (Employee
Id #) tbl "IDRb" is linked to "IDRa" via [IDRa_ID] (Primary key in IDRa and
number in IDRb) tbl IDRa is in the middle of IDRb and EMP. Do I need to
add a field such as [EID] to tbl "IDRb"?..Randy
"Ken Snell [MVP]" wrote in message
...
If I'm understanding correctly, it woudl best if we modify the qryAll
query to do filter out the nonworking employees. However, to do that, I
need to know how we can relate EMP table with the IDRb table. The reason
for this is that we will "look up" an employee in the IDRb table to see if
he/she has the CID value of 50. I wouldn't modify qryGaps to use the link
of IDRa to IDRb because, if an employee doesn't have a record in IDRa
(meaning that there is a gap), you won't be able to link into IDRb table
to check the CID value.

If you can show me how we can related EMP and IDRb tables, we can make
this happen.
--

Ken Snell
MS ACCESS MVP




"Randy" wrote in message
news
Yes, but I need results for only employee's that are currently working.
Employees that are currently working have a data entry of "50" which
corresponds to "First Day" in the "IDRb" table. I need to see any
missing [Current_Date] up to now, with employees with "50" or "First Day"
. This would show me only employees with the code [CID] of "50" with
missing dates.

[EID] = 2045 [CID] = 50 [Desc] = First Day [Current_Date] = 8/1/05

8/2/05

8/4/05

[EID] = 3040 [CID] = 50 [Desc] = First Day [Current_Date] = 8/1/05

8/3/05

8/4/05

Thie missing date is 8/3/05 for employee 2045 and 8/2/05 for employee
3040 The other 1000 employees should not be listed. Thanks a lot for
your help.

"Ken Snell [MVP]" wrote in message
...
Let me see if I am understanding what you want to do.

Your Dates table contains all possible dates that can / should be the
dates on the time records for each employee.

Each employee (characterized by each unique EID value) has a start date
and an end date.

For each employee, the "gap" analysis should be based only on the date
range covered by the start date and the end date.

Is this what you're asking?
--

Ken Snell
MS ACCESS MVP



"Randy" wrote in message
...
The tables used in "QryAll" query are "EMP" and "Dates". The "qryGaps"
query is joined with "QryAll" query but also includes the table "IDRa".
In my "IDRb" table (Sub to my form IDR) which is related to "IDRa"
table via "IDRa_ID" primary key, I have a field of [CID] which is where
the code of "50" for "First Day" and "60" for "Last Day" is entered.
"50" or "60" is selected from the table "CommCode" field of [CID] via
combobox. [CID] corresponds with the field of [Desc]. Example: [CID]
of 50 = [Desc] First Day. Another example: The [Current_Date] of
8/15/05, [EID] 2045 or what ever employee I use, is entered in the
table "IDRa". The table "IDRb" is where I enter [CID] 50 or 60 and
other employee timesheet info. Does this help?..Thanks..Randy

"Ken Snell [MVP]" wrote in message
...
I am not understanding the first day and last day data in terms of how
it relates to the tables that we were using. Are you entering a value
of 50 into a field in a table? Which field and which table? How is that
table related to the employee table? Same questions for the 60 value.
How do these values of 50 and 60 relate to the [Enter Beginning Date]
and the [Enter Ending Date] parameters?

I need to have a better understanding of the data so that we can
identify how to properly select what you want.
--

Ken Snell
MS ACCESS MVP




"Randy" wrote in message
...
Ken you helped me last week with a query to find missing dates from
my employee db. It works great except I have found that it returns
all dates from my employee database which contains approx 1000
employees. This is a statewide employee table. I need it to return
only currently working employees (About 25). When I return an
employee from furlough or re-hire them I enter data of "First Day"
which has a coresponding code of 50 into my table of "IDRb" which is
a table used for the subform of my "IDR" form., for the first day
back to work and I enter "Last Day' which has a coresponding code of
60 for the last day worked for the season. The codes 50 and 60 are
selected from a table of "CommCode" by a combo box. This is my last
hurdle before I can distribute the db to other offices...Thank
you..Randy
qryAll query:
-------------
SELECT EMP.EID, Dates.D
FROM EMP, Dates
WHERE (((Dates.D) Between [Enter Beginning Date] And
[Enter Ending Date]));


qryGaps query:
---------------
SELECT Q.EID, Q.D
FROM qryAll AS Q LEFT JOIN IDRa AS W
ON (Q.EID = W.Eid) AND (Q.D = W.Current_Date)
WHERE (((Q.EID) Like 2045) AND ((W.Eid) Is Null))
ORDER BY Q.EID, Q.D;

--

Ken Snell
MS ACCESS MVP




"Randy" wrote in message
...


IDRa_ID Eid Current_Date District_Id TotalHours TotalMiles
TotalFees
tblMonth StateMiles StateLicense Expenses AccountID
865 2045 7/18/2005 764 8 48 $0.00 7 0 $0.00
866 2045 7/19/2005 764 8 142 $0.00 7 0 $0.00
867 2045 7/20/2005 764 8 38 $0.00 7 0 $0.00
869 2045 7/22/2005 764 8 0 $0.00 7 0 $0.00
870 2045 7/23/2005 764 0 0 $0.00 7 0 $0.00


D
2/18/2005
2/19/2005
2/20/2005
2/21/2005
2/22/2005
2/23/2005
2/24/2005
2/25/2005
2/26/2005
2/27/2005
2/28/2005


Emp_ID EID Ename Payroll HQ_Dist
1 2045 John Smith P 764


I hope this comes out ok..


"Ken Snell [MVP]" wrote in
message
...
No, what I'm asking is that you post the actual data from the
tables for
records that contain a value of 2045 for the EID and/or the value
of
2/21/05 for the Current_Dat. I need to see the actual data so that
I can
see how the query is selecting the records. As I noted, I expect
the
problem is because the query is not designed quite right to match
your
data, and seeing your actual data records will help me identify
what I
need to change in the query structure that I suggested.
--

Ken Snell
MS ACCESS MVP


"Randy" wrote in message
...
The query returns 11 times EID with data of 2045, and
Current_Date with
data of 2/21/05 which is the actual missing date. Thanks again.

"Ken Snell [MVP]" wrote in
message
...
Also show the data for the specific example that you say is
returning
11 records of the same employee/date combination. I need to see
the
data from all the tables that relate to that specific employee
and
date.

--

Ken Snell
MS ACCESS MVP

"Randy" wrote in message
...
Here are the three tables used. I really appreciate your
help..Randy
1st table: "IDRa" With primary key of [IDRa_ID] (AutoNumber)
and
[EID] (Number), [Current_Date] (Date/Time), [District_ID]
(Number),
[TotalHours] (Number), [TotalMiles] (Number), [TotalFees]
(Currency)
2nd table: "EMP" with primary key of [EMP_ID] (Autonumber),
[EID]
(Number), [EName] (Text), [Payroll] (Text), [HQ_Dist] (Number)
3rd table: "Dates" with one field [D] (Date/Time)


"Ken Snell [MVP]" wrote in
message
...
I'd need to see your data to provide a suggestion. My initial
thought is that we need one more join in the second query, but
I
don't know that until I see what your entire table structure
and
data values are. Can you post that information?
--

Ken Snell
MS ACCESS MVP


"Randy" wrote in message
...
I'm getting closer, I made a few changes to the sql. I now
get the
missing date, but the result is 11 records of the same
missing
date..Any thoughts..Thanks..Ken....Randy

SELECT [EMP].[EID], [Current_Date]
FROM EMP, IDRa
WHERE Current_Date Between [Enter Beginning Date] And [Enter
Ending
Date];

SELECT Q.EID, Q.Current_Date
FROM qryAll AS Q LEFT JOIN IDRa AS W ON (Q.EID = W.Eid) AND
(Q.Current_Date = W.Current_Date)
WHERE (((Q.EID) Like 2045) AND ((W.Eid) Is Null));

I was understanding that IDRa is where you keep the daily time records for
each employee for each day. If you want to find gaps in that table, you
cannot rely on a link to another table via this one to look up a match for
the employee. For example, suppose I am EID 1111. I started on August 24.
You ask the report to run and tell you gaps on August 25. I have no time
record for August 24. Therefore, IDRa will not have a record to link to my
record in IDRb. Therefore, your report would show that I have no missing
records.

Not knowing the purpose or structure of IDRb, I cannot say for certain if it
needs an EID field. However, if you want to use data in IDRb that is unique
for an EID, then the answer probably is Yes.

--

Ken Snell
MS ACCESS MVP

"Randy" wrote in message
...
Right now tbl "EID" is linked to "IDRa" vis [EID] Both have [EID]
(Employee Id #) tbl "IDRb" is linked to "IDRa" via [IDRa_ID] (Primary key
in IDRa and number in IDRb) tbl IDRa is in the middle of IDRb and EMP.
Do I need to add a field such as [EID] to tbl "IDRb"?..Randy
"Ken Snell [MVP]" wrote in message
...
If I'm understanding correctly, it woudl best if we modify the qryAll
query to do filter out the nonworking employees. However, to do that, I
need to know how we can relate EMP table with the IDRb table. The reason
for this is that we will "look up" an employee in the IDRb table to see
if he/she has the CID value of 50. I wouldn't modify qryGaps to use the
link of IDRa to IDRb because, if an employee doesn't have a record in
IDRa (meaning that there is a gap), you won't be able to link into IDRb
table to check the CID value.

If you can show me how we can related EMP and IDRb tables, we can make
this happen.
--

Ken Snell
MS ACCESS MVP




"Randy" wrote in message
news
Yes, but I need results for only employee's that are currently working.
Employees that are currently working have a data entry of "50" which
corresponds to "First Day" in the "IDRb" table. I need to see any
missing [Current_Date] up to now, with employees with "50" or "First
Day" . This would show me only employees with the code [CID] of "50"
with missing dates.

[EID] = 2045 [CID] = 50 [Desc] = First Day [Current_Date] =
8/1/05

8/2/05

8/4/05

[EID] = 3040 [CID] = 50 [Desc] = First Day [Current_Date] =
8/1/05

8/3/05

8/4/05

Thie missing date is 8/3/05 for employee 2045 and 8/2/05 for employee
3040 The other 1000 employees should not be listed. Thanks a lot for
your help.

"Ken Snell [MVP]" wrote in message
...
Let me see if I am understanding what you want to do.

Your Dates table contains all possible dates that can / should be the
dates on the time records for each employee.

Each employee (characterized by each unique EID value) has a start date
and an end date.

For each employee, the "gap" analysis should be based only on the date
range covered by the start date and the end date.

Is this what you're asking?
--

Ken Snell
MS ACCESS MVP



"Randy" wrote in message
...
The tables used in "QryAll" query are "EMP" and "Dates". The
"qryGaps" query is joined with "QryAll" query but also includes the
table "IDRa". In my "IDRb" table (Sub to my form IDR) which is related
to "IDRa" table via "IDRa_ID" primary key, I have a field of [CID]
which is where the code of "50" for "First Day" and "60" for "Last
Day" is entered. "50" or "60" is selected from the table "CommCode"
field of [CID] via combobox. [CID] corresponds with the field of
[Desc]. Example: [CID] of 50 = [Desc] First Day. Another example:
The [Current_Date] of 8/15/05, [EID] 2045 or what ever employee I use,
is entered in the table "IDRa". The table "IDRb" is where I enter
[CID] 50 or 60 and other employee timesheet info. Does this
help?..Thanks..Randy

"Ken Snell [MVP]" wrote in message
...
I am not understanding the first day and last day data in terms of how
it relates to the tables that we were using. Are you entering a value
of 50 into a field in a table? Which field and which table? How is
that table related to the employee table? Same questions for the 60
value. How do these values of 50 and 60 relate to the [Enter Beginning
Date] and the [Enter Ending Date] parameters?

I need to have a better understanding of the data so that we can
identify how to properly select what you want.
--

Ken Snell
MS ACCESS MVP




"Randy" wrote in message
...
Ken you helped me last week with a query to find missing dates from
my employee db. It works great except I have found that it returns
all dates from my employee database which contains approx 1000
employees. This is a statewide employee table. I need it to return
only currently working employees (About 25). When I return an
employee from furlough or re-hire them I enter data of "First Day"
which has a coresponding code of 50 into my table of "IDRb" which is
a table used for the subform of my "IDR" form., for the first day
back to work and I enter "Last Day' which has a coresponding code of
60 for the last day worked for the season. The codes 50 and 60 are
selected from a table of "CommCode" by a combo box. This is my last
hurdle before I can distribute the db to other offices...Thank
you..Randy
qryAll query:
-------------
SELECT EMP.EID, Dates.D
FROM EMP, Dates
WHERE (((Dates.D) Between [Enter Beginning Date] And
[Enter Ending Date]));


qryGaps query:
---------------
SELECT Q.EID, Q.D
FROM qryAll AS Q LEFT JOIN IDRa AS W
ON (Q.EID = W.Eid) AND (Q.D = W.Current_Date)
WHERE (((Q.EID) Like 2045) AND ((W.Eid) Is Null))
ORDER BY Q.EID, Q.D;

--

Ken Snell
MS ACCESS MVP




"Randy" wrote in message
...


IDRa_ID Eid Current_Date District_Id TotalHours TotalMiles
TotalFees
tblMonth StateMiles StateLicense Expenses AccountID
865 2045 7/18/2005 764 8 48 $0.00 7 0 $0.00
866 2045 7/19/2005 764 8 142 $0.00 7 0 $0.00
867 2045 7/20/2005 764 8 38 $0.00 7 0 $0.00
869 2045 7/22/2005 764 8 0 $0.00 7 0 $0.00
870 2045 7/23/2005 764 0 0 $0.00 7 0 $0.00


D
2/18/2005
2/19/2005
2/20/2005
2/21/2005
2/22/2005
2/23/2005
2/24/2005
2/25/2005
2/26/2005
2/27/2005
2/28/2005


Emp_ID EID Ename Payroll HQ_Dist
1 2045 John Smith P 764


I hope this comes out ok..


"Ken Snell [MVP]" wrote in
message
...
No, what I'm asking is that you post the actual data from the
tables for
records that contain a value of 2045 for the EID and/or the value
of
2/21/05 for the Current_Dat. I need to see the actual data so
that I can
see how the query is selecting the records. As I noted, I expect
the
problem is because the query is not designed quite right to match
your
data, and seeing your actual data records will help me identify
what I
need to change in the query structure that I suggested.
--

Ken Snell
MS ACCESS MVP


"Randy" wrote in message
...
The query returns 11 times EID with data of 2045, and
Current_Date with
data of 2/21/05 which is the actual missing date. Thanks again.

"Ken Snell [MVP]" wrote in
message
...
Also show the data for the specific example that you say is
returning
11 records of the same employee/date combination. I need to see
the
data from all the tables that relate to that specific employee
and
date.

--

Ken Snell
MS ACCESS MVP

"Randy" wrote in message
...
Here are the three tables used. I really appreciate your
help..Randy
1st table: "IDRa" With primary key of [IDRa_ID] (AutoNumber)
and
[EID] (Number), [Current_Date] (Date/Time), [District_ID]
(Number),
[TotalHours] (Number), [TotalMiles] (Number), [TotalFees]
(Currency)
2nd table: "EMP" with primary key of [EMP_ID] (Autonumber),
[EID]
(Number), [EName] (Text), [Payroll] (Text), [HQ_Dist] (Number)
3rd table: "Dates" with one field [D] (Date/Time)


"Ken Snell [MVP]" wrote in
message
...
I'd need to see your data to provide a suggestion. My initial
thought is that we need one more join in the second query,
but I
don't know that until I see what your entire table structure
and
data values are. Can you post that information?
--

Ken Snell
MS ACCESS MVP


"Randy" wrote in message
...
I'm getting closer, I made a few changes to the sql. I now
get the
missing date, but the result is 11 records of the same
missing
date..Any thoughts..Thanks..Ken....Randy

SELECT [EMP].[EID], [Current_Date]
FROM EMP, IDRa
WHERE Current_Date Between [Enter Beginning Date] And [Enter
Ending
Date];

SELECT Q.EID, Q.Current_Date
FROM qryAll AS Q LEFT JOIN IDRa AS W ON (Q.EID = W.Eid) AND
(Q.Current_Date = W.Current_Date)
WHERE (((Q.EID) Like 2045) AND ((W.Eid) Is Null));

tbl "IDRb" and "EMP" are now related through the field [EID] can this now
work?

"Ken Snell [MVP]" wrote in message
...
I was understanding that IDRa is where you keep the daily time records for
each employee for each day. If you want to find gaps in that table, you
cannot rely on a link to another table via this one to look up a match for
the employee. For example, suppose I am EID 1111. I started on August 24.
You ask the report to run and tell you gaps on August 25. I have no time
record for August 24. Therefore, IDRa will not have a record to link to my
record in IDRb. Therefore, your report would show that I have no missing
records.

Not knowing the purpose or structure of IDRb, I cannot say for certain if
it needs an EID field. However, if you want to use data in IDRb that is
unique for an EID, then the answer probably is Yes.

--

Ken Snell
MS ACCESS MVP

"Randy" wrote in message
...
Right now tbl "EID" is linked to "IDRa" vis [EID] Both have [EID]
(Employee Id #) tbl "IDRb" is linked to "IDRa" via [IDRa_ID] (Primary
key in IDRa and number in IDRb) tbl IDRa is in the middle of IDRb and
EMP. Do I need to add a field such as [EID] to tbl "IDRb"?..Randy
"Ken Snell [MVP]" wrote in message
...
If I'm understanding correctly, it woudl best if we modify the qryAll
query to do filter out the nonworking employees. However, to do that, I
need to know how we can relate EMP table with the IDRb table. The reason
for this is that we will "look up" an employee in the IDRb table to see
if he/she has the CID value of 50. I wouldn't modify qryGaps to use the
link of IDRa to IDRb because, if an employee doesn't have a record in
IDRa (meaning that there is a gap), you won't be able to link into IDRb
table to check the CID value.

If you can show me how we can related EMP and IDRb tables, we can make
this happen.
--

Ken Snell
MS ACCESS MVP




"Randy" wrote in message
news Yes, but I need results for only employee's that are currently working.
Employees that are currently working have a data entry of "50" which
corresponds to "First Day" in the "IDRb" table. I need to see any
missing [Current_Date] up to now, with employees with "50" or "First
Day" . This would show me only employees with the code [CID] of "50"
with missing dates.

[EID] = 2045 [CID] = 50 [Desc] = First Day [Current_Date] =
8/1/05

8/2/05

8/4/05

[EID] = 3040 [CID] = 50 [Desc] = First Day [Current_Date] =
8/1/05

8/3/05

8/4/05

Thie missing date is 8/3/05 for employee 2045 and 8/2/05 for employee
3040 The other 1000 employees should not be listed. Thanks a lot for
your help.

"Ken Snell [MVP]" wrote in message
...
Let me see if I am understanding what you want to do.

Your Dates table contains all possible dates that can / should be the
dates on the time records for each employee.

Each employee (characterized by each unique EID value) has a start
date and an end date.

For each employee, the "gap" analysis should be based only on the date
range covered by the start date and the end date.

Is this what you're asking?
--

Ken Snell
MS ACCESS MVP



"Randy" wrote in message
...
The tables used in "QryAll" query are "EMP" and "Dates". The
"qryGaps" query is joined with "QryAll" query but also includes the
table "IDRa". In my "IDRb" table (Sub to my form IDR) which is
related to "IDRa" table via "IDRa_ID" primary key, I have a field of
[CID] which is where the code of "50" for "First Day" and "60" for
"Last Day" is entered. "50" or "60" is selected from the table
"CommCode" field of [CID] via combobox. [CID] corresponds with the
field of [Desc]. Example: [CID] of 50 = [Desc] First Day. Another
example: The [Current_Date] of 8/15/05, [EID] 2045 or what ever
employee I use, is entered in the table "IDRa". The table "IDRb" is
where I enter [CID] 50 or 60 and other employee timesheet info. Does
this help?..Thanks..Randy

"Ken Snell [MVP]" wrote in message
...
I am not understanding the first day and last day data in terms of
how it relates to the tables that we were using. Are you entering a
value of 50 into a field in a table? Which field and which table? How
is that table related to the employee table? Same questions for the
60 value. How do these values of 50 and 60 relate to the [Enter
Beginning Date] and the [Enter Ending Date] parameters?

I need to have a better understanding of the data so that we can
identify how to properly select what you want.
--

Ken Snell
MS ACCESS MVP




"Randy" wrote in message
...
Ken you helped me last week with a query to find missing dates from
my employee db. It works great except I have found that it returns
all dates from my employee database which contains approx 1000
employees. This is a statewide employee table. I need it to return
only currently working employees (About 25). When I return an
employee from furlough or re-hire them I enter data of "First Day"
which has a coresponding code of 50 into my table of "IDRb" which
is a table used for the subform of my "IDR" form., for the first
day back to work and I enter "Last Day' which has a coresponding
code of 60 for the last day worked for the season. The codes 50 and
60 are selected from a table of "CommCode" by a combo box. This is
my last hurdle before I can distribute the db to other
offices...Thank you..Randy
qryAll query:
-------------
SELECT EMP.EID, Dates.D
FROM EMP, Dates
WHERE (((Dates.D) Between [Enter Beginning Date] And
[Enter Ending Date]));


qryGaps query:
---------------
SELECT Q.EID, Q.D
FROM qryAll AS Q LEFT JOIN IDRa AS W
ON (Q.EID = W.Eid) AND (Q.D = W.Current_Date)
WHERE (((Q.EID) Like 2045) AND ((W.Eid) Is Null))
ORDER BY Q.EID, Q.D;

--

Ken Snell
MS ACCESS MVP




"Randy" wrote in message
...


IDRa_ID Eid Current_Date District_Id TotalHours TotalMiles
TotalFees
tblMonth StateMiles StateLicense Expenses AccountID
865 2045 7/18/2005 764 8 48 $0.00 7 0 $0.00
866 2045 7/19/2005 764 8 142 $0.00 7 0 $0.00
867 2045 7/20/2005 764 8 38 $0.00 7 0 $0.00
869 2045 7/22/2005 764 8 0 $0.00 7 0 $0.00
870 2045 7/23/2005 764 0 0 $0.00 7 0 $0.00


D
2/18/2005
2/19/2005
2/20/2005
2/21/2005
2/22/2005
2/23/2005
2/24/2005
2/25/2005
2/26/2005
2/27/2005
2/28/2005


Emp_ID EID Ename Payroll HQ_Dist
1 2045 John Smith P 764


I hope this comes out ok..


"Ken Snell [MVP]" wrote in
message
...
No, what I'm asking is that you post the actual data from the
tables for
records that contain a value of 2045 for the EID and/or the
value of
2/21/05 for the Current_Dat. I need to see the actual data so
that I can
see how the query is selecting the records. As I noted, I expect
the
problem is because the query is not designed quite right to
match your
data, and seeing your actual data records will help me identify
what I
need to change in the query structure that I suggested.
--

Ken Snell
MS ACCESS MVP


"Randy" wrote in message
...
The query returns 11 times EID with data of 2045, and
Current_Date with
data of 2/21/05 which is the actual missing date. Thanks
again.

"Ken Snell [MVP]" wrote in
message
...
Also show the data for the specific example that you say is
returning
11 records of the same employee/date combination. I need to
see the
data from all the tables that relate to that specific employee
and
date.

--

Ken Snell
MS ACCESS MVP

"Randy" wrote in message
...
Here are the three tables used. I really appreciate your
help..Randy
1st table: "IDRa" With primary key of [IDRa_ID]
(AutoNumber) and
[EID] (Number), [Current_Date] (Date/Time), [District_ID]
(Number),
[TotalHours] (Number), [TotalMiles] (Number), [TotalFees]
(Currency)
2nd table: "EMP" with primary key of [EMP_ID] (Autonumber),
[EID]
(Number), [EName] (Text), [Payroll] (Text), [HQ_Dist]
(Number)
3rd table: "Dates" with one field [D] (Date/Time)


"Ken Snell [MVP]" wrote in
message
...
I'd need to see your data to provide a suggestion. My
initial
thought is that we need one more join in the second query,
but I
don't know that until I see what your entire table structure
and
data values are. Can you post that information?
--

Ken Snell
MS ACCESS MVP


"Randy" wrote in message
...
I'm getting closer, I made a few changes to the sql. I now
get the
missing date, but the result is 11 records of the same
missing
date..Any thoughts..Thanks..Ken....Randy

SELECT [EMP].[EID], [Current_Date]
FROM EMP, IDRa
WHERE Current_Date Between [Enter Beginning Date] And
[Enter Ending
Date];

SELECT Q.EID, Q.Current_Date
FROM qryAll AS Q LEFT JOIN IDRa AS W ON (Q.EID = W.Eid) AND
(Q.Current_Date = W.Current_Date)
WHERE (((Q.EID) Like 2045) AND ((W.Eid) Is Null));

I apologize for asking more questions, but I don't know the structure of
your IDRb table. What are its fields? How many records will it contain for
each value of EID? Is there a record where CID=50? and possibly a second
record where CID=60?

I should be able to suggest the change to qryAll with these answers.
--

Ken Snell
MS ACCESS MVP




"Randy" wrote in message
...
tbl "IDRb" and "EMP" are now related through the field [EID] can this now
work?

"Ken Snell [MVP]" wrote in message
...
I was understanding that IDRa is where you keep the daily time records for
each employee for each day. If you want to find gaps in that table, you
cannot rely on a link to another table via this one to look up a match for
the employee. For example, suppose I am EID 1111. I started on August 24.
You ask the report to run and tell you gaps on August 25. I have no time
record for August 24. Therefore, IDRa will not have a record to link to my
record in IDRb. Therefore, your report would show that I have no missing
records.

Not knowing the purpose or structure of IDRb, I cannot say for certain if
it needs an EID field. However, if you want to use data in IDRb that is
unique for an EID, then the answer probably is Yes.

--

Ken Snell
MS ACCESS MVP

"Randy" wrote in message
...
Right now tbl "EID" is linked to "IDRa" vis [EID] Both have [EID]
(Employee Id #) tbl "IDRb" is linked to "IDRa" via [IDRa_ID] (Primary
key in IDRa and number in IDRb) tbl IDRa is in the middle of IDRb and
EMP. Do I need to add a field such as [EID] to tbl "IDRb"?..Randy
"Ken Snell [MVP]" wrote in message
...
If I'm understanding correctly, it woudl best if we modify the qryAll
query to do filter out the nonworking employees. However, to do that, I
need to know how we can relate EMP table with the IDRb table. The
reason for this is that we will "look up" an employee in the IDRb table
to see if he/she has the CID value of 50. I wouldn't modify qryGaps to
use the link of IDRa to IDRb because, if an employee doesn't have a
record in IDRa (meaning that there is a gap), you won't be able to link
into IDRb table to check the CID value.

If you can show me how we can related EMP and IDRb tables, we can make
this happen.
--

Ken Snell
MS ACCESS MVP




"Randy" wrote in message
news Yes, but I need results for only employee's that are currently
working. Employees that are currently working have a data entry of
"50" which corresponds to "First Day" in the "IDRb" table. I need to
see any missing [Current_Date] up to now, with employees with "50" or
"First Day" . This would show me only employees with the code [CID]
of "50" with missing dates.

[EID] = 2045 [CID] = 50 [Desc] = First Day [Current_Date] =
8/1/05

8/2/05

8/4/05

[EID] = 3040 [CID] = 50 [Desc] = First Day [Current_Date] =
8/1/05

8/3/05

8/4/05

Thie missing date is 8/3/05 for employee 2045 and 8/2/05 for employee
3040 The other 1000 employees should not be listed. Thanks a lot for
your help.

"Ken Snell [MVP]" wrote in message
...
Let me see if I am understanding what you want to do.

Your Dates table contains all possible dates that can / should be the
dates on the time records for each employee.

Each employee (characterized by each unique EID value) has a start
date and an end date.

For each employee, the "gap" analysis should be based only on the
date range covered by the start date and the end date.

Is this what you're asking?
--

Ken Snell
MS ACCESS MVP



"Randy" wrote in message
...
The tables used in "QryAll" query are "EMP" and "Dates". The
"qryGaps" query is joined with "QryAll" query but also includes the
table "IDRa". In my "IDRb" table (Sub to my form IDR) which is
related to "IDRa" table via "IDRa_ID" primary key, I have a field of
[CID] which is where the code of "50" for "First Day" and "60" for
"Last Day" is entered. "50" or "60" is selected from the table
"CommCode" field of [CID] via combobox. [CID] corresponds with the
field of [Desc]. Example: [CID] of 50 = [Desc] First Day.
Another example: The [Current_Date] of 8/15/05, [EID] 2045 or what
ever employee I use, is entered in the table "IDRa". The table
"IDRb" is where I enter [CID] 50 or 60 and other employee timesheet
info. Does this help?..Thanks..Randy

"Ken Snell [MVP]" wrote in
message ...
I am not understanding the first day and last day data in terms of
how it relates to the tables that we were using. Are you entering a
value of 50 into a field in a table? Which field and which table?
How is that table related to the employee table? Same questions for
the 60 value. How do these values of 50 and 60 relate to the [Enter
Beginning Date] and the [Enter Ending Date] parameters?

I need to have a better understanding of the data so that we can
identify how to properly select what you want.
--

Ken Snell
MS ACCESS MVP




"Randy" wrote in message
...
Ken you helped me last week with a query to find missing dates
from my employee db. It works great except I have found that it
returns all dates from my employee database which contains approx
1000 employees. This is a statewide employee table. I need it to
return only currently working employees (About 25). When I return
an employee from furlough or re-hire them I enter data of "First
Day" which has a coresponding code of 50 into my table of "IDRb"
which is a table used for the subform of my "IDR" form., for the
first day back to work and I enter "Last Day' which has a
coresponding code of 60 for the last day worked for the season.
The codes 50 and 60 are selected from a table of "CommCode" by a
combo box. This is my last hurdle before I can distribute the db
to other offices...Thank you..Randy
qryAll query:
-------------
SELECT EMP.EID, Dates.D
FROM EMP, Dates
WHERE (((Dates.D) Between [Enter Beginning Date] And
[Enter Ending Date]));


qryGaps query:
---------------
SELECT Q.EID, Q.D
FROM qryAll AS Q LEFT JOIN IDRa AS W
ON (Q.EID = W.Eid) AND (Q.D = W.Current_Date)
WHERE (((Q.EID) Like 2045) AND ((W.Eid) Is Null))
ORDER BY Q.EID, Q.D;

--

Ken Snell
MS ACCESS MVP




"Randy" wrote in message
...


IDRa_ID Eid Current_Date District_Id TotalHours TotalMiles
TotalFees
tblMonth StateMiles StateLicense Expenses AccountID
865 2045 7/18/2005 764 8 48 $0.00 7 0 $0.00
866 2045 7/19/2005 764 8 142 $0.00 7 0 $0.00
867 2045 7/20/2005 764 8 38 $0.00 7 0 $0.00
869 2045 7/22/2005 764 8 0 $0.00 7 0 $0.00
870 2045 7/23/2005 764 0 0 $0.00 7 0 $0.00


D
2/18/2005
2/19/2005
2/20/2005
2/21/2005
2/22/2005
2/23/2005
2/24/2005
2/25/2005
2/26/2005
2/27/2005
2/28/2005


Emp_ID EID Ename Payroll HQ_Dist
1 2045 John Smith P 764


I hope this comes out ok..


"Ken Snell [MVP]" wrote in
message
...
No, what I'm asking is that you post the actual data from the
tables for
records that contain a value of 2045 for the EID and/or the
value of
2/21/05 for the Current_Dat. I need to see the actual data so
that I can
see how the query is selecting the records. As I noted, I
expect the
problem is because the query is not designed quite right to
match your
data, and seeing your actual data records will help me identify
what I
need to change in the query structure that I suggested.
--

Ken Snell
MS ACCESS MVP


"Randy" wrote in message
...
The query returns 11 times EID with data of 2045, and
Current_Date with
data of 2/21/05 which is the actual missing date. Thanks
again.

"Ken Snell [MVP]" wrote in
message
...
Also show the data for the specific example that you say is
returning
11 records of the same employee/date combination. I need to
see the
data from all the tables that relate to that specific
employee and
date.

--

Ken Snell
MS ACCESS MVP

"Randy" wrote in message
...
Here are the three tables used. I really appreciate your
help..Randy
1st table: "IDRa" With primary key of [IDRa_ID]
(AutoNumber) and
[EID] (Number), [Current_Date] (Date/Time), [District_ID]
(Number),
[TotalHours] (Number), [TotalMiles] (Number), [TotalFees]
(Currency)
2nd table: "EMP" with primary key of [EMP_ID] (Autonumber),
[EID]
(Number), [EName] (Text), [Payroll] (Text), [HQ_Dist]
(Number)
3rd table: "Dates" with one field [D] (Date/Time)


"Ken Snell [MVP]" wrote
in message
...
I'd need to see your data to provide a suggestion. My
initial
thought is that we need one more join in the second query,
but I
don't know that until I see what your entire table
structure and
data values are. Can you post that information?
--

Ken Snell
MS ACCESS MVP


"Randy" wrote in message
...
I'm getting closer, I made a few changes to the sql. I
now get the
missing date, but the result is 11 records of the same
missing
date..Any thoughts..Thanks..Ken....Randy

SELECT [EMP].[EID], [Current_Date]
FROM EMP, IDRa
WHERE Current_Date Between [Enter Beginning Date] And
[Enter Ending
Date];

SELECT Q.EID, Q.Current_Date
FROM qryAll AS Q LEFT JOIN IDRa AS W ON (Q.EID = W.Eid)
AND
(Q.Current_Date = W.Current_Date)
WHERE (((Q.EID) Like 2045) AND ((W.Eid) Is Null));