Results 1 to 13 of 13
  1. #1
    fnem is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2015
    Posts
    19

    Design a schedule of service events with sub tasks

    I wonder if anyone is able to give me any suggestions regarding the structure of a database I wish to create.

    The database concerns the management and recording of engineering tasks on items of machinery.

    Simplified significantly.


    • There will be a number of identical but unique machines.
    • Each machine will be subject to a schedule of service events.
    • There will be several different types of service event
    • Each service event will consist of a number of tasks.
    • Some tasks will be unique to a particular service, others will associate with various different services.




    I require the user to be able to add service events to a given machine, to be able to log when tasks associated with a service have been completed and to be able to query which tasks for a given service are outstanding.

    I've tried a few approaches but I think I may be over complicating things, this must be a fairly common database problem.

    All help gratefully received

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How about showing us the tables and relationships of your latest database effort?

    Who performs the Service -- or is that not within the scope of your project?

    Some sample data would also be useful.

  3. #3
    fnem is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2015
    Posts
    19
    Well I've deliberately kept data out of it to try and simplify the problem, it's the concept I'm interested in cracking.

    Here's an idea of some of the structure so far, for PM read service

    Click image for larger version. 

Name:	Tables.JPG 
Views:	29 
Size:	29.4 KB 
ID:	24453

    This will allow me to log completed services and query what tasks are associated with a service but not record the tasks completed, that's the bit I'm scratching my head over. How to record what may be a large number of tasks that vary by service type as shown.

    Thanks

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    This is a draft I made based on your post #1. Hope it's helpful.

    Test your model with test data using typical scenarios.

    Good luck.
    Attached Thumbnails Attached Thumbnails MachineServiceTask.jpg  

  5. #5
    fnem is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2015
    Posts
    19
    Thanks very much for your input.

    What you've got there seems to be similar to what I've gone with so far, which is reassuring. Correct me if I'm wrong though but I can't see how you can record which specific tasks have been completed/are outstanding for a given service, only whether the service itself is complete. This is the real issue I'm struggling with, how to efficiently record the completion of tasks associated with a given service.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The intent was to record Scheduled Service and Task in the MachineScheduledServices table and the actual performed Service and Task in MachineActualServices. The latter table is for Completed Services and tasks.
    The Tasks included in a Service can be derived from ServiceHasTask.

    Some realistic test data and scenarios could be used to test the model. As I mentioned, it is a draft based on your initial post.

    I have used a single PK in MachineScheduledServices and would have a unique composite index on the
    MachineID, ProposedServiceDate, ServiceId and TaskId.

    You know which Tasks are part of which Service. You know which Tasks for which Service are scheduled for which Machine. By recording the actual performed Task and Service in MachineActualServices, you know which Task and Service were actually done on which Date and by whom.

    I think the pieces are there, but test data and scenarios would confirm/reject the model. Some adjustments during testing are to be expected.

    Good luck.

  7. #7
    fnem is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2015
    Posts
    19
    Yes I think I follow you. The key thing is to have a table of scheduled service events and use this to populate a table of completed/pending service tasks. I've had a think about it and here's what I've got:

    Click image for larger version. 

Name:	Tables 2.JPG 
Views:	27 
Size:	37.1 KB 
ID:	24466

    The PM Log table is a list of proposed and completed service events, the PM Jobs table is a list of unique tasks, both pending and complete, each linked to a particular service event and populated by querying the PM Tasks table using the PM Type. By Jove I think it might just work. Thanks very much for your help.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Make sure your model works with your test data and meets your requirements before getting too deeply into forms etc.
    Biggest and most basic issue is to get your tables and relationships designed and tested to meet your business requirement.


    I would not have spaces in field names -they will lead to syntax issues.

    Do you have a description of the business issue or an itemized list of requirements?
    Test data?

    Good luck.

  9. #9
    fnem is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2015
    Posts
    19
    Yes I completely agree with your first point, that is my goal at the moment, to get the data structures right.

    Thanks for the tip on field names, have been a bit naughty with that one, will makes some modifications.

    Business has been typically fuzzy with requirements but at least I have some preliminary ideas now to thrash out some more detail. Do have some test data which is good, will start testing with this shortly.

    Cheers

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    fnem,

    Don't know if you're monitoring this but I reread your post and have another model for your consideration.
    Since you need to know if a task is complete, you must record at the service-task level.

    I created a small database 2010(attached zip) with some sample data and queries. It may be helpful, or it may be off-base since I don't know details of your set up nor your data.

    Anyway, if it isn't applicable, just discard it.
    Attached Thumbnails Attached Thumbnails MachineServiceV2.jpg  
    Attached Files Attached Files
    Last edited by orange; 04-28-2016 at 04:16 PM.

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is yet another model dealing with Appointment (supertype) with 2 subtypes (ScheduledAppointment and ActualAppointment).
    As in previous post, this is meant for consideration. If this, with adjustments, doesn't match your requirements,just discard it.
    Attached Thumbnails Attached Thumbnails MachineServiceV3.jpg  

  12. #12
    fnem is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2015
    Posts
    19
    Wow this is some really helpful work and greatly appreciated.

    I've taken a little time to go through your sample db and digest it, I think it is very similar to what I've come up with only a little more refined, this encourages me. Having some sample queries in there is very helpful too. Also gives me a few ideas about creating forms and when and how to populate the respective tables. I think we're on the same page over this one.

    Great work, many thanks.

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    fnem,

    Glad the posts are helpful. The latest model, which does not reflect the sample database I sent, seems more consistent to me than the earlier models. But, as I said, whether any model meets your needs or not, it is the testing of the model with sample data where the rubber meets the road. Test your set up with some sample scenarios before getting too involved in Access. It will pay off.

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

Similar Threads

  1. Replies: 6
    Last Post: 01-26-2015, 02:11 PM
  2. Replies: 5
    Last Post: 07-10-2014, 09:37 AM
  3. How to design table of working-tasks in a meaningful way?
    By ICBSprod in forum Database Design
    Replies: 1
    Last Post: 02-12-2014, 09:49 AM
  4. Replies: 3
    Last Post: 05-23-2013, 05:30 PM
  5. Replies: 1
    Last Post: 06-28-2012, 01:46 AM

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