Day of week queries

[Brent Sweet]

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.



Expand AllCollapse All

 

[Rick B]

I would think you could put your format statement under each column of your
query. This seems like a very very strange design though.

SSince you are looking to find anyone with a "TUE" in any column, you;d need
to place your criteria under each "Day#" column, but on a separate row.


Employee# Day1 Day2
Day3...
=Format(Date(),"ddd")
=Format(Date(),"ddd")

=Format(Date(),"ddd")

 

[Rick B]

That looks bad as the wrap messed it up...


Emp# Day1 Day2 Day3
Cond
Cond
Cond
..
..
..

 

[Chris2]

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.



Expand AllCollapse All

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

CREATE TABLE Employees_03012005
([Emplyee#] LONG
,Day1 TEXT(3)
,Day2 TEXT(3)
,Day3 TEXT(3)
,Day4 TEXT(3)
,Day5 TEXT(3)
,Day6 TEXT(3)
,Day7 TEXT(3)
,CONSTRAINT pk_Employees PRIMARY KEY ([Emplyee#])
)

Load in the sample date above.

Execute the following Query:

SELECT E1.[Emplyee#]
FROM Employees_03012005 AS E1
WHERE 1 = IIF(E1.Day1 = Format(Weekday(Date()), "ddd") OR
E1.Day2 = Format(Weekday(Date()), "ddd") OR
E1.Day3 = Format(Weekday(Date()), "ddd") OR
E1.Day4 = Format(Weekday(Date()), "ddd") OR
E1.Day5 = Format(Weekday(Date()), "ddd") OR
E1.Day6 = Format(Weekday(Date()), "ddd") OR
E1.Day7 = Format(Weekday(Date()), "ddd")
, 1)

Results:

1234
1354

It needs more test data to make sure, but try it out.


Sincerely,

Chris O.