Schedule Query

[Guest]

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.

 

[MGFoster]

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-----

 

[Guest]

Hi, Brent.

Since the result of the Format function could appear in any of the seven day
fields, you need to include the function call in each day field, on separate
criteria rows, which creates a logical OR conjunction. The SQL would be
something like:

SELECT Employees.FirstName, Employees.LastName
FROM EmpSchedule INNER JOIN Employees ON EmpSchedule.EmpID = Employees.EmpID
WHERE (((EmpSchedule.Day1)=Format(Date(),"ddd"))) OR
(((EmpSchedule.Day2)=Format(Date(),"ddd"))) OR
(((EmpSchedule.Day3)=Format(Date(),"ddd"))) OR
(((EmpSchedule.Day4)=Format(Date(),"ddd"))) OR
(((EmpSchedule.Day5)=Format(Date(),"ddd"))) OR
(((EmpSchedule.Day6)=Format(Date(),"ddd"))) OR
(((EmpSchedule.Day7)=Format(Date(),"ddd")));


Hope that helps.
Sprinks

 

[MGFoster]

MGFoster,

When I check the Immediate Window:

? WeekDay(Date())
3

? WeekDay(Date()-1)
2

? WeekDay(Date()-2)
1

? WeekDay(Date()-3)
7

I'm thinking it's output number range is 1-7.

Sincerely,

Chris O.

You're right. I can't remember where I thought weekdays were
represented as 0 thru 6. Hmmm.... Probably some other language.

 

[Chris2]

Brent Sweet wrote:

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,

When I check the Immediate Window:

? WeekDay(Date())
3

? WeekDay(Date()-1)
2

? WeekDay(Date()-2)
1

? WeekDay(Date()-3)
7

I'm thinking it's output number range is 1-7.


Sincerely,

Chris O.

 

[Chris2]

Hmmm.... Probably some other language.

Yeah, I hear that.