Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Unanswered: Weekday Schedule Report

    Hello,

    I'm currently working on a feature of my database that allows a manager to schedule staff members to certain shifts throughout the week. The primary table, tblShifts, contains the following data:

    ShiftID
    EmployeeID
    Date
    TimeIn
    TimeOut

    While the interface for entering data works correctly, I cannot seem to figure out how to create a report to display this information as intended. The way it *should* look is having a column structure where the EmployeeID (their name) is listed in column one, followed by columns for each day of the week (mon, tuesday, etc). In each column, the assigned shift for that day (if any) is displayed for the employee as follows:


    Employee Monday Tuesday Wednesday

    Bob 1-5 11-7 9-6

    Jane 4-10 6-12 8-5



    And so on. My question is, How can this layout be achieved in MsAccess considering the data table structure I have chosen?

    Thanks for your help!

  2. #2
    Join Date
    Apr 2002
    Location
    Illinois
    Posts
    133
    With your current structure, using VBA somewhere along the line may be the only solution. One place would be in the report. Group the report on EmployeeID and Date. Have a group footer for Employee ID. Format this footer to display the info as you want. Then use VBA in a hidden detail section to fill in fields in the EmployeeID group footer.

    Hope this helps.
    SteveH

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Pleistarchos
    Hello,

    I'm currently working on a feature of my database that allows a manager to schedule staff members to certain shifts throughout the week. The primary table, tblShifts, contains the following data:

    ShiftID
    EmployeeID
    Date
    TimeIn
    TimeOut

    While the interface for entering data works correctly, I cannot seem to figure out how to create a report to display this information as intended. The way it *should* look is having a column structure where the EmployeeID (their name) is listed in column one, followed by columns for each day of the week (mon, tuesday, etc). In each column, the assigned shift for that day (if any) is displayed for the employee as follows:


    Employee Monday Tuesday Wednesday

    Bob 1-5 11-7 9-6

    Jane 4-10 6-12 8-5



    And so on. My question is, How can this layout be achieved in MsAccess considering the data table structure I have chosen?

    Thanks for your help!
    Is this report gonna be for exactly one week? if so, why not make a table with Employee attributes and 7 columns 1 for each day then just query and populate the table ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Apr 2004
    Posts
    3
    What SQL would I use to query the shifts table for the correct information to be transfered into the new table (with 1 column for each day)? I created a crosstab query, which displays the information as intended, however I cannot seem to figure out how to limit the data for only one week's worth of records.

  5. #5
    Join Date
    Apr 2002
    Location
    Illinois
    Posts
    133
    Use the "Between" operator and have the user enter the beginning and ending dates of the schedule. You may consider creating fields on a form that the query can pull these dates from.
    SteveH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •