Re: Involved query / possible over Access assistance.
From: GB (GB_at_discussions.microsoft.com)
Date: 03/25/05
- Next message: Marion: "Number of tables related in a query"
- Previous message: Andreas: "Re: transferring data from one record to an other"
- In reply to: MGFoster: "Re: Involved query / possible over Access assistance."
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 25 Mar 2005 04:43:03 -0800
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
"MGFoster" wrote:
> -----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-----
>
>
> GB wrote:
> > 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.
>
- Next message: Marion: "Number of tables related in a query"
- Previous message: Andreas: "Re: transferring data from one record to an other"
- In reply to: MGFoster: "Re: Involved query / possible over Access assistance."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
