Using Between in queries

[Randy]

Access 2000 I have an employee db where I enter employee timesheet info. I
have a tbl of "IDRa" where employee time info is entered and another tbl of
"CommCode" which stores codes for our office use. Two codes I use in
"CommCode" are 50 which is for [First Day] worked and 60 which is for [Last
Day] worked. I need to query only the employees [EID] and dates
[Current_Date] from the "IDRa" tbl from the [First Day] which is codes to 50
and the [Last Day] which is coded to 60. Dates prior to the [First Day] are
irrelevant. How do I do this? Thanks..Randy

 

[Guest]

Randy,

I'm not absolutely certain I understand what you want here. It sounds like
you want to pull the records with a date between the date corresponding to
the [First Day] and the date corresponding to the [Last Day] fro each
employee. If that is so, then the query you want should look like:

SELECT D1.[Current_Date], D1.EID, D1.CommCode
FROM IDRa D1
where D1.[Current_Date] between (select D2.[Current_Date] from IRDa D2 where
D2.CommCode = 50 and D2.EID= D1.EID)
and (select D2.date1 from IRDa D2 where D2.CommCode= 60 and D2.EID= D1.EID)
order by D1.EID, D1.[Crurent_Date];

That entire 'BETWEEN ... AND ...' goes into the Criteria line for the date
(on one line of course).

If anybody's got a cleaner way to do this, let us all know please. And if
this isn't what you were looking for, let us know.

Good Luck!

 

[Randy]

I tried your sql but i got an error of "Cannot find the input table IDRa"
You have the right idea of what I need but I may have explained the tables
wrong. I have a table "IDRa" which contains the fields [Current_Date] and
[EID] The second table "IDRb" which is the subtable to my form contains
fields of [CID] 50 = First Day and 60 = Last Day. The third table
"CommCode" has a field of [CID] and [DESC] which is the DESCription of the
codes to [CID] I hope this makes sense...Randy

Randy,

I'm not absolutely certain I understand what you want here. It sounds like
you want to pull the records with a date between the date corresponding to
the [First Day] and the date corresponding to the [Last Day] fro each
employee. If that is so, then the query you want should look like:

SELECT D1.[Current_Date], D1.EID, D1.CommCode
FROM IDRa D1
where D1.[Current_Date] between (select D2.[Current_Date] from IRDa D2
where
D2.CommCode = 50 and D2.EID= D1.EID)
and (select D2.date1 from IRDa D2 where D2.CommCode= 60 and D2.EID=
D1.EID)
order by D1.EID, D1.[Current_Date];

That entire 'BETWEEN ... AND ...' goes into the Criteria line for the date
(on one line of course).

If anybody's got a cleaner way to do this, let us all know please. And if
this isn't what you were looking for, let us know.

Good Luck!
--
Chaim

Access 2000 I have an employee db where I enter employee timesheet info.
I
have a tbl of "IDRa" where employee time info is entered and another tbl
of
"CommCode" which stores codes for our office use. Two codes I use in
"CommCode" are 50 which is for [First Day] worked and 60 which is for
[Last
Day] worked. I need to query only the employees [EID] and dates
[Current_Date] from the "IDRa" tbl from the [First Day] which is codes to
50
and the [Last Day] which is coded to 60. Dates prior to the [First Day]
are
irrelevant. How do I do this? Thanks..Randy

 

[Guest]

Randy,

I'm still missing something. Where are [FirstDay] and [LastDay]? Are they
fields in IRDb? I think they must be, otherwise I don't see how you are
bounding your dates. Also, there needs to be a way to restrict the FirstDay
and LastDay to each EID; I assume there is one set for each employee.

Assuming a set up that looks like:
IDRa ---> (EID, Current_Date)
CommCode ---> (CID, Desc)
IDRb ---> (SomeDate, CID, EID)

I don't understand where the FirstDay and LastDay fit into this, since I am
understanding the CID to differentiate between the First and Last Day.

Am I getting this right now?

Assuming the above, it seems that the SQL should look like:

SELECT D1.[Current_Date], D1.EID
FROM IDRa D1
where D1.[Current_Date] between (select D2.[SomeDate] from IRDb D2 where
D2.CID= 50 and D2.EID= D1.EID)
and (select D2.[SomeDate] from IRDb D2 where D2.CID= 60 and D2.EID= D1.EID)
order by D1.EID, D1.[Current_Date];

I don't see CommCode being used in this at all, unless you want to look up
the 'FiirstDay' and 'LastDay' codes and these are the description strings.

Let me know if I'm getting this straight.

Good Luck!
--
Chaim

I tried your sql but i got an error of "Cannot find the input table IDRa"
You have the right idea of what I need but I may have explained the tables
wrong. I have a table "IDRa" which contains the fields [Current_Date] and
[EID] The second table "IDRb" which is the subtable to my form contains
fields of [CID] 50 = First Day and 60 = Last Day. The third table
"CommCode" has a field of [CID] and [DESC] which is the DESCription of the
codes to [CID] I hope this makes sense...Randy

Randy,

I'm not absolutely certain I understand what you want here. It sounds like
you want to pull the records with a date between the date corresponding to
the [First Day] and the date corresponding to the [Last Day] fro each
employee. If that is so, then the query you want should look like:

SELECT D1.[Current_Date], D1.EID, D1.CommCode
FROM IDRa D1
where D1.[Current_Date] between (select D2.[Current_Date] from IRDa D2
where
D2.CommCode = 50 and D2.EID= D1.EID)
and (select D2.date1 from IRDa D2 where D2.CommCode= 60 and D2.EID=
D1.EID)
order by D1.EID, D1.[Current_Date];

That entire 'BETWEEN ... AND ...' goes into the Criteria line for the date
(on one line of course).

If anybody's got a cleaner way to do this, let us all know please. And if
this isn't what you were looking for, let us know.

Good Luck!
--
Chaim

Access 2000 I have an employee db where I enter employee timesheet info.
I
have a tbl of "IDRa" where employee time info is entered and another tbl
of
"CommCode" which stores codes for our office use. Two codes I use in
"CommCode" are 50 which is for [First Day] worked and 60 which is for
[Last
Day] worked. I need to query only the employees [EID] and dates
[Current_Date] from the "IDRa" tbl from the [First Day] which is codes to
50
and the [Last Day] which is coded to 60. Dates prior to the [First Day]
are
irrelevant. How do I do this? Thanks..Randy

 

[Randy]

'First Day" and "Last Day" is text I v'e typed under the field [Desc] which
corresponds to the field [CID] with an assigned code of 50 for first day and
60 for Last Day. This is my lookup table of "CommCode" where my combo box
in IDRb selects the [CID] code of 50 or 60 which is bound to [CID] of my
IDRb table. Each employee has an assigned [EID] number. Each employee
will only have one "First Day" and one "Last Day" per year. Looking at your
example below IDRb does not have "SomeDate" or "EID" IDRb has a
relationship to IDRa through its primary key of "IDRa_ID" ([IDRA_ID] is in
IDRa and IDRb) I'm confusing myself, I hope this helps. Than a lot for
your help...Randy

Randy,

I'm still missing something. Where are [FirstDay] and [LastDay]? Are they
fields in IRDb? I think they must be, otherwise I don't see how you are
bounding your dates. Also, there needs to be a way to restrict the
FirstDay
and LastDay to each EID; I assume there is one set for each employee.

Assuming a set up that looks like:
IDRa ---> (EID, Current_Date)
CommCode ---> (CID, Desc)
IDRb ---> (SomeDate, CID, EID)

I don't understand where the FirstDay and LastDay fit into this, since I
am
understanding the CID to differentiate between the First and Last Day.

Am I getting this right now?

Assuming the above, it seems that the SQL should look like:

SELECT D1.[Current_Date], D1.EID
FROM IDRa D1
where D1.[Current_Date] between (select D2.[SomeDate] from IRDb D2 where
D2.CID= 50 and D2.EID= D1.EID)
and (select D2.[SomeDate] from IRDb D2 where D2.CID= 60 and D2.EID=
D1.EID)
order by D1.EID, D1.[Current_Date];

I don't see CommCode being used in this at all, unless you want to look up
the 'FiirstDay' and 'LastDay' codes and these are the description strings.

Let me know if I'm getting this straight.

Good Luck!
--
Chaim

I tried your sql but i got an error of "Cannot find the input table IDRa"
You have the right idea of what I need but I may have explained the
tables
wrong. I have a table "IDRa" which contains the fields [Current_Date]
and
[EID] The second table "IDRb" which is the subtable to my form contains
fields of [CID] 50 = First Day and 60 = Last Day. The third table
"CommCode" has a field of [CID] and [DESC] which is the DESCription of
the
codes to [CID] I hope this makes sense...Randy

Randy,

I'm not absolutely certain I understand what you want here. It sounds
like
you want to pull the records with a date between the date corresponding
to
the [First Day] and the date corresponding to the [Last Day] fro each
employee. If that is so, then the query you want should look like:

SELECT D1.[Current_Date], D1.EID, D1.CommCode
FROM IDRa D1
where D1.[Current_Date] between (select D2.[Current_Date] from IRDa D2
where
D2.CommCode = 50 and D2.EID= D1.EID)
and (select D2.date1 from IRDa D2 where D2.CommCode= 60 and D2.EID=
D1.EID)
order by D1.EID, D1.[Current_Date];

That entire 'BETWEEN ... AND ...' goes into the Criteria line for the
date
(on one line of course).

If anybody's got a cleaner way to do this, let us all know please. And
if
this isn't what you were looking for, let us know.

Good Luck!
--
Chaim


:

Access 2000 I have an employee db where I enter employee timesheet
info.
I
have a tbl of "IDRa" where employee time info is entered and another
tbl
of
"CommCode" which stores codes for our office use. Two codes I use in
"CommCode" are 50 which is for [First Day] worked and 60 which is for
[Last
Day] worked. I need to query only the employees [EID] and dates
[Current_Date] from the "IDRa" tbl from the [First Day] which is codes
to
50
and the [Last Day] which is coded to 60. Dates prior to the [First
Day]
are
irrelevant. How do I do this? Thanks..Randy