Involved query / possible over Access assistance.

[GB]

This is the idea I want to try to implement in an access database.

I have an event that has a start and end date. Obviously the end date can
not be before the start date, however the end date could be the same as the
start date or the end date may be any amount of time following the start date.

For each event, I want to store the day(s) of the week that the event
occurs. I will be using a form to make the selection of the days available,
and I believe I will be able to control that input. However, I want to store
the corresponding value for the selection(s) made. I am thinking that if I
use an integer, and assign a value of 2 raised to the power of the day of the
week, that I can then decode the stored value to get each day. A table I
have established is like this:
[Day] [Value]
Monday 64
Tuesday 32
Wed. 16
Thurs 8
Fri 4
Sat 2
Sun 1

So if an event occurs on Mon, wed, and fri then I wish to store 84 (64+16+4)
for that event.

So now I have stored the day(s) of the week the event is on.

I want to then do a query for a given week. I have been able to setup the
basic part of the query, that if the start/end dates are within the week then
the event appears in the returned query. But I have not been able to design
a query that will use that information in conjunction with the stored integer
number so that I could test and say, okay if the event does occur at some
point during the week 1) display which day(s) of the week, 2) perform a query
for which events will be done on a given day, 3) display the event only if it
actually is happening (i.e. if the end date is Tuesday, and the class is only
held Friday, then don't display the class.)

I have written a simple VBA module that can return the power of 2 that is
included in the originally stored number, but I haven't been able to make any
good use of the information. (Obvious Access programming/usage deficiency.)

Hopefully I have explained what I am trying to do, if nothing else, I'm
trying to make data entry easy for the database user, and provide quality
results while minimizing actual database management.

Obviously when using a form to input data, I can test the days of the week
for the start and end date, and only provide those days in the drop down
list. Then when I go to store the data, I can convert it to an integer using
the table that has the values stored. I think that I'm using Access wrongly
for this, and if so, help a guy out if you could.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

According to some SQL DB experts bitmaps shouldn't be used 'cuz it
violates the 1st Normal Form (no more than one data item per cell).
Also, Standard SQL doesn't include a bit operator, but most SQL products
do include some. In Access 2002, using ANSI SQL-92 option, you can use
the BAND operator in a query. E.g. (DaysOfWeek is a bitmap):

IIf((DaysOfWeek BAND 64)=64,"Sunday","")

The experts would suggest that you have a table like this:

Table: EventDays
Fields: EventID - link to the Events table
DayOfWeek - numbers 1 (Sun) thur 7 (Sat)
VBA standards vbSunday, vbMonday, etc.

Example data (I'll use words instead of numbers for the DayOfWeek):

EventID DayOfWeek
1 Tuesday
1 Friday
2 Monday
2 Wednesday
2 Thursday

If the Events form is set up w/ a subform of Event Days w/ just one
field showing (Day of Week) in a ComboBox you could populate the
EventDays table very easily, instead of running a bitwise operation on
the selected days.

Read the Access Help articles on Creating a subform for more info.
Also, read some database design books or articles on the WEB for info on
Normalization.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkHfX4echKqOuFEgEQLhSACbBb2dJSreMrAeMrTqxfdUdnjrSE4AoJqN
WF56GTw0mUmqNPPaMW/Rp67r
=fBRe
-----END PGP SIGNATURE-----

This is the idea I want to try to implement in an access database.

I have an event that has a start and end date. Obviously the end date can
not be before the start date, however the end date could be the same as the
start date or the end date may be any amount of time following the start date.

For each event, I want to store the day(s) of the week that the event
occurs. I will be using a form to make the selection of the days available,
and I believe I will be able to control that input. However, I want to store
the corresponding value for the selection(s) made. I am thinking that if I
use an integer, and assign a value of 2 raised to the power of the day of the
week, that I can then decode the stored value to get each day. A table I
have established is like this:
[Day] [Value]
Monday 64
Tuesday 32
Wed. 16
Thurs 8
Fri 4
Sat 2
Sun 1

So if an event occurs on Mon, wed, and fri then I wish to store 84 (64+16+4)
for that event.

So now I have stored the day(s) of the week the event is on.

I want to then do a query for a given week. I have been able to setup the
basic part of the query, that if the start/end dates are within the week then
the event appears in the returned query. But I have not been able to design
a query that will use that information in conjunction with the stored integer
number so that I could test and say, okay if the event does occur at some
point during the week 1) display which day(s) of the week, 2) perform a query
for which events will be done on a given day, 3) display the event only if it
actually is happening (i.e. if the end date is Tuesday, and the class is only
held Friday, then don't display the class.)

I have written a simple VBA module that can return the power of 2 that is
included in the originally stored number, but I haven't been able to make any
good use of the information. (Obvious Access programming/usage deficiency.)

Hopefully I have explained what I am trying to do, if nothing else, I'm
trying to make data entry easy for the database user, and provide quality
results while minimizing actual database management.

Obviously when using a form to input data, I can test the days of the week
for the start and end date, and only provide those days in the drop down
list. Then when I go to store the data, I can convert it to an integer using
the table that has the values stored. I think that I'm using Access wrongly
for this, and if so, help a guy out if you could.

 

[GB]

Thank you,

The next morning when I awoke, I realized that I was in fact trying to abuse
Access, and that it related to normalization. I think that it is possible to
accomplish the task desired without performing the bitwise computation that
you have described. I have found that I can get the desired information
using a table setup like you show, where I store the unique event ID, and
then the occurrence of that unique event, and can perform the entry using a
form. Thank you for your response.

GB

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

According to some SQL DB experts bitmaps shouldn't be used 'cuz it
violates the 1st Normal Form (no more than one data item per cell).
Also, Standard SQL doesn't include a bit operator, but most SQL products
do include some. In Access 2002, using ANSI SQL-92 option, you can use
the BAND operator in a query. E.g. (DaysOfWeek is a bitmap):

IIf((DaysOfWeek BAND 64)=64,"Sunday","")

The experts would suggest that you have a table like this:

Table: EventDays
Fields: EventID - link to the Events table
DayOfWeek - numbers 1 (Sun) thur 7 (Sat)
VBA standards vbSunday, vbMonday, etc.

Example data (I'll use words instead of numbers for the DayOfWeek):

EventID DayOfWeek
1 Tuesday
1 Friday
2 Monday
2 Wednesday
2 Thursday

If the Events form is set up w/ a subform of Event Days w/ just one
field showing (Day of Week) in a ComboBox you could populate the
EventDays table very easily, instead of running a bitwise operation on
the selected days.

Read the Access Help articles on Creating a subform for more info.
Also, read some database design books or articles on the WEB for info on
Normalization.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkHfX4echKqOuFEgEQLhSACbBb2dJSreMrAeMrTqxfdUdnjrSE4AoJqN
WF56GTw0mUmqNPPaMW/Rp67r
=fBRe
-----END PGP SIGNATURE-----

This is the idea I want to try to implement in an access database.

I have an event that has a start and end date. Obviously the end date can
not be before the start date, however the end date could be the same as the
start date or the end date may be any amount of time following the start date.

For each event, I want to store the day(s) of the week that the event
occurs. I will be using a form to make the selection of the days available,
and I believe I will be able to control that input. However, I want to store
the corresponding value for the selection(s) made. I am thinking that if I
use an integer, and assign a value of 2 raised to the power of the day of the
week, that I can then decode the stored value to get each day. A table I
have established is like this:
[Day] [Value]
Monday 64
Tuesday 32
Wed. 16
Thurs 8
Fri 4
Sat 2
Sun 1

So if an event occurs on Mon, wed, and fri then I wish to store 84 (64+16+4)
for that event.

So now I have stored the day(s) of the week the event is on.

I want to then do a query for a given week. I have been able to setup the
basic part of the query, that if the start/end dates are within the week then
the event appears in the returned query. But I have not been able to design
a query that will use that information in conjunction with the stored integer
number so that I could test and say, okay if the event does occur at some
point during the week 1) display which day(s) of the week, 2) perform a query
for which events will be done on a given day, 3) display the event only if it
actually is happening (i.e. if the end date is Tuesday, and the class is only
held Friday, then don't display the class.)

I have written a simple VBA module that can return the power of 2 that is
included in the originally stored number, but I haven't been able to make any
good use of the information. (Obvious Access programming/usage deficiency.)

Hopefully I have explained what I am trying to do, if nothing else, I'm
trying to make data entry easy for the database user, and provide quality
results while minimizing actual database management.

Obviously when using a form to input data, I can test the days of the week
for the start and end date, and only provide those days in the drop down
list. Then when I go to store the data, I can convert it to an integer using
the table that has the values stored. I think that I'm using Access wrongly
for this, and if so, help a guy out if you could.