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
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>
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.
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>
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
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>
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>
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..
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>
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.
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>
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)
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>
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));
