Re: Schedule Query

From: MGFoster (me_at_privacy.com)
Date: 03/01/05 

Date: Tue, 01 Mar 2005 21:25:56 GMT

Brent Sweet wrote:
> I have a tbale of employees and the day they work as follows:
>
> Emplyee# Day1 Day2 Day3 Day4 Day5 Day6 Day7
> 1234 Mon Tue Wed Thu Fri
> 1354 Tue Wed Thu Fri Sat
> 1658 Sat Sun Mon Wed Thu
>
> I want to query a list of employee #s that are working today.. IE if today
> is Tue I want my query to return Users 1234 and 1354. I want access to
> figure what day it is though I have been using Format(Date(),"ddd") but have
> been unable how to crossreference that function with the above data to get
> the desired results.
>
> My goal is to click the query any given day of the week and have a list of
> users that are working on that day.

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

First off, your table is incorrectly designed. It should be like this:

CREATE TABLE work_days (
   EmployeeID long not null references Employees ,
   work_day byte not null check (day_number Between 1 and 7) ,
   week_day byte not null check (weekday_number Between 0 and 6) ,
   CONSTRAINT PK_work_days PRIMARY KEY (EmployeeID, work_day)
)

work_day corresponds to your "Day1" thru "Day7." Day1 = 1, Day2 = 2,
etc. week_day corresponds to Sun (0) thru Sat (6)

Using the above table the query would be:

SELECT EmployeeID, work_day
FROM work_days
WHERE week_day = WeekDay(Date())

The function WeekDay() returns the number of the week day (0-6) of the
date parameter. Date() is the VBA function that returns the current
date. 

-- 
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQiTd3YechKqOuFEgEQK4uQCfUQyKHmB8l1tRFh2ZlFfKa3DXdesAn3yd
lAA0Xgcc6L0oGMNveCfNMNOt
=bp3u
-----END PGP SIGNATURE-----

Relevant Pages

  • Re: OT: SQL
    ... query processing. ... FROM Employees e, Employees m, Management mgt ... Manager and Employee Salaries. ... The scheme used does not model database files in general, ...
    (sci.logic)
  • Re: OT: SQL
    ... said was a totally failed attempt to formalize and automate database ... query processing. ... create table Employees ... Manager and Employee Salaries. ...
    (sci.logic)
  • Re: query a number stored as text
    ... Your query is quite complex, ... I need to join the Employees table to the Orders table like so ... ... "Lee" wrote in message ... >> GlobalSign digital certificate is a forgery and should be deleted without ...
    (microsoft.public.access.queries)
  • Re: OT: SQL
    ... said was a totally failed attempt to formalize and automate database ... query processing. ... create table Employees ... Manager and Employee Salaries. ...
    (sci.logic)
  • Re: Multi-Value Field Query
    ... It was directed at what Microsoft calls a Multi-Valued field, which is not the same as what you have ... This doesn't work very well in the query interface in Access, so you probably need to write a VBA function that does this for you. ... three areas 'CreatedBy', 'Owner', 'Employees'. ... appropriately and it lists all employees involved ...
    (microsoft.public.access.queries)