Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164

    Table Relationship DB Help

    Hi,

    I have a part built DB for a Warehouse Rota team

    Days - 7 week rolling pattern

    Nights - 5 week rolling pattern

    Team manager - 16 week rolling pattern

    I'm not sure how to connect them together?

    They all share one common denominator - Week commencing Sunday date (01/01/2017,08/01/2017 etc. etc.)

    How do I align 3 shift patterns together in a table of different rotation week cycles of 7, 5 and 16 weeks.

    Any help would be appreciated, i have attached a few screen shots and the DB itself.

    Some tables and forms are for testing out purposes (Trial and Error)

    Only focus on the DB Relationship tables and the main rota form.



    Thanks
    Attached Thumbnails Attached Thumbnails Warehouse Rota.jpg   Warehouse Rota Relationship.jpg   Warehouse Rota Relationship 2.jpg  
    Attached Files Attached Files

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,264
    can you clarify what 'align 3 shift patterns together' means. Does it mean you want to see what everyone is doing and in what role in any particular week?

    I suspect your use of non normalised tables for pattern complicates things and some tables I don't understand why you have them - the week date table for example - you appear to have repeating week numbers on a 35 week year. And why do you need separate tables for team managers? they are constructed in exactly the same way as for the shift workers.

    And how do you handle the situation where somebody changes roles, either permanently or temporarily? e.g. team manager cover for holidays or long term sick? Or perhaps someone on day shift does a 'long one' to cover a night shift worker.

    It also looks like your night shift is over midnight - so a nightshift starts Saturday evening and ends Sunday morning

  3. #3
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164
    Hi Ajax , thanks for the reply.

    can you clarify what 'align 3 shift patterns together' means. Does it mean you want to see what everyone is doing and in what role in any particular week?

    Yes , essentially get all shift patterns to run from 1 table or a query and feed into form, the driver of this form will be week commencing Sunday Date.

    I want the form to look like the frmMainRota but my data that is feeding this form is in a currently Un- normalized state (A mess)

    Your correct on the 35 week, I basically worked out that 5 week pattern and 7 week pattern would repeat every 35 weeks by both shifts returning back to week 1 again on every 36 week, that was the plan anyway but where does the 16 week pattern rotation fit in this plan.
    I know there is a better way of doing this but I need help on the best approach and table design.

    Once the form is created to a good normalized standard, I plan to have dropdowns on each box to change to holiday, sick, meeting etc.

    From the data saved the Rota can then feed an updated report to print off with Live information.

    if anyone changes shift? I was thinking along the lines of EmpiD table, change role that is linked to a RoleProfile table (what would you suggest)

    Any suggestions that you can make would be great.

    Thanks

    Darren

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,264
    Sorry to say this, but I think you have designed your db in a way that won't really work

    Best I can suggest is use a union query. You will need to define a 'date zero' when all shift patterns align on week 1 (this should just mean moving your patterns around)

    Then using things like datepart, dateadd, datediff, mod functions etc determine where you are in each shift pattern

    It would also be easier if you do away with your tm tables and combine in the other tables.

    I would also split your pattern ID field into two, keep the week number of the rotation as a separate field - for below called ShiftFK and WeekNum

    Also your rotation table should be removed and replaced with a shift table

    shiftPK..Weeks
    dd....7
    ni.....5
    TM...16

    So say week zero is Sunday 1st Jan and the week you want to look at is 26th Feb

    to determine the right pattern

    datediff("ww","#01/01/2017#,"02/26/2017#)

    gives you 8 weeks

    mod it by the shift weeks (5,7 or 16) will give you the weeknum to look up e.g.

    SELECT *
    FROM Pattern INNER JOIN Shift ON ShiftFK =ShiftPK
    WHERE Weeknum=datediff("ww","#01/01/2017#,"02/26/2017#) MOD Weeks
    will give you 1 for dd, 3 for ni and 8 for TM

    In fact you don't even need the shift table

    SELECT *
    FROM Pattern INNER JOIN (SELECT ShiftFK, Count(*) AS Weeks FROM Pattern GROUP BY ShiftFK) Q ON Pattern.ShiftFK=Q.shiftFK
    WHERE Weeknum=datediff("ww","#01/01/2017#,"02/26/2017#) MOD Weeks

    if anyone changes shift? I was thinking along the lines of EmpiD table, change role that is linked to a RoleProfile table (what would you suggest)
    you will need a 'date started' field and perhaps a date finished field, however your current structure will only allow for these being on a Sunday, if they were mid week, it wouldn't work - because the pattern table is not normalised.

  5. #5
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164
    Hi,

    Thanks for your detailed help above, I get some of your logic but not all.
    Forgive me but I am still fairly new SQL Union query's and Code/Mods.

    I know I have some mods and code already but this was kindly shared with myself by another Forum Member Ssanfu.

    I have enclosed a few new screenshots of the new Table reworks but I don't think I am on your thinking path.

    Would you mind over viewing what I have changed and point myself in the right direction please.

    Brilliant help, thanks a lot for taking your time to support, I just wished I understood it all :-)

    Darren
    Attached Thumbnails Attached Thumbnails Pattern Rework.jpg   Shift Table.jpg  

  6. #6
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,264
    table pattern is how I suggested. You can either add another column for the primary key and combine the Weeknum and ShiftFK fields with a no duplicates index or make them primary key.

    Only other point is from the db you uploaded you appear to have two night shifts - same pattern but different hours, one starts and finishes earlier than the other.

    tblShift just needs the 3 records I outlined, no more, no less. Just the three rows (unless there are additional shifts). You could also add another field for a better description.

    modifying my query with your pattern table and my Shift table

    SELECT *
    FROM Pattern INNER JOIN Shift ON Pattern.ShiftFK =tblShift.ShiftPK
    WHERE Weeknum=datediff("ww","#01/01/2017#,#02/26/2017#) MOD Weeks


    replace #02/26/2017# with however you are selecting a date - looks like it may be

    "#" & format(forms!frmMainRota!cboWeekCommencing,"mm/dd/yyyy") & "#"

    where cboWeekCommencing is the name of the control you have in the form top right in your first post where a user can select a date

  7. #7
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,264
    Please find attached your db duly amended.

    I've created two new tables, tblPatterns and tblShifts and there is a query AjaxQry to demonstrated how it works

    Note the commencement date in tblShifts - all set to 1/1/2017 at the moment, but each could be changed to different Sundays if required.

    Note I have not changed any relationships, you are using lookups in tables which confuses things, so will leave that to you
    Attached Files Attached Files

  8. #8
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164
    That's Brilliant Ajax.

    Many thanks, Just got in from work and I'll have look and report back.

    Darren

  9. #9
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164
    That is very close to what I want, the code works a treat


    I did not understand how the query worked so I decided to inspect and try and break it down to make sense of it.


    Starting with the datediff function I looked here: https://www.techonthenet.com/access/...e/datediff.php


    Great source of information that I have quickly bookmarked!


    A quick question of your great DB rework:


    In the ajax query, I get the tables and fields but where does the Q table/Query come from? (I could not work that out)


    Yes I would like to incorporate a Date list of Sunday date drop downs if possible and pass that value into the datediff function from a cbo instead of the user entering the value.


    Lastly, I should have explained how the staff and patterns worked in a Rotation.


    in one complete Rota week for example, we have 7 day staff employees in total, each employee will be on a different pattern for the entire week.


    IE: DD1 is on pattern week 1, DD2 is on pattern week 2, DD3 is on pattern week 3 and so on (All the way to DD7 - Pattern 7)


    The following week, DD1 is on week 2, DD2 is on week 3 etc. etc. Esentiallt they all go through each week pattern and then week 8 repeat the whole process again.


    This also same for the LD1 and nid1, Patterns 1 to 5, LD2 and nid patterns 1 to 5 etc. etc.

    Now then, How does that lot fit to work the code magic?

    Thanks for the help so far and I have attached the 4 on and 4 off rota which will hopefully explain this better to you.

    Cheers

    Darren
    Attached Files Attached Files

  10. #10
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,264
    where does the Q table/Query come from?
    it is called a sub query. You could just take the sql between the brackets and put into another query, then use in the query provided instead. it simply counts the number of weeks in the pattern 'cycle', So if you decided that you needed to change the cycle and add or reduce a week or two, it will compensate automatically

    each employee will be on a different pattern for the entire week.
    in that case include a field in the staff table to indicate the offset - staff A is week 1 - so offset is 0, staff B is on the next pattern, so offset is 1, etc

    obviously you would need to add the staff table to the query to pick up the offset value

    Your question was about how to combine all rota's into one, which I have shown you how. The rest is just maths to show the right pattern.

    As mentioned before, be aware your pattern data is not normalised and is designed the 'excel' way (mixing storage and presentation together) so expect to get problems further down the line. Problems may not be insurmountable, but will require complex solutions.

    Good luck with your project.

  11. #11
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,264
    Just to complete the work, see attached. two new tables, tblStaff and tblSundays and query modified to incorporate them. Give you the rota for all weeks listed in tblSundays.
    Attached Files Attached Files

  12. #12
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164
    That's Fantastic Ajax, many thanks for your knowledge and support with this project!

    So from your last revamp its just a case of aligning the patterns and offsetting the employee's to the correct week.

    It has been a very valuable learning journey for myself and some brilliant tips picked up.

    One final question, I am right in thinking, to normalize the shift table, I would have a few new tables, Shit time ID, ID1 = 06:00 ID2 = 17:00 ID10 - Days etc and also a table for the days of week etc.

    Basically I'm asking what would you do to make the Pattern table better in a more normalized state as a professional programmer.

    Thanks again

    Darren

  13. #13
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,264
    Pattern table should be something like this for the LD shift - you only include records for the days they are working

    tblPatterns
    PatternPK......ShiftFK...DayNum...DayDescFK
    autonumber...long.......Integer.....Long
    1..................2...........3..............2
    2..................2...........4..............2
    3..................2...........5..............2
    4..................2...........6..............2
    5..................2...........9..............2
    6..................2...........10............2
    7..................2...........11............2
    8..................2...........12............2
    9..................2...........15............2
    10................2...........16............2
    11................2...........16............2
    12................2...........17............2
    13................2...........21............2
    14................2...........16............3
    ...
    ...

    tblDayDescs
    DayDescPK...DayDesc
    Autonumber..Text
    1.................06:00-18:00
    2.................17:00-05:00
    3.................18:00-04:30

    the query would be changed to a crosstab to display Sun-Sat in a row

    depends on where you are going for day descs - does this need to be a separate columns for Start and Finish to cater for half days/late starts etc.

    or instead of having the tblDayDescs table (and daydescFK field in tblPatterns) you could put the daydesc in your tblShifts table and have a separate table for 'exceptions'

    tblDayExceptions
    DayExceptionPK autonnumber
    ShiftFK long (e.g. 3)
    WDay integer (e.g. 7 for Saturdays)
    ExcDesc...text (e.g. 18:00-04:30)

    which you would link with a left join from tblPatterns (logic being if wday(date being looked at) exists use ExcDesc value, otherwise use daydesc in tblShifts

    For employees you would also need a table for exceptions - eg. on holiday, LTS, etc and this is the one you would edit to get the 'modified picture'

    Similarly you may need another table for shutdowns/bank holidays etc - this may need to include a 'shift left' offset if everyone's shifts are delayed a day/week etc. All depends on your rules.

  14. #14
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164
    Thanks for helping to explain the table normalize process for patterns, I get that now a lot better.

    Good explanations and examples.

    cheers for your help.

    Before you posted that Q SQL query into the main query 'Ajax'

    What was the design of it please, IE:

    SELECT *
    FROM Pattern INNER JOIN Shift ON Pattern.ShiftFK =tblShift.ShiftPK
    WHERE Weeknum=datediff("ww","#01/01/2017#,#02/26/2017#) MOD Weeks

    Thanks

    Darren

  15. #15
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,264
    Before you posted that Q SQL query into the main query 'Ajax'

    What was the design of it please, IE:
    I don't understand

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. I need help to relationship in Table
    By Mehvan in forum Database Design
    Replies: 2
    Last Post: 09-25-2016, 06:06 AM
  2. Which table should have this relationship?
    By MickFlanagen in forum Database Design
    Replies: 14
    Last Post: 05-16-2013, 02:18 PM
  3. Table Relationship Help
    By JoshLewis in forum Database Design
    Replies: 1
    Last Post: 04-08-2013, 10:55 AM
  4. Replies: 2
    Last Post: 04-08-2012, 03:04 PM
  5. Relationship Table Help
    By Nick F in forum Database Design
    Replies: 5
    Last Post: 10-10-2011, 01:28 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 - Senior Forums