Help again from Ken Snell (Query)

Help again from Ken Snell (Query)

Post by Rand » Wed, 24 Aug 2005 08:55:39


en 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



 
 
 

Help again from Ken Snell (Query)

Post by Ken Snell » Wed, 24 Aug 2005 14:42:15

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" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...



 
 
 

Help again from Ken Snell (Query)

Post by Rand » Wed, 24 Aug 2005 22:32:38

he 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]" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

Help again from Ken Snell (Query)

Post by Ken Snell » Thu, 25 Aug 2005 22:52:06

et 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" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

Help again from Ken Snell (Query)

Post by Rand » Fri, 26 Aug 2005 06:06:21

es, 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]" < XXXX@XXXXX.COM > wrote in message
news:% XXXX@XXXXX.COM ...


 
 
 

Help again from Ken Snell (Query)

Post by Ken Snell » Fri, 26 Aug 2005 22:22:31

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>
 
 
 

Help again from Ken Snell (Query)

Post by Ken Snell » Sat, 27 Aug 2005 07:00:55

f 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" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

Help again from Ken Snell (Query)

Post by Rand » Sat, 27 Aug 2005 07:41:32

ight 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]" < XXXX@XXXXX.COM > wrote in message
news:% XXXX@XXXXX.COM ...


 
 
 

Help again from Ken Snell (Query)

Post by Ken Snell » Sat, 27 Aug 2005 08:32:12

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" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...