Results 1 to 13 of 13
  1. #1
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164

    Query missing certain records from 5 tables? (Scratching my head to understand why)

    Hi,



    I have built a database for a 16 week rotation pattern for 4 Team Managers and 4 Key Operatives.

    Each Team Manager has a key operative that works on the same pattern as them.

    The shift pattern is: 4 days on, 4 off, 4 nights on and 4 off .

    My calculations are that it runs for 16 weeks then it goes back to week 1 and repeats the whole process again (16 week cycle)

    My tables consist of: W/C Sunday Dates for a year or 2, Staff Table, Rotation of the pattern vs the week number/W/C and also the shift pattern itself for each week of the 16 week cycle.

    My select query pulls in all the tables and generates a 2 year shift pattern for all to later display in a form to add holidays etc. and also amend SICK/Overtime.

    This will then feed a report.

    I have already achieved this for the Warehouse guys who work 5 week pattern and the days work a 7 week pattern (35 Week Cycle)

    The 16 week query seems to miss all week 1's pattern off all the TMan1 Group for the whole year?? (qry16WeekPattern)


    The same applies for the rest of the groups but for different weeks, TMan2 - Week 9 for the whole year is missing, TMan3 - Week 13 and TMan4 - Week 5.

    I'm totally baffled as to why?

    Can anyone help with please?
    Attached Thumbnails Attached Thumbnails Slide1.JPG   Slide2.JPG   Slide3.JPG   Slide4.JPG  
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Could you post the SQL of the query that has the issue?

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

    Just out at the moment but if it is any use the whole database is attached in the zip file.

    Qry16weekpattern

    Thanks for looking and for your assistance Darren



    Sent from my SM-G935F using Tapatalk

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

    I'm now back home.

    SELECT tblTMRotation.Week, TMWeekDates.[wc date], MrmentStaff.Name, tblTMPattern.SUN, tblTMPattern.MON, tblTMPattern.TUE, tblTMPattern.WED, tblTMPattern.THU, tblTMPattern.FRI, tblTMPattern.SAT
    FROM (([section] INNER JOIN MrmentStaff ON section.Section = MrmentStaff.Section) INNER JOIN (tblTMPattern INNER JOIN tblTMRotation ON tblTMPattern.ID = tblTMRotation.Pattern) ON MrmentStaff.Group = tblTMRotation.Group) INNER JOIN TMWeekDates ON tblTMRotation.Week = TMWeekDates.[week number];

  5. #5
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164
    Click image for larger version. 

Name:	Slide3.JPG 
Views:	21 
Size:	123.2 KB 
ID:	26905Click image for larger version. 

Name:	Slide2.JPG 
Views:	21 
Size:	184.3 KB 
ID:	26906

  6. #6
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Hi Darren, Hopefully someone with more knowledge than myself helps you. If not ill download it and take a look tomorrow.

  7. #7
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164
    thanks Andy

    In the meantime I have started to look at my tables and relationship to see if it is Normalized in a good state.

    I'm also thinking a multi Query might be the answer.

    thanks Darren

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    What is W/C (as in W/C Sunday Dates)?


    Just a few observations: In table "MrmentStaff" the 3 fields "Name", "Group" & "Section" are all reserved words. You really need to change these names, even if it is only "txtName" (a poor name - sorry). A better name might be "EmpName" - but if you are going to enter first and last names, you should have 2 fields:"FName" and "LName" or "FirstName" and "LastName".

    Don't use spaces in object name!

    In table "tblTMPattern", field "ID" (poor name - I changed it to "PatternID_PK") is a text field.
    The data is "TM1, TM2, ...,TM16.
    Being text, it sorts differently: TM1, TM10, TM11, TM12, TM13, TM14, TM15, TM16, TM2, ect.....

    You should have zeros in the number part: TM01, TM02, TM03, ... you get the idea.


    When I execute the query "qry16WeekPattern", I get:
    Week_FK
    Group_FK Name Section wcDate
    (mm/dd/yyyy)
    SUN MON TUE WED THU FRI SAT
    1 TMan1 Bob Team Manager 01/01/2017 Days
    Days Days Days OFF OFF OFF
    2 TMan1 Bob Team Manager 01/08/2017 OFF Nights
    Nights Nights Nights OFF OFF
    3 TMan1 Bob Team Manager 01/15/2017 OFF OFF Days
    Days Days Days OFF
    4 TMan1 Bob Team Manager 01/22/2017 OFF OFF OFF Nights
    Nights Nights Nights
    5 TMan1 Bob Team Manager 01/29/2017 OFF OFF OFF OFF Days
    Days Days
    6 TMan1 Bob Team Manager 02/05/2017 Days OFF OFF OFF OFF Nights
    Nights
    7 TMan1 Bob Team Manager 02/12/2017 Nights
    Nights OFF OFF OFF OFF Days
    8 TMan1 Bob Team Manager 02/19/2017 Days
    Days Days OFF OFF OFF OFF
    9 TMan1 Bob Team Manager 02/26/2017 Nights
    Nights Nights Nights OFF OFF OFF
    10 TMan1 Bob Team Manager 03/05/2017 OFF Days
    Days Days Days OFF OFF
    11 TMan1 Bob Team Manager 03/12/2017 OFF OFF Nights
    Nights Nights Nights OFF
    12 TMan1 Bob Team Manager 03/19/2017 OFF OFF OFF Days
    Days Days Days
    13 TMan1 Bob Team Manager 03/26/2017 OFF OFF OFF OFF Nights
    Nights Nights
    14 TMan1 Bob Team Manager 04/02/2017 Nights OFF OFF OFF OFF Days
    Days
    15 TMan1 Bob Team Manager 04/09/2017 Days
    Days OFF OFF OFF OFF Nights
    16 TMan1 Bob Team Manager 04/16/2017 Nights
    Nights Nights OFF OFF OFF OFF

    Is this right? The pattern seems correct?

  9. #9
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Im getting the same as Darren. Bob doesn't have week_FK 1 In my results

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

    Thanks again for your feedback, I will try the great suggestions that have been provided by Ssanfu and re run the query later tonight.

    I'll report back my findings.

    Thanks

    Darren

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

    Just to report back, that has sorted it!! Nice one!

    I can now stop ripping hair out (Not got that much rip out anyway)

    All weeks in the pattern are now falling in the correct places.

    As suggested I renamed the ID fields and reserved names, good advice.

    I also incorporated your VBA code for the dates (Rota Ver 2)

    A combination of both seems to have done the trick

    Once again, a big thanks for your help.

    Cheers

    Darren

  12. #12
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Wonderful! Ready to mark this solved??

  13. #13
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164
    Apologies, I forgot!

    I have now marked it solved.

    Cheers

    Darren

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

Similar Threads

  1. Just cannot understand lookup tables?
    By dominover in forum Access
    Replies: 7
    Last Post: 03-04-2016, 08:39 AM
  2. Missing records when query runs
    By SydB in forum Queries
    Replies: 6
    Last Post: 06-28-2014, 01:43 PM
  3. Replies: 1
    Last Post: 08-13-2013, 02:09 PM
  4. In over my head with Linked Tables!!
    By Chedmeister in forum Access
    Replies: 6
    Last Post: 01-31-2012, 03:04 AM
  5. Replies: 3
    Last Post: 01-05-2012, 12:04 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