sql question

[Duane]

Hello,

I guess the easiest way to explain this problem is to show you what I am
confronted with. I have a project that was written in Paradox that I want
to do in Access. I am confronted with one major issue. I have been racking
my non-programmer brain trying to figure this out.

There is a table strictly used as a LOOKUP table, to determine which work
groups are off, on a given day. The table (Paradox) has 49 records.
Basically 7 different work schedules with 7 different variations for each
group. Some of the schedules are as follows;

Rotating Days Off
Set days off (Work 5, Off 2)
10-4 (Work 10, Off 4)
7-2-3-2 (Work 7,Off 2,Work 3,Off 2).
Each type of schedule has 7 different variations to the schedule.

The table (Paradox ROT.db) has the following fields; Group, GroupType,
Description. Then there are several fields labeled MOD concatenated with a
number. Field #1 starts out at MOD0 and the last field is MOD195, for a
total of 196 MOD fields. I assume there are 196 fields because it takes 196
days to make a complete cycle of all the schedules. The data type for all
the MOD fields is set to logical.

In the Paradox database, there is a textbox on the switchboard form that
gets the current date. Through code the date is converted to a long
integer. Then there is a MOD function to get the remainder, which is then
concatenated with a string "MOD" to get the end result of MOD162. Using
what I have tried to explain here, the current date is 04/20/2008. Then
long integer equivalent is 39558, and the MOD remainder is 162.

Here is the code that is used in the paradox database.

myDate = date(theDate) ;assigns date from main page to var
myLongDate = longInt(myDate) ;converts the date to a long integer

myMod = smallInt(myLongDate.mod(196)) ;gets the remainder of myLongDate
divided by 196

myField = "MOD"+string(myMod) ;creates a string = MOD + myMod converted to
string

;the following query finds the 14 RDO groups on myDate
myQ = Query

:WORK:RDOrot.db|Group |~myField|
|check |True |


The string variable of MOD162 is being used find out which of the 14 groups
are off on 04/20/2008, which are 4, 5, 13, 14, 18, 19, 20, 21, 36, 38, 39,
42, 44, 45.

On my form in Access, I have similar textboxes that return the same as the
Paradox version;

Dim MyDate As Date
Dim MyLongDate As Long
Dim MyResult As Integer
Dim MyMODResult as string

MyDate = Me.txtDate
MyLongDate = CLng(MyDate)
Me.txtLongDate = MyLongDate

MyResult = MyLongDate Mod 196
Me.txtResult = MyResult

MyMODResult = "MOD" & MyResult

I would like to emulate the same thing is Access, but I am not sure how I
can use the variable to query the table like what is being done in Paradox.
Can this be done in VBA or even the QBE in access? i.e., SELECT
MyTable.MyMODResult From MyTable WHERE MyTable.MyMODResult = -1;
(MyMODResult = a string variable of MOD162.)

The ultimate question is can I somehow use MyMODResult to query MyTable to
return the 14 groups that off work on 04/20/2008?

I appreciate any and all help.

Thanks in advance

 

[Bob Quintal]

Hello,

I guess the easiest way to explain this problem is to show you
what I am confronted with. I have a project that was written in
Paradox that I want to do in Access. I am confronted with one
major issue. I have been racking my non-programmer brain trying
to figure this out.

There is a table strictly used as a LOOKUP table, to determine
which work groups are off, on a given day. The table (Paradox)
has 49 records. Basically 7 different work schedules with 7
different variations for each group. Some of the schedules are as
follows;

Rotating Days Off
Set days off (Work 5, Off 2)
10-4 (Work 10, Off 4)
7-2-3-2 (Work 7,Off 2,Work 3,Off 2).
Each type of schedule has 7 different variations to the schedule.

The table (Paradox ROT.db) has the following fields; Group,
GroupType, Description. Then there are several fields labeled MOD
concatenated with a number. Field #1 starts out at MOD0 and the
last field is MOD195, for a total of 196 MOD fields. I assume
there are 196 fields because it takes 196 days to make a complete
cycle of all the schedules. The data type for all the MOD fields
is set to logical.

In the Paradox database, there is a textbox on the switchboard
form that gets the current date. Through code the date is
converted to a long integer. Then there is a MOD function to get
the remainder, which is then concatenated with a string "MOD" to
get the end result of MOD162. Using what I have tried to explain
here, the current date is 04/20/2008. Then long integer
equivalent is 39558, and the MOD remainder is 162.

Here is the code that is used in the paradox database.

myDate = date(theDate) ;assigns date from main page to var
myLongDate = longInt(myDate) ;converts the date to a long integer

myMod = smallInt(myLongDate.mod(196)) ;gets the remainder of
myLongDate divided by 196

myField = "MOD"+string(myMod) ;creates a string = MOD + myMod
converted to string

;the following query finds the 14 RDO groups on myDate
myQ = Query

:WORK:RDOrot.db|Group |~myField|
|check |True |


The string variable of MOD162 is being used find out which of the
14 groups are off on 04/20/2008, which are 4, 5, 13, 14, 18, 19,
20, 21, 36, 38, 39, 42, 44, 45.

On my form in Access, I have similar textboxes that return the
same as the Paradox version;

Dim MyDate As Date
Dim MyLongDate As Long
Dim MyResult As Integer
Dim MyMODResult as string

MyDate = Me.txtDate
MyLongDate = CLng(MyDate)
Me.txtLongDate = MyLongDate

MyResult = MyLongDate Mod 196
Me.txtResult = MyResult

MyMODResult = "MOD" & MyResult

I would like to emulate the same thing is Access, but I am not
sure how I can use the variable to query the table like what is
being done in Paradox. Can this be done in VBA or even the QBE in
access? i.e., SELECT MyTable.MyMODResult From MyTable WHERE
MyTable.MyMODResult = -1; (MyMODResult = a string variable of
MOD162.)

The ultimate question is can I somehow use MyMODResult to query
MyTable to return the 14 groups that off work on 04/20/2008?

I appreciate any and all help.

Thanks in advance

yes, either by building a querystring and opening it in a recordset,
or also by setting the value "MOD" + Forms!<<formname>>!txtResult in
the criteria row of the relevant column in the query.

 

[Duane]

Thanks for the response Bob.

I am not very good at building a querystring, but I will start reading up on
that. I would really like to figure this out. I have be looking at this,
leaving it, and then returning to for a few weeks later. This has been
going on for awhile.

You mentioned setting the value "MOD" + Forms!frm_Switchboard!txtResult in
the criteria row of the relevant column in the query. That's just it, "MOD"
+ txtResult IS the relevant column in the query.

In essence, I need to be able to query 1 of the 196 columns(fields) in the
table, however, the column is unknown until the Mod function returns the
MyResult, which in this example is 162. Once the 162 is concatenated with
the text "MOD", (MOD162), this is the actual Column (field) that I need to
query.

There are 49 records of which there are 14 records that are true any given
field. I need to know which records are true. Whenever the user changes
the date in the txtDate textbox, I want to be able to use the updated
txtResult value to know which field in the table I need to query.

On 04/20/2008, I need to query the field MOD162 to know which groups are off
work. On 04/21/2008, I would need to query field MOD163. On 04/22/2008 I
would need to query MOD164 and so on. I would have to create 196 different
queries.

Thanks again.

 

[Bob Quintal]

Thanks for the response Bob.

I am not very good at building a querystring, but I will start
reading up on that. I would really like to figure this out. I
have be looking at this, leaving it, and then returning to for a
few weeks later. This has been going on for awhile.

You mentioned setting the value "MOD" +
Forms!frm_Switchboard!txtResult in the criteria row of the
relevant column in the query. That's just it, "MOD" + txtResult
IS the relevant column in the query.

In essence, I need to be able to query 1 of the 196
columns(fields) in the table, however, the column is unknown until
the Mod function returns the MyResult, which in this example is
162. Once the 162 is concatenated with the text "MOD", (MOD162),
this is the actual Column (field) that I need to query.

There are 49 records of which there are 14 records that are true
any given field. I need to know which records are true. Whenever
the user changes the date in the txtDate textbox, I want to be
able to use the updated txtResult value to know which field in the
table I need to query.

On 04/20/2008, I need to query the field MOD162 to know which
groups are off work. On 04/21/2008, I would need to query field
MOD163. On 04/22/2008 I would need to query MOD164 and so on. I
would have to create 196 different queries.

Thanks again.

Ok, now I understand part of your problem in that the original design
has rows and columns interchanged.

You'll need to build the query in Visual Basic.

'Given the Paradox statement, the equivalent vb code is
Dim strSQL as string

' for today
strSQL = "SELECT Rot.Group from Rot WHERE MOD" _
& format(clng(date()) mod 196, "000") & " is true;"

' for the date in txtdate
strSQL = "SELECT Rot.Group from Rot WHERE MOD" _
& format(clng(me.txtdate) mod 196, "000") & " is true;"

' now write the SQL to the query.
' in this example I'll call it qryGroupsOffWork
dim qdfRot as querydef
set qdfRot = currentdb.querydefs("qryGroupsOffWork")
qdfRot.SQL = strSQL
qdfRot.close
' now run the query whichever way works for you..

Q