Help again from Ken Snell (Query)

Wow! I finally got the result I need. Thank you very much. The only thing
is when I run the qryGaps query, I am prompted for "Enter beginning date",
"Enter Ending Date" and "Enter Starting Date". Isn't the beginning date and
the starting date the same?...Randy
"Ken Snell [MVP]" wrote in message
...
Yes, you are seeing the solution clearly. And you could enter those dates
via a popup form if you'd like. Just keep in mind that the whole purpose
of this discussion was to find "missing records" in the IDRa table, so you
may want to do the "First Date" and "Last Date" entry separate from the
IDRa data entry.

If you add these fields, then you can modify the qryAll query to do the
desired filtering:

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 Starting Date]) AND
EMP.[FirstDay]=[Enter Ending Date];


--

Ken Snell
MS ACCESS MVP

"Randy" wrote in message
...
I apologize for my lack of access knowledge, but I'm trying to learn. I
think you are suggesting I use the EMP table and add two fields of
[FirstDay] and [LastDay]. When I have an employee whose first day is
9/1/05, I would enter via form with the control source of EMP the date of
9/1/05 for the First Day. Maybe I could have a cmd button on my IDR form
to open lets say another form to enter the First day or the last day.
Would this work. Thanks for your help..Randy
"Ken Snell [MVP]" wrote in message
...
"Use" -- as in let the user change the value of those checkboxes... or
"use" -- as in display whether that date is a "first" date as info.

If you're willing to let the "first date" entry be done via the EMP
table, you'll go much farther with what you want. If you want to use
IDRa as the data storage, and then copy data to EMP, the initial
scenario that I'd raised is still a problem -- if you don't enter that
"first day" record into IDRa table, you cannot use that data to copy to
EMP....

The answer to your data scenario is to use EMP table to hold the "first
date" date and the "last date" date.... then you can use those date
values to show a checked/unchecked checkbox in the IDRa form, and you
can use those date values to filter the employees in the queries looking
for gaps.


--

Ken Snell
MS ACCESS MVP


"Randy" wrote in message
...
My intention is to use my form "IDR" to use the checkboxes First Day
and Last Day...Thank you
"Ken Snell [MVP]" wrote in message
...
Adding the fields to the EMP table will be a big help. I will be away
from PC most of today and won't get chance to write a detailed
response till Monday (about 28 - 30 hours from now), but what I'm
considering is to not use those fields in IDRa at all for "check if
it's first day", etc., but rather to change your query that supplies
the form on which you show shuch info so that it would use the EMP
table to do a "lookup" or a "read" and set the value of a checkbox
accordingly....unless, that is, your form is used to set the checkbox
value to yes or no?

--

Ken Snell
MS ACCESS MVP

"Randy" wrote in message
...
No, I couldn't guarentee that the first day would never be missing.
What if I added Start Date and an End Date fields to my EMP table.
Could I still use check boxes for First Day and Last Day. I dont want
to have three date fields on my form. I was thinking if I check a
box that is for First Day and then have some code or something that
will insert the [Current_date] tbl "IDRa" into the field [Start Date]
that way when ever i check the box it is entered into the EMP table.
Is this possible?..Randy
"Ken Snell [MVP]" wrote in message
...
Again, this will work only if you can be 100% positive that the
"first day" record will never be one of the "gaps" (missing records)
in IDRa table.... can you guarantee that?

--

Ken Snell
MS ACCESS MVP

"Randy" wrote in message
...
I have an idea. What if I add two checkbox fields to IDRa table.
One checkbox for First Day and one for Last Day. If it is the
employees first day back to work I check the First day checkbox and
I would check the checkbox Last Day if it is their last day.etc.
This way I could totally eliminate the table IDRb in the query. The
[Current_date] would still be entered in IDRa...Thanks Randy
"Ken Snell [MVP]" wrote in
message ...
Ah, this helps immensely... but I don't see EID in IDRb table?

However, even with EID in IDRb table, now that I see your
structure, we won't be able to do what you want using this
structure. As I noted before, unless there is a record in IDRa
that then is related to the IDRb record with the CID value of 50,
we will not be able to identify which employees are "active" and
which are not. If you can be absolutely positive that one of the
"missing" IDRa records would never be the one that is the parent
of the CID=50 record in IDRb, then we can come up with a
workaround that you could use -- but if the IDRa record is
missing, the workaround will not show you the "missing" dates for
that particular employee.

What would be best for your desired querying would be to add two
fields to your EMP table: DateStart and DateEnd. Use those two
fields to put in the "starting" date for the EID person, and the
"ending" date. Then we can easily do what you seek because we can
include those fields in the criterion for qryAll.

Any chance of changing your EMP table structure and populating it
with data from your forms in the future?
--

Ken Snell
MS ACCESS MVP



"Randy" wrote in message
...
Here is a copy of my IDRa table
IDRa_ID Eid Current_Date District_Id TotalHours
790 2045 7/28/2005 764 8.00
865 2045 7/15/2005 764 8.00
866 2045 7/19/2005 764 8.00
867 2045 7/20/2005 764 8.00
868 2045 7/21/2005 764 8.00
869 2045 7/22/2005 764 8.00


IDrb Table IDRbID IDRa_ID Cid App_Id Station_Id TimeBase Hours
Miles Fees
588 1114 60 0 0 08 0.00 0 $0.00
154 865 50 8229 2 02 8.00 48 $0.00
334 970 217 508 0 02 1.50 45 $93.20
18 790 217 2755 0 02 2.00 30 $79.00


EMP table EID Ename Payroll HQ_Dist
1005 Edwards, Cathrine S 762
1006 Huereca, Beatriz S 762
1007 Deanda Christine S 758
1011 Reeve, Mary S 756
1012 Kirchoff, Kim Renee S 791
1014 Dillard, Linda S 762
1015 Mooney Cecil S 796
1016 Baker, Hope S 762


"Ken Snell [MVP]" wrote in
message +
...
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));

My error:

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];

Glad it's working. This is a good "object lesson" on the importance of
designing your tables so that you will be able to query the data to get the
desired information. I hope my "obstinance" wasn't a "put-off" for you g
.

--

Ken Snell
MS ACCESS MVP



"Randy" wrote in message
...
Wow! I finally got the result I need. Thank you very much. The only
thing is when I run the qryGaps query, I am prompted for "Enter beginning
date", "Enter Ending Date" and "Enter Starting Date". Isn't the beginning
date and the starting date the same?...Randy
"Ken Snell [MVP]" wrote in message
...
Yes, you are seeing the solution clearly. And you could enter those dates
via a popup form if you'd like. Just keep in mind that the whole purpose
of this discussion was to find "missing records" in the IDRa table, so
you may want to do the "First Date" and "Last Date" entry separate from
the IDRa data entry.

If you add these fields, then you can modify the qryAll query to do the
desired filtering:

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 Starting Date]) AND
EMP.[FirstDay]=[Enter Ending Date];


--

Ken Snell
MS ACCESS MVP

"Randy" wrote in message
...
I apologize for my lack of access knowledge, but I'm trying to learn. I
think you are suggesting I use the EMP table and add two fields of
[FirstDay] and [LastDay]. When I have an employee whose first day is
9/1/05, I would enter via form with the control source of EMP the date of
9/1/05 for the First Day. Maybe I could have a cmd button on my IDR form
to open lets say another form to enter the First day or the last day.
Would this work. Thanks for your help..Randy
"Ken Snell [MVP]" wrote in message
...
"Use" -- as in let the user change the value of those checkboxes... or
"use" -- as in display whether that date is a "first" date as info.

If you're willing to let the "first date" entry be done via the EMP
table, you'll go much farther with what you want. If you want to use
IDRa as the data storage, and then copy data to EMP, the initial
scenario that I'd raised is still a problem -- if you don't enter that
"first day" record into IDRa table, you cannot use that data to copy to
EMP....

The answer to your data scenario is to use EMP table to hold the "first
date" date and the "last date" date.... then you can use those date
values to show a checked/unchecked checkbox in the IDRa form, and you
can use those date values to filter the employees in the queries
looking for gaps.


--

Ken Snell
MS ACCESS MVP


"Randy" wrote in message
...
My intention is to use my form "IDR" to use the checkboxes First Day
and Last Day...Thank you
"Ken Snell [MVP]" wrote in message
...
Adding the fields to the EMP table will be a big help. I will be away
from PC most of today and won't get chance to write a detailed
response till Monday (about 28 - 30 hours from now), but what I'm
considering is to not use those fields in IDRa at all for "check if
it's first day", etc., but rather to change your query that supplies
the form on which you show shuch info so that it would use the EMP
table to do a "lookup" or a "read" and set the value of a checkbox
accordingly....unless, that is, your form is used to set the checkbox
value to yes or no?

--

Ken Snell
MS ACCESS MVP

"Randy" wrote in message
...
No, I couldn't guarentee that the first day would never be missing.
What if I added Start Date and an End Date fields to my EMP table.
Could I still use check boxes for First Day and Last Day. I dont
want to have three date fields on my form. I was thinking if I
check a box that is for First Day and then have some code or
something that will insert the [Current_date] tbl "IDRa" into the
field [Start Date] that way when ever i check the box it is entered
into the EMP table. Is this possible?..Randy
"Ken Snell [MVP]" wrote in
message ...
Again, this will work only if you can be 100% positive that the
"first day" record will never be one of the "gaps" (missing
records) in IDRa table.... can you guarantee that?

--

Ken Snell
MS ACCESS MVP

"Randy" wrote in message
...
I have an idea. What if I add two checkbox fields to IDRa table.
One checkbox for First Day and one for Last Day. If it is the
employees first day back to work I check the First day checkbox and
I would check the checkbox Last Day if it is their last day.etc.
This way I could totally eliminate the table IDRb in the query.
The [Current_date] would still be entered in IDRa...Thanks Randy
"Ken Snell [MVP]" wrote in
message ...
Ah, this helps immensely... but I don't see EID in IDRb table?

However, even with EID in IDRb table, now that I see your
structure, we won't be able to do what you want using this
structure. As I noted before, unless there is a record in IDRa
that then is related to the IDRb record with the CID value of 50,
we will not be able to identify which employees are "active" and
which are not. If you can be absolutely positive that one of the
"missing" IDRa records would never be the one that is the parent
of the CID=50 record in IDRb, then we can come up with a
workaround that you could use -- but if the IDRa record is
missing, the workaround will not show you the "missing" dates for
that particular employee.

What would be best for your desired querying would be to add two
fields to your EMP table: DateStart and DateEnd. Use those two
fields to put in the "starting" date for the EID person, and the
"ending" date. Then we can easily do what you seek because we can
include those fields in the criterion for qryAll.

Any chance of changing your EMP table structure and populating it
with data from your forms in the future?
--

Ken Snell
MS ACCESS MVP



"Randy" wrote in message
...
Here is a copy of my IDRa table
IDRa_ID Eid Current_Date District_Id TotalHours
790 2045 7/28/2005 764 8.00
865 2045 7/15/2005 764 8.00
866 2045 7/19/2005 764 8.00
867 2045 7/20/2005 764 8.00
868 2045 7/21/2005 764 8.00
869 2045 7/22/2005 764 8.00


IDrb Table IDRbID IDRa_ID Cid App_Id Station_Id TimeBase Hours
Miles Fees
588 1114 60 0 0 08 0.00 0 $0.00
154 865 50 8229 2 02 8.00 48 $0.00
334 970 217 508 0 02 1.50 45 $93.20
18 790 217 2755 0 02 2.00 30 $79.00


EMP table EID Ename Payroll HQ_Dist
1005 Edwards, Cathrine S 762
1006 Huereca, Beatriz S 762
1007 Deanda Christine S 758
1011 Reeve, Mary S 756
1012 Kirchoff, Kim Renee S 791
1014 Dillard, Linda S 762
1015 Mooney Cecil S 796
1016 Baker, Hope S 762


"Ken Snell [MVP]" wrote in
message +
...
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));

That was it Ken. Your help has saved me weeks of frustration. Thanks for
hanging in there...Randy
"Ken Snell [MVP]" wrote in message
...
My error:

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];

Glad it's working. This is a good "object lesson" on the importance of
designing your tables so that you will be able to query the data to get
the desired information. I hope my "obstinance" wasn't a "put-off" for you
g
.

--

Ken Snell
MS ACCESS MVP



"Randy" wrote in message
...
Wow! I finally got the result I need. Thank you very much. The only
thing is when I run the qryGaps query, I am prompted for "Enter beginning
date", "Enter Ending Date" and "Enter Starting Date". Isn't the
beginning date and the starting date the same?...Randy
"Ken Snell [MVP]" wrote in message
...
Yes, you are seeing the solution clearly. And you could enter those
dates via a popup form if you'd like. Just keep in mind that the whole
purpose of this discussion was to find "missing records" in the IDRa
table, so you may want to do the "First Date" and "Last Date" entry
separate from the IDRa data entry.

If you add these fields, then you can modify the qryAll query to do the
desired filtering:

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 Starting Date]) AND
EMP.[FirstDay]=[Enter Ending Date];


--

Ken Snell
MS ACCESS MVP

"Randy" wrote in message
...
I apologize for my lack of access knowledge, but I'm trying to learn. I
think you are suggesting I use the EMP table and add two fields of
[FirstDay] and [LastDay]. When I have an employee whose first day is
9/1/05, I would enter via form with the control source of EMP the date
of 9/1/05 for the First Day. Maybe I could have a cmd button on my IDR
form to open lets say another form to enter the First day or the last
day. Would this work. Thanks for your help..Randy
"Ken Snell [MVP]" wrote in message
...
"Use" -- as in let the user change the value of those checkboxes... or
"use" -- as in display whether that date is a "first" date as info.

If you're willing to let the "first date" entry be done via the EMP
table, you'll go much farther with what you want. If you want to use
IDRa as the data storage, and then copy data to EMP, the initial
scenario that I'd raised is still a problem -- if you don't enter that
"first day" record into IDRa table, you cannot use that data to copy
to EMP....

The answer to your data scenario is to use EMP table to hold the
"first date" date and the "last date" date.... then you can use those
date values to show a checked/unchecked checkbox in the IDRa form, and
you can use those date values to filter the employees in the queries
looking for gaps.


--

Ken Snell
MS ACCESS MVP


"Randy" wrote in message
...
My intention is to use my form "IDR" to use the checkboxes First Day
and Last Day...Thank you
"Ken Snell [MVP]" wrote in message
...
Adding the fields to the EMP table will be a big help. I will be
away from PC most of today and won't get chance to write a detailed
response till Monday (about 28 - 30 hours from now), but what I'm
considering is to not use those fields in IDRa at all for "check if
it's first day", etc., but rather to change your query that supplies
the form on which you show shuch info so that it would use the EMP
table to do a "lookup" or a "read" and set the value of a checkbox
accordingly....unless, that is, your form is used to set the
checkbox value to yes or no?

--

Ken Snell
MS ACCESS MVP

"Randy" wrote in message
...
No, I couldn't guarentee that the first day would never be missing.
What if I added Start Date and an End Date fields to my EMP table.
Could I still use check boxes for First Day and Last Day. I dont
want to have three date fields on my form. I was thinking if I
check a box that is for First Day and then have some code or
something that will insert the [Current_date] tbl "IDRa" into the
field [Start Date] that way when ever i check the box it is entered
into the EMP table. Is this possible?..Randy
"Ken Snell [MVP]" wrote in
message ...
Again, this will work only if you can be 100% positive that the
"first day" record will never be one of the "gaps" (missing
records) in IDRa table.... can you guarantee that?

--

Ken Snell
MS ACCESS MVP

"Randy" wrote in message
...
I have an idea. What if I add two checkbox fields to IDRa table.
One checkbox for First Day and one for Last Day. If it is the
employees first day back to work I check the First day checkbox
and I would check the checkbox Last Day if it is their last
day.etc. This way I could totally eliminate the table IDRb in the
query. The [Current_date] would still be entered in IDRa...Thanks
Randy
"Ken Snell [MVP]" wrote in
message ...
Ah, this helps immensely... but I don't see EID in IDRb table?

However, even with EID in IDRb table, now that I see your
structure, we won't be able to do what you want using this
structure. As I noted before, unless there is a record in IDRa
that then is related to the IDRb record with the CID value of
50, we will not be able to identify which employees are "active"
and which are not. If you can be absolutely positive that one of
the "missing" IDRa records would never be the one that is the
parent of the CID=50 record in IDRb, then we can come up with a
workaround that you could use -- but if the IDRa record is
missing, the workaround will not show you the "missing" dates
for that particular employee.

What would be best for your desired querying would be to add two
fields to your EMP table: DateStart and DateEnd. Use those two
fields to put in the "starting" date for the EID person, and the
"ending" date. Then we can easily do what you seek because we
can include those fields in the criterion for qryAll.

Any chance of changing your EMP table structure and populating
it with data from your forms in the future?
--

Ken Snell
MS ACCESS MVP



"Randy" wrote in message
...
Here is a copy of my IDRa table
IDRa_ID Eid Current_Date District_Id TotalHours
790 2045 7/28/2005 764 8.00
865 2045 7/15/2005 764 8.00
866 2045 7/19/2005 764 8.00
867 2045 7/20/2005 764 8.00
868 2045 7/21/2005 764 8.00
869 2045 7/22/2005 764 8.00


IDrb Table IDRbID IDRa_ID Cid App_Id Station_Id TimeBase Hours
Miles Fees
588 1114 60 0 0 08 0.00 0 $0.00
154 865 50 8229 2 02 8.00 48 $0.00
334 970 217 508 0 02 1.50 45 $93.20
18 790 217 2755 0 02 2.00 30 $79.00


EMP table EID Ename Payroll HQ_Dist
1005 Edwards, Cathrine S 762
1006 Huereca, Beatriz S 762
1007 Deanda Christine S 758
1011 Reeve, Mary S 756
1012 Kirchoff, Kim Renee S 791
1014 Dillard, Linda S 762
1015 Mooney Cecil S 796
1016 Baker, Hope S 762


"Ken Snell [MVP]" wrote in
message +
...
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));