Convert date to weekday overview

[LarsBoer]

Hey Guys,

I have a problem and I can't seem to solve it.

I have a Table called Planning. In that table i have: Date, the activityID, EmployeeID, and some other irrelevant to this question.

Now i want to create a screen in wich you can select a week. Then it will show you a view that contains the following headers:

Employeee  -  Monday  -  thuesday  - wednesday  -  Thurstday - friday

John        -  Admin    -  mailroom   -  reception   -   admin      - reception
Carl         -  Mailroom- Mailroom    - admin         -   admin      - admin

Does anyone know how i can do this? ps it has to be possible to create new records...

i hope someone can help me with this

[apr pillai]

Try the following sample method:

• Copy the following Code into a Standard Module:

Public Function GetDuty(ByVal x As Integer)
    GetDuty = Choose(x, "Admin", "Mailroom", "Receiption")
End Function

If more options are required then add them to the list above. Selection of option depends on the value passed to the variable x.

• Create a Table (DutyRoster) with the attached Image structure and data.
• Copy and paste the following SQL into the SQL editing Window of a new Query and save it with the Name DutyRosterQ:

SELECT DutyRoster.Ename,
 GetDuty([Mon]) AS Monday,
 GetDuty([Tue]) AS Tuesday, 
 GetDuty([Wed]) AS Wednesday,
 GetDuty([Thu]) AS Thursday,
 GetDuty([Fri]) AS Friday
FROM DutyRoster;

• Open the DutyRosterQ in Normal view.

If you need to convert a Date to get the number 1,2,3 then use the expression:

(weekday([DateFieldName]) Mod 3)+1

to get the numbers indicated in the table fields.

You may require a CrossTab Query as Source for the SQL given above instead of the DutyRoster Table.

[LarsBoer]

Thanks for your reply. But i think i didn't explain it correctly.

The purpose of the form is to plan activities for employees. So i select by box week 43 for example. Than it will show me employees on the left. Folowed with Monday, Tuesday, etc) Beneath the day's i can select the activities for each employee. Then click save and the planning is saved in the table Planning.

I could send you the database as an example. I should warn you, it's in dutch

Thanks for your help,

Lars

[LarsBoer]

Here is a sample form. This is how my boss would like to see the planning form.

I'm looking forward to your response