Results 1 to 5 of 5
  1. #1
    carahorse is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    2

    Database Relationship for Equipment Maintenance

    Hi All, thanks in advance

    I maintain military equipment with inspections coming up requiring printed forms.

    tblPersonnel
    tblEquipment
    tblComponents
    tblInspections
    tblCatalog

    One person has many equipment. Each one (tblequipment)equipment has many (tblInspections)
    has many (tblComponents)
    tblCatalog is simply a reference pick list, that holds part numbers to describe the components and equipment (price, location, ect)

    What is getting me is trying to pass information through subforms

    PersonID 1
    has equipements ID 18, 19, 20


    each has inspections

    it's passing personID beautifully through all forms/relationships, but I'm having to manually enter the EquipmentID, which is a bummer and besides the point of a database.
    Any Ideas? Missing keys, relationships?
    https://goo.gl/photos/RtWK8yz8ufR38Ahr7
    https://goo.gl/photos/32zGxUi3ojsrso7a9

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    cross posted here https://www.access-programmers.co.uk...d.php?t=293305

    @carahorse - please see this link about crossposting http://www.excelguru.ca/content.php?184

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    Hi

    Do you want a personell database or equipment database? Having accent on one or other can influence the optimal design.

    My advice is to build your app based on equipment. So the base form is equipment register (single form based on equipment table).
    To make some operations like making inspections easier, add to equipment table field CurrentUser
    Add a multipage tab control to equipment form. On pages, you add subforms where you can attach :
    1. Personell as user (continuous form based on table tblEquipmentUser: EquipmentID, PerconellID, UsingFrom, ...), linked through EquipmentID to Equipment form;
    2. Components (continuous form based on table tblEquipmentComponents: EquipmentID, ComponentID, ...), linked through EquipmentID to Equipment form;
    3. Inspections (continuous form based on table tblInspectionEquipment: InspectionDate, EquipmentID, ...), linked through EquipmentID to Equipment form;

    Now create an unbound Main form with multipage tab control.
    Add your Personell form as subform into 1st page.
    In other pages of Main form, add subforms where you register personell and inspections.
    To make inspection easier, you can have on single Inspections form (InspectionDate, ...) a continuous subform, based on tblInspectionEquipment, linked through InspectionDate to Inspections form - so you can register existence of equipment in one continuous form.

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

    See this free, generic data model for equipment maintenance from Barry Williams' site.

    Get your tables and relationships designed and tested before getting too deeply into physical Access.

    See this link for database planning and design

  5. #5
    carahorse is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    2
    That does help! Thank you. Pertaining to the tblEquipment, I want to know what kind of equipment the person has checked out(not just a serial no.). Will it cause problems if the tblEquipment and the tblComponents references the tblCatalog to find out what kind of equipment and components are checked out to a person? Or would I need to create 2 tblcatalog lists? --Which would not seem very efficient? since both of the federal stock numbers to describe the equipment and components are the same and will also be a benchstock list at some point.

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

Similar Threads

  1. Replies: 4
    Last Post: 08-08-2016, 12:28 PM
  2. Replies: 1
    Last Post: 04-01-2014, 10:54 AM
  3. Replies: 2
    Last Post: 02-13-2014, 04:59 PM
  4. Need design help on maintenance database
    By creyc in forum Database Design
    Replies: 7
    Last Post: 03-29-2013, 03:51 PM
  5. Maintenance Database
    By shariq1989 in forum Database Design
    Replies: 1
    Last Post: 06-28-2012, 04:55 PM

Tags for this Thread

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