Results 1 to 8 of 8
  1. #1
    RoniR is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    4

    Automated Weekly Schedule

    Hi there,

    I would like to create an automated weekly schedule for my volunteers business.

    Each of my volunteers can be assigned to up to 3 different department.
    Each volunteers has a fixed shift for each department that normally doesn't change.
    I have the volunteers table with Last Name, First Name, Department 1, Shift for Dep1, Department 2, Shift for Dep2, Department 3, Shift for Dep3.

    My goal is to have a report that looks like a weekly calendar for each department.
    So for example, I want to have a report for Dep X, where Mon AM shows all the volunteers in Dep X with the Mon AM shift, and Mon PM shows all the volunteers in Dep X with the Mon PM shift etc.

    What is the best way to do this?

    I tried the following solution:


    I was successful in creating the following queries: Dep X Mon AM query, Dep X Mon PM query, Dep X Tue AM query etc. for all AM PM shifts of the week.
    I was then trying to create a report that is based on all those queries, but it wouldn't let me complete this report because quote: "you have chosen fields from record sources which the wizard can't connect". I'm assuming this error is because all my queries are based on the same table?

    How can I proceed?

    Thanks so much!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I have the volunteers table with Last Name, First Name, Department 1, Shift for Dep1, Department 2, Shift for Dep2, Department 3, Shift for Dep3.
    sounds like this is where your problem resides. Looks like you are trying to apply excel methodology to a database environment which either won't work or if you can get it to work will be complex with high maintenance costs. Basically you are building what should be field values as field names - Department 1, Shift for Dep1, Department 2, Shift for Dep2, Department 3, Shift for Dep3, etc

    this should be at least two tables, one for volunteers and one for date/department/shift or perhaps three with department being separate. Hard to say without knowing more

    then you would only need 1 query, not 3 or more. Suggest before you go any further google/bing 'normalisation' to get a better idea how tables should be constructed.

  3. #3
    RoniR is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    4
    Hi Ajax, thank you for your help, much appreciated!
    So you are suggesting to have another table called “volunteers schedule” and have the following fields:
    Department, Sunday Am, Sunday Pm, Monday Am, Mon Pm.......Sat Am, Sat Pm
    and populate this table with the following example record:
    Dep X, person A, Person B, Person C, Person D........Person E, Person F
    then do a query to select only records with Dep X
    then base the report (schedule for Dep X) on that query.
    Did I understand you correctly?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    No - as I said read up on normalisation.

    it might be something like this

    tblVolunteers
    VolunteerPK
    FirstName
    LastName
    Tel
    Email
    ...
    ...


    tblDepartments
    DeptPK
    DeptName
    ...
    ...

    tblShifts
    ShiftPK
    ShiftName
    TimeFrom
    TimeTo
    ...
    ...

    tblSchedules
    SchedulePK
    VolunteerFK
    SchedDate
    DeptFK
    ShiftFK

    you might have another table to detail when volunteers are available so you don't accidentally assign them during these periods

    tblAvailability
    AvailabilityPK
    VolunteerFK
    AvailFromDate
    AvailToDate

    and/or you might have another table, or field in the volunteer table, that stated whether they can work AM shift, PM shift or both - or can only work one or the other, but not both. All depends on what your requirements are.

    Note the PK suffix means Primary Key and is an autonumber. The FK suffix means Foreign or Family Key and is a long and enables you to link the tables together

  5. #5
    RoniR is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    4
    Thank you Ajax! I still don’t understand though how I will create a report that looks like a department monthly schedule?
    How can I write one query for the scheduletbl on which this report will be based.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    couldn't tell you off the top of my head - there must be 100's of different department schedule designs

    most likely you would use a crosstab query to put either departments or volunteers across the top. Or you can take a look at this calendar tool http://msaccessgurus.com/tool/CalendarMaker.htm

    your current design is based on excel methodology where a) data and presentation is in the same 'view' (access stores data in tables and presentation via forms and reports) and b) data is not normalised (simplistically excel data is 'short and wide', databases are 'tall and narrow'). Pretty much anything you know about excel is the opposite of what you need to know about databases.

    how would you write a query? - start with tblschedules and join to the other tables as required when you need the data from those tables like a name - and as stated above, you'll probably need to convert to a crosstab query. I'm not going to do it for you because it is an example, may not be a proper representation of your business - only you know that - and I have no idea what other issues you may have - shutdowns/bank holidays priorities of business flow etc.

  7. #7
    RoniR is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    4
    Thank you Ajax for your help! I will look into the crosstab query as I’m not familiar with what it does.
    Sounds like Excel is much more suitable for designing a schedule than Access but I’ll see if I can make it work in Access with the crosstab query. Thank you again for all your help.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Sounds like Excel is much more suitable for designing a schedule
    no more or less suitable. All depends on the requirement, if you just need to fill in cells each week, you could just as easily use word . databases are about managing data. If you don't need to manage it then access is probably the wrong tool

    good luck with your project

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Weekly Report
    By bronson_mech in forum Reports
    Replies: 2
    Last Post: 01-20-2017, 05:21 AM
  2. Help: Automated Rotating Schedule
    By Breezer23 in forum Access
    Replies: 2
    Last Post: 01-08-2013, 06:56 PM
  3. Weekly Total
    By coffeyja in forum Access
    Replies: 3
    Last Post: 01-13-2012, 12:30 AM
  4. Weekly Totals
    By tcheck in forum Access
    Replies: 4
    Last Post: 09-27-2011, 09:35 AM
  5. weekly total
    By nkuebelbeck in forum Queries
    Replies: 2
    Last Post: 03-24-2010, 02:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums