Re: Day of week queries
From: Chris2 (rainofsteel.NOTVALID_at_GETRIDOF.luminousrain.com)
Date: 03/02/05
- Next message: Chris2: "Re: Schedule Query"
- Previous message: Chris2: "Re: Dates Queries"
- In reply to: Brent Sweet: "Day of week queries"
- Next in thread: Chris2: "Re: Day of week queries"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 1 Mar 2005 19:12:06 -0800
"Brent Sweet" <BrentSweet@discussions.microsoft.com> wrote in message
news:7A59586D-9BC0-42DD-B442-F22529FB4295@microsoft.com...
> 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.
- Next message: Chris2: "Re: Schedule Query"
- Previous message: Chris2: "Re: Dates Queries"
- In reply to: Brent Sweet: "Day of week queries"
- Next in thread: Chris2: "Re: Day of week queries"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
