Additional Help with a query

  • Thread starter Thread starter Duane
  • Start date Start date
D

Duane

I have recently asked this question here about how to create a query when I
don't know which field I need to query until the user makes a selection on
my switchboard. I am still having great difficulty with a solution.

The concept here is as follows. The user needs to know which work groups
are off work on the date that is entered in the date field on the form. The
default value for the Textbox is Date() + 1. When the user opens the
switchboard, the textbox shows the next days date. Using the date, the user
prints a report of all people who are available for overtime call-in for
that date, OR opens a form to enter hour is the employee works.

One of the obstacles is as follows. Most of the staff work a rotating
schedule. There are groups 1 - 7. The employees work what is referred to
as a 6 on - 2 off schedule, with the exception of - every 5th and 6th week
they get 3 days off. Fri, Sat, Sun, and then Sat, Sun, Mon the following
week. To make it more complicated, there are other work schedules too. If
fact, there are a total of 49 different schedules. There are 7 different
types with 7 variations of each type.

What I am trying to achieve here is a way to query the table to find out
which groups are off that day, based on the date in the textbox on the
switchboard. I don't want for the user to have to think about which group
is off each day. I want my database to tell them.

The date Textbox receives the focus when the switchboard opens. The code
behind the GotFocus Event for the Textbox does the following. It takes the
date and creates a variable and stores the date as a Long Integer. Then I
do a Mod Function on the Long Integer to get the remainder. For example:
MyLongDate = CLng(MyDate) The long Integer = 39568. The MOD Function -
MyResult = MyLongDate Mod 196. The remainder is 172.

Date = 04/30/2008
Long Integer = 39568
Mod 196 = 172

If the Date were 05/01/2008
Long Integer = 39569
MOD 196 = 173

The reason for the MOD 196 is because of the table I have. Because of the
49 different work schedules, it takes 196 days to complete the cycle. There
are 196 fields in the table, one for each MOD number. The MODs go from
0 -195.

Here are some of the fields in the table:

Group Text
GType Text
Description Text
MOD0 Number
MOD1 Number
MOD2 Number
MOD3 Number
MOD4 Number
MOD5 Number
MOD6 Number
....... Number
MOD195 Number

MOD 7- 195 omitted.

The table is populated and never changes. There are 49 records in the
table. Record #1 equals Group #1. It is a Rotating Day Off schedule of 6
on and 2 off. I have determined that the table/schedule starts on
10/10/2007. That day is equal to MOD0. Looking at Group #1 I know that
that group has 11/10 & 11/11 Off. The fields representing an Off day has a
1 in it.. Continuing through the table, Group #1 is off again on MOD 8, 9,
16,17, 24,25, and etc., all the way through the table to MOD 195. Records
2 - 49 are populated in the same manner as Group #1.

Using today's date of 04/30/2008 (Date()+1), I know the MOD remainder equals
172. Now I can query the table as follows: SELECT Rot.Group FROM Rot WHERE
Rot.MOD172=1; and find out that groups
1,2,11,12,16,17,18,19,32,34,35,37,47, and 48 are all off on that date.

Here is my dilemma;

I need to find a way to be able to query the table as soon as the users has
determined the date they intend to use. I want the query to have the
flexibility to be able to query the appropriate field based on the MOD
remainder for that day, concatenated for the text literal of MOD (MOD0,
MOD12 or whichever MOD is Off).

The query would need to be a Make table query. Then I could use the results
to query the Employee table to know who can be called in for overtime. I
would like to think it could be as easy as creating a variable MyMOD= "MOD "
& MyResult. Then I could query the table like this, SELECT Rot.Group FROM
Rot WHERE MyMOD=1;. I have been told this cannot be done.

I sure could use any help that anyone might be able to give me.
 
Duane,

If I understand what you are needing to do, I would suggest that you can do
basically what you have described: "I would like to think it could be as
easy as creating a variable MyMOD= "MOD " & MyResult. Then I could query
the table like this, SELECT Rot.Group FROM Rot WHERE MyMOD=1"

First try and see if you can develop your query that will return the correct
values usng the value that would be stored in your variable. Then you can
use VBA code to define your variable and assign the correct value to it. Then
use the "QueryDef" function to modify the sql statement for your existinig
query so that it will have the value from your variable as the criteria. You
do this by assigning the entire sql statement to a variable and then set the
sql of your defined query using the "QueryDef" function. Check out Access
help for info on the "QueryDef" function.

Once you have modified the sql for your query, it will have the correct
criteria and you can just use it as you normally would except that your VBA
code has modified the query each time a new MOD value is selected by the user.
 
Right next to the date field, I would have a command button (cmd_Available)
to identify those that are available. In the click event of that control, I
would build a SQL statement to identify all of the groups that are off on
that day. Assuming that your employees table contains a field "GroupID" that
maps it to a record in the [RotatingDayOff] table, I don't think you will
need an intermediate step.

Personally, I avoid MakeTable queries in my code. I will generally create
the table that I want the data to go into, and will run a delete query to
empty the table, and an Append query to add data to it (personal preference).
Then, I would create a report based on this table, and would use that to
present the information back to your applications users. The code behind
that button might look like:

Private sub cmd_Available_Click

Dim strSQL as string
Dim intMod as integer

'Delete all the records from tbl_Available

'assumes your date textbox is named txt_AvailableDate
intMod = Cint(me.txt_AvailableDate) Mod 196

'Assumes you have created tbl_Available to store the names
strSQL = "DELETE * FROM tbl_Available"
Currentdb.execute strsql

'The WHERE clause of this SQL statement is the critical
'piece of this code, because it allow you to select the
'appropriate column.
'You could not do this from a saved query
strSQL = "INSERT INTO tbl_Available (EmpLastName, " _
& "EmpFirstName, EmpPhone) " _
& "SELECT E.EmpLastName, E.EmpFirstName, " _
& " E.EmpPhone " _
& "FROM tbl_Employees E " _
& "INNER JOIN tbl_RotDayOff RDO " _
& "ON E.GroupID = RDO.GroupID " _
& "WHERE RDO.[MOD" & intMod & "] = 1"
debug.print strSQL
Currentdb.Execute strsql, dbfailonerror
docmd.OpenReport "rpt_Whos_Available", acViewPreview

End Sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Thank you very much Dale. I think this is exactly what I am looking for.
Can't wait to try it out.


Dale Fye said:
Right next to the date field, I would have a command button
(cmd_Available)
to identify those that are available. In the click event of that control,
I
would build a SQL statement to identify all of the groups that are off on
that day. Assuming that your employees table contains a field "GroupID"
that
maps it to a record in the [RotatingDayOff] table, I don't think you will
need an intermediate step.

Personally, I avoid MakeTable queries in my code. I will generally create
the table that I want the data to go into, and will run a delete query to
empty the table, and an Append query to add data to it (personal
preference).
Then, I would create a report based on this table, and would use that to
present the information back to your applications users. The code behind
that button might look like:

Private sub cmd_Available_Click

Dim strSQL as string
Dim intMod as integer

'Delete all the records from tbl_Available

'assumes your date textbox is named txt_AvailableDate
intMod = Cint(me.txt_AvailableDate) Mod 196

'Assumes you have created tbl_Available to store the names
strSQL = "DELETE * FROM tbl_Available"
Currentdb.execute strsql

'The WHERE clause of this SQL statement is the critical
'piece of this code, because it allow you to select the
'appropriate column.
'You could not do this from a saved query
strSQL = "INSERT INTO tbl_Available (EmpLastName, " _
& "EmpFirstName, EmpPhone) " _
& "SELECT E.EmpLastName, E.EmpFirstName, " _
& " E.EmpPhone " _
& "FROM tbl_Employees E " _
& "INNER JOIN tbl_RotDayOff RDO " _
& "ON E.GroupID = RDO.GroupID " _
& "WHERE RDO.[MOD" & intMod & "] = 1"
debug.print strSQL
Currentdb.Execute strsql, dbfailonerror
docmd.OpenReport "rpt_Whos_Available", acViewPreview

End Sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Duane said:
I have recently asked this question here about how to create a query when
I
don't know which field I need to query until the user makes a selection
on
my switchboard. I am still having great difficulty with a solution.

The concept here is as follows. The user needs to know which work groups
are off work on the date that is entered in the date field on the form.
The
default value for the Textbox is Date() + 1. When the user opens the
switchboard, the textbox shows the next days date. Using the date, the
user
prints a report of all people who are available for overtime call-in for
that date, OR opens a form to enter hour is the employee works.

One of the obstacles is as follows. Most of the staff work a rotating
schedule. There are groups 1 - 7. The employees work what is referred
to
as a 6 on - 2 off schedule, with the exception of - every 5th and 6th
week
they get 3 days off. Fri, Sat, Sun, and then Sat, Sun, Mon the following
week. To make it more complicated, there are other work schedules too.
If
fact, there are a total of 49 different schedules. There are 7 different
types with 7 variations of each type.

What I am trying to achieve here is a way to query the table to find out
which groups are off that day, based on the date in the textbox on the
switchboard. I don't want for the user to have to think about which
group
is off each day. I want my database to tell them.

The date Textbox receives the focus when the switchboard opens. The code
behind the GotFocus Event for the Textbox does the following. It takes
the
date and creates a variable and stores the date as a Long Integer. Then
I
do a Mod Function on the Long Integer to get the remainder. For example:
MyLongDate = CLng(MyDate) The long Integer = 39568. The MOD Function -
MyResult = MyLongDate Mod 196. The remainder is 172.

Date = 04/30/2008
Long Integer = 39568
Mod 196 = 172

If the Date were 05/01/2008
Long Integer = 39569
MOD 196 = 173

The reason for the MOD 196 is because of the table I have. Because of
the
49 different work schedules, it takes 196 days to complete the cycle.
There
are 196 fields in the table, one for each MOD number. The MODs go from
0 -195.

Here are some of the fields in the table:

Group Text
GType Text
Description Text
MOD0 Number
MOD1 Number
MOD2 Number
MOD3 Number
MOD4 Number
MOD5 Number
MOD6 Number
....... Number
MOD195 Number

MOD 7- 195 omitted.

The table is populated and never changes. There are 49 records in the
table. Record #1 equals Group #1. It is a Rotating Day Off schedule of
6
on and 2 off. I have determined that the table/schedule starts on
10/10/2007. That day is equal to MOD0. Looking at Group #1 I know that
that group has 11/10 & 11/11 Off. The fields representing an Off day has
a
1 in it.. Continuing through the table, Group #1 is off again on MOD 8,
9,
16,17, 24,25, and etc., all the way through the table to MOD 195.
Records
2 - 49 are populated in the same manner as Group #1.

Using today's date of 04/30/2008 (Date()+1), I know the MOD remainder
equals
172. Now I can query the table as follows: SELECT Rot.Group FROM Rot
WHERE
Rot.MOD172=1; and find out that groups
1,2,11,12,16,17,18,19,32,34,35,37,47, and 48 are all off on that date.

Here is my dilemma;

I need to find a way to be able to query the table as soon as the users
has
determined the date they intend to use. I want the query to have the
flexibility to be able to query the appropriate field based on the MOD
remainder for that day, concatenated for the text literal of MOD (MOD0,
MOD12 or whichever MOD is Off).

The query would need to be a Make table query. Then I could use the
results
to query the Employee table to know who can be called in for overtime. I
would like to think it could be as easy as creating a variable MyMOD=
"MOD "
& MyResult. Then I could query the table like this, SELECT Rot.Group
FROM
Rot WHERE MyMOD=1;. I have been told this cannot be done.

I sure could use any help that anyone might be able to give me.
Click to expand...
Click to expand...
 
Worked like a champ Dale. Of course I made a couple of adjustments, but
thank you again for your help. I had to make one change. I was receiving
an overflow error on the
intMod = Cint(me.txt_AvailableDate) Mod 196.

I had to change it to
intMod = Clng(me.txt_AvailableDate) Mod 196.

Thanks again......


Dale Fye said:
Right next to the date field, I would have a command button
(cmd_Available)
to identify those that are available. In the click event of that control,
I
would build a SQL statement to identify all of the groups that are off on
that day. Assuming that your employees table contains a field "GroupID"
that
maps it to a record in the [RotatingDayOff] table, I don't think you will
need an intermediate step.

Personally, I avoid MakeTable queries in my code. I will generally create
the table that I want the data to go into, and will run a delete query to
empty the table, and an Append query to add data to it (personal
preference).
Then, I would create a report based on this table, and would use that to
present the information back to your applications users. The code behind
that button might look like:

Private sub cmd_Available_Click

Dim strSQL as string
Dim intMod as integer

'Delete all the records from tbl_Available

'assumes your date textbox is named txt_AvailableDate
intMod = Cint(me.txt_AvailableDate) Mod 196

'Assumes you have created tbl_Available to store the names
strSQL = "DELETE * FROM tbl_Available"
Currentdb.execute strsql

'The WHERE clause of this SQL statement is the critical
'piece of this code, because it allow you to select the
'appropriate column.
'You could not do this from a saved query
strSQL = "INSERT INTO tbl_Available (EmpLastName, " _
& "EmpFirstName, EmpPhone) " _
& "SELECT E.EmpLastName, E.EmpFirstName, " _
& " E.EmpPhone " _
& "FROM tbl_Employees E " _
& "INNER JOIN tbl_RotDayOff RDO " _
& "ON E.GroupID = RDO.GroupID " _
& "WHERE RDO.[MOD" & intMod & "] = 1"
debug.print strSQL
Currentdb.Execute strsql, dbfailonerror
docmd.OpenReport "rpt_Whos_Available", acViewPreview

End Sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Duane said:
I have recently asked this question here about how to create a query when
I
don't know which field I need to query until the user makes a selection
on
my switchboard. I am still having great difficulty with a solution.

The concept here is as follows. The user needs to know which work groups
are off work on the date that is entered in the date field on the form.
The
default value for the Textbox is Date() + 1. When the user opens the
switchboard, the textbox shows the next days date. Using the date, the
user
prints a report of all people who are available for overtime call-in for
that date, OR opens a form to enter hour is the employee works.

One of the obstacles is as follows. Most of the staff work a rotating
schedule. There are groups 1 - 7. The employees work what is referred
to
as a 6 on - 2 off schedule, with the exception of - every 5th and 6th
week
they get 3 days off. Fri, Sat, Sun, and then Sat, Sun, Mon the following
week. To make it more complicated, there are other work schedules too.
If
fact, there are a total of 49 different schedules. There are 7 different
types with 7 variations of each type.

What I am trying to achieve here is a way to query the table to find out
which groups are off that day, based on the date in the textbox on the
switchboard. I don't want for the user to have to think about which
group
is off each day. I want my database to tell them.

The date Textbox receives the focus when the switchboard opens. The code
behind the GotFocus Event for the Textbox does the following. It takes
the
date and creates a variable and stores the date as a Long Integer. Then
I
do a Mod Function on the Long Integer to get the remainder. For example:
MyLongDate = CLng(MyDate) The long Integer = 39568. The MOD Function -
MyResult = MyLongDate Mod 196. The remainder is 172.

Date = 04/30/2008
Long Integer = 39568
Mod 196 = 172

If the Date were 05/01/2008
Long Integer = 39569
MOD 196 = 173

The reason for the MOD 196 is because of the table I have. Because of
the
49 different work schedules, it takes 196 days to complete the cycle.
There
are 196 fields in the table, one for each MOD number. The MODs go from
0 -195.

Here are some of the fields in the table:

Group Text
GType Text
Description Text
MOD0 Number
MOD1 Number
MOD2 Number
MOD3 Number
MOD4 Number
MOD5 Number
MOD6 Number
....... Number
MOD195 Number

MOD 7- 195 omitted.

The table is populated and never changes. There are 49 records in the
table. Record #1 equals Group #1. It is a Rotating Day Off schedule of
6
on and 2 off. I have determined that the table/schedule starts on
10/10/2007. That day is equal to MOD0. Looking at Group #1 I know that
that group has 11/10 & 11/11 Off. The fields representing an Off day has
a
1 in it.. Continuing through the table, Group #1 is off again on MOD 8,
9,
16,17, 24,25, and etc., all the way through the table to MOD 195.
Records
2 - 49 are populated in the same manner as Group #1.

Using today's date of 04/30/2008 (Date()+1), I know the MOD remainder
equals
172. Now I can query the table as follows: SELECT Rot.Group FROM Rot
WHERE
Rot.MOD172=1; and find out that groups
1,2,11,12,16,17,18,19,32,34,35,37,47, and 48 are all off on that date.

Here is my dilemma;

I need to find a way to be able to query the table as soon as the users
has
determined the date they intend to use. I want the query to have the
flexibility to be able to query the appropriate field based on the MOD
remainder for that day, concatenated for the text literal of MOD (MOD0,
MOD12 or whichever MOD is Off).

The query would need to be a Make table query. Then I could use the
results
to query the Employee table to know who can be called in for overtime. I
would like to think it could be as easy as creating a variable MyMOD=
"MOD "
& MyResult. Then I could query the table like this, SELECT Rot.Group
FROM
Rot WHERE MyMOD=1;. I have been told this cannot be done.

I sure could use any help that anyone might be able to give me.
Click to expand...
Click to expand...
 
Glad I could help.
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Duane said:
Worked like a champ Dale. Of course I made a couple of adjustments, but
thank you again for your help. I had to make one change. I was receiving
an overflow error on the
intMod = Cint(me.txt_AvailableDate) Mod 196.

I had to change it to
intMod = Clng(me.txt_AvailableDate) Mod 196.

Thanks again......


Dale Fye said:
Right next to the date field, I would have a command button
(cmd_Available)
to identify those that are available. In the click event of that control,
I
would build a SQL statement to identify all of the groups that are off on
that day. Assuming that your employees table contains a field "GroupID"
that
maps it to a record in the [RotatingDayOff] table, I don't think you will
need an intermediate step.

Personally, I avoid MakeTable queries in my code. I will generally create
the table that I want the data to go into, and will run a delete query to
empty the table, and an Append query to add data to it (personal
preference).
Then, I would create a report based on this table, and would use that to
present the information back to your applications users. The code behind
that button might look like:

Private sub cmd_Available_Click

Dim strSQL as string
Dim intMod as integer

'Delete all the records from tbl_Available

'assumes your date textbox is named txt_AvailableDate
intMod = Cint(me.txt_AvailableDate) Mod 196

'Assumes you have created tbl_Available to store the names
strSQL = "DELETE * FROM tbl_Available"
Currentdb.execute strsql

'The WHERE clause of this SQL statement is the critical
'piece of this code, because it allow you to select the
'appropriate column.
'You could not do this from a saved query
strSQL = "INSERT INTO tbl_Available (EmpLastName, " _
& "EmpFirstName, EmpPhone) " _
& "SELECT E.EmpLastName, E.EmpFirstName, " _
& " E.EmpPhone " _
& "FROM tbl_Employees E " _
& "INNER JOIN tbl_RotDayOff RDO " _
& "ON E.GroupID = RDO.GroupID " _
& "WHERE RDO.[MOD" & intMod & "] = 1"
debug.print strSQL
Currentdb.Execute strsql, dbfailonerror
docmd.OpenReport "rpt_Whos_Available", acViewPreview

End Sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Duane said:
I have recently asked this question here about how to create a query when
I
don't know which field I need to query until the user makes a selection
on
my switchboard. I am still having great difficulty with a solution.

The concept here is as follows. The user needs to know which work groups
are off work on the date that is entered in the date field on the form.
The
default value for the Textbox is Date() + 1. When the user opens the
switchboard, the textbox shows the next days date. Using the date, the
user
prints a report of all people who are available for overtime call-in for
that date, OR opens a form to enter hour is the employee works.

One of the obstacles is as follows. Most of the staff work a rotating
schedule. There are groups 1 - 7. The employees work what is referred
to
as a 6 on - 2 off schedule, with the exception of - every 5th and 6th
week
they get 3 days off. Fri, Sat, Sun, and then Sat, Sun, Mon the following
week. To make it more complicated, there are other work schedules too.
If
fact, there are a total of 49 different schedules. There are 7 different
types with 7 variations of each type.

What I am trying to achieve here is a way to query the table to find out
which groups are off that day, based on the date in the textbox on the
switchboard. I don't want for the user to have to think about which
group
is off each day. I want my database to tell them.

The date Textbox receives the focus when the switchboard opens. The code
behind the GotFocus Event for the Textbox does the following. It takes
the
date and creates a variable and stores the date as a Long Integer. Then
I
do a Mod Function on the Long Integer to get the remainder. For example:
MyLongDate = CLng(MyDate) The long Integer = 39568. The MOD Function -
MyResult = MyLongDate Mod 196. The remainder is 172.

Date = 04/30/2008
Long Integer = 39568
Mod 196 = 172

If the Date were 05/01/2008
Long Integer = 39569
MOD 196 = 173

The reason for the MOD 196 is because of the table I have. Because of
the
49 different work schedules, it takes 196 days to complete the cycle.
There
are 196 fields in the table, one for each MOD number. The MODs go from
0 -195.

Here are some of the fields in the table:

Group Text
GType Text
Description Text
MOD0 Number
MOD1 Number
MOD2 Number
MOD3 Number
MOD4 Number
MOD5 Number
MOD6 Number
....... Number
MOD195 Number

MOD 7- 195 omitted.

The table is populated and never changes. There are 49 records in the
table. Record #1 equals Group #1. It is a Rotating Day Off schedule of
6
on and 2 off. I have determined that the table/schedule starts on
10/10/2007. That day is equal to MOD0. Looking at Group #1 I know that
that group has 11/10 & 11/11 Off. The fields representing an Off day has
a
1 in it.. Continuing through the table, Group #1 is off again on MOD 8,
9,
16,17, 24,25, and etc., all the way through the table to MOD 195.
Records
2 - 49 are populated in the same manner as Group #1.

Using today's date of 04/30/2008 (Date()+1), I know the MOD remainder
equals
172. Now I can query the table as follows: SELECT Rot.Group FROM Rot
WHERE
Rot.MOD172=1; and find out that groups
1,2,11,12,16,17,18,19,32,34,35,37,47, and 48 are all off on that date.

Here is my dilemma;

I need to find a way to be able to query the table as soon as the users
has
determined the date they intend to use. I want the query to have the
flexibility to be able to query the appropriate field based on the MOD
remainder for that day, concatenated for the text literal of MOD (MOD0,
MOD12 or whichever MOD is Off).

The query would need to be a Make table query. Then I could use the
results
to query the Employee table to know who can be called in for overtime. I
would like to think it could be as easy as creating a variable MyMOD=
"MOD "
& MyResult. Then I could query the table like this, SELECT Rot.Group
FROM
Rot WHERE MyMOD=1;. I have been told this cannot be done.

I sure could use any help that anyone might be able to give me.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top