Storing Weekday Flags

[TipTop]

Hi NG,

I would like to store a flag for weekdays for each record in my table.
By this I mean, the user can (for one record) flag Monday thru Sunday as
True or False.

I could obviously have one boolean field for each weekday. However, it
occurs to me it would be neater to store all the data as one byte
whereby each bit would represent one weekday e.g.

Bit number: 1 0 1 0 1 0 0
Weekday: M T W T F S S
Status: Y N Y N Y N N

This would give us:
-SSFTWTM
Base2: 00010101
Base10: 21

If I pull the byte back from the database I can easily work with it but
I want to know if there are any bit level operations I can carry out
using standard Access SQL. e.g. to know if a record is valid for a
Monday I need to know that the first bit = 1.

Is this possible? And also, is it really worth it or should I just go
with separate fields (certainly that would be easier for reading the
database directly)?

Thanks for any advice.

Tiptop

 

[Douglas J. Steele]

It's possible, but are you really going to be saving that much space?

Define a bunch of constants

Public Const cMonday As Long = 1
Public Const cTuesday As Long = 2
Public Const cWednesday As Long = 4
Public Const cThursday As Long = 8
Public Const cFriday As Long = 16
Public Const cSaturday As Long = 32
Public Const cSunday As Long = 64

To determine whether or not a particular day is represented in the value,
And the value with the appropriate constant. If the result of the And
operation is 0, the day is not represented. If it's non-zero, it is.
Fortunately, Access treats every non-zero value as True.

?(21 and cMonday)
1
?(21 and cTuesday)
0
?(21 and cWednesday)
4
?(21 and cThursday)
0
?(21 and cFriday)
16
?(21 and cSaturday)
0
?(21 and cSunday)
0

 

[TipTop]

Hi Doug,

Thanks for your fast response. You're right - it won't save much space.
I was just trying to make my code more generic (I'm generating it in
VBA) - so I can avoid having to change the SQL depending on the day of
the week i.e. use something like

'-------------------------------------------------------
myDate = #9/25/2005#
sql = " SELECT * FROM test "
sql = sql & " WHERE (myweekday AND "
sql = sql & 2^(Weekday(myDate, vbMonday) - 1) & ") = true"
'-------------------------------------------------------


Rather than change it depending on the day of the week:
'-------------------------------------------------------
myDate = #9/25/2005#
sql = " SELECT * FROM test "
DayNo = Weekday(myDate, vbMonday)
Select Case DayNo
Case 1: sql = sql & " WHERE Monday = True"
etc.
'-------------------------------------------------------


I can't get your example (21 and cMonday, say) to work as part of an SQL
query. Is this possible? (I know it works in VBA - I apologize for not
being clearer in my OP.)

I guess if I name my fields accordingly WeekDay1, WeekDay2 I could still
use generic code:

sql = sql & " WHERE WeekDay" & Weekday(myDate, vbMonday) & " = true"

or even something using Format(myDate, "ddd") to construct the FieldName
on the fly maybe.

Thanks again for your help.

 

[Douglas J. Steele]

Sounds as though you've got denormalized tables. You should never have
fields named Monday, Tuesday, ... (nor, for that matter, Weekday1, Weekday2,
....)

Rather than 7 fields in a particular row, you should have up to 7 rows in a
second table, with Weekday as one of the fields in that second table.

If you really want to use the flag approach, and you're working strictly
within Access (as opposed to, say, linking to SQL Server), you could always
write a public function that you can pass the weekday flags variable and a
weekday constant, and have the function return True or False.

 

[TipTop]

I thought maybe 7 fields to handle this wasn't *that* many but you're
right of course: it's a far better approach to have a Weekdays table to
bring them together, just using the values 1 thru 7 against a foreign key.

I'm actually working in VB writing to Access (thru ADO) but am designing
deliberately to be database agnostic as far as I can. (I know there's
certain elements I'll need to change of course - passing dates for
example is TO_DATE('YYYY-MM-DD') for Oracle for example, whereas Access
seems happy with #DD/MMM/YYYY#). I'm pretty sure the bitwise stuff can
be achieved in MySQL but I won't tackle it if it's not cross
platform/database.

Thanks for all your help.

 

[TipTop]

Hi Doug,

While you're taking an interest (if you still are interested!).... would
you consider this code appropriate to use the dedicated weekday table
solution? I'm trying to get a recordset back looking something like this:
- week days -
1 True 09:00 09:30 Blah blah True True False True etc.
2 False 11:00 12:00 Blah blah False True False False etc.

which I'm loading into a Listview (it would be easier to get the data
back from query preformatted rather than handle it in VB).

SELECT d.pkid, d.ismandatory,
d.starttime, d.endtime, d.blockdesc,
SUM( b.weekdayno = 1) as MON,
SUM( b.weekdayno = 2) as Tue,
SUM(b.weekdayno = 3) as Wed,
SUM(b.weekdayno = 4) etc.

FROM templates d, templateweekdays b
WHERE d.ownerid = 1
AND b.templateid = d.pkid

GROUP BY d.pkid, d.ismandatory,
d.starttime, d.endtime, d.blockdesc

Thanks very much

 

[TipTop]

Actually scratch that... the following works (returns rows where no
weekdays are flagged true).

SELECT d.pkid, d.ismandatory, d.starttime, d.endtime,d.blockdesc,
SUM( w.weekdayno = 1 and d.pkid= w.DesignerBlockID) as MON,
SUM( w.weekdayno = 2 and w.DesignerBlockID = d.pkid) as TUE,
SUM( w.weekdayno = 3 and w.DesignerBlockID = d.pkid) as WED,
SUM( w.weekdayno = 4 and w.DesignerBlockID = d.pkid) as THU,
SUM( w.weekdayno = 5 and w.DesignerBlockID = d.pkid) as FRI,
SUM( w.weekdayno = 6 and w.DesignerBlockID = d.pkid) as SAT,
SUM( w.weekdayno = 7 and w.DesignerBlockID = d.pkid) as SUN,

FROM templates d, templateweekdays w
WHERE d.ownerid = 1

GROUP BY d.pkid, d.ismandatory, d.blockdate, d.starttime,
d.endtime,d.blockdesc

Thanks