day-of-week selection : time cards / work scheduling

[KTPIERCE]

I need to input startdate enddate parameters, and using these MSAccess data...
[tt]
EmpName, WDay1 Wday2 Wday3 Wday4 Wday5*
Joe 1 2 3 4 5
Fred 2 3 4 5 6
Tom 3 4 5 6 7
[/tt]
*Wday=workday, where 1=sunday 7=Sat, or similar, open to suggestion)

...produce a set of timecards, in one-calendar-week groups, with one card for each employee for each day he works.
For example, if I enter Start=4/16/06,End=4/19/06, I'd want to report
[tt]
Fred 4/17
Fred 4/18
Fred 4/19
Joe 4/16
Joe 4/17
Joe 4/18
Joe 4/19
Tom 4/18
Tom 4/19
[/tt]
I'm a complete newbie to Access; my experience is all mainframe cobol. Can someone get me started? That's "just started", not write it for me. Please?

 

[Golom]

I don't really understand your table.

Specifically, for "Fred" on "WDay3", what does the "4" represent? If "WDay3" means "Tuesday" then "4" on "Tuesday" means ... ???

Your expected output doesn't seem to involve the values in the source table.

How, from the source table and the date range, do you deduce that "Tom" (for example) works only two days?

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[KTPIERCE]

The table lets us specify which days of the week a person works. While it would be nice, we dont at this point care if there are sequence errors or duplicates; these are simply 5 occurrences of workdays for each person listed. This data could live in a linked table if you like, too.
The table says
Joe works Sunday thru Thursday
Fred works MOnday thru Friday
TOm works Tuesday thru Saturday.

 

[Golom]

[tt]
tblIntegers
num
0
1
2
3
4
5
6
7
[/tt]

Select EmployeeID, 
       DateAdd ("d", I.Num, [Start Date]) As WorkDay

From   Employees E, Integers I

Where  WeekDay (DateAdd ("d", I.Num, [Start Date]))  IN
       (Select WorkDay From EmployeeWorkDays D
        Where E.EmployeeID = D.EmployeeID)

  AND  DateAdd ("d", I.Num, [Start Date]) BETWEEN
       [Start Date] AND [End Date]

  AND  I.Num <= DateDiff ( "d", [Start Date], [End Date] )

ORDER BY 1, 2

Assuming that the days an employee works are in a table of the form
[tt]
EmployeeWorkDays
EmployeeID WorkDay
Joe 1
Joe 2
etc.
[/tt]

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]