Results 1 to 5 of 5
  1. #1
    markizan is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2018
    Posts
    2

    Relationship structure - Vendor certs

    I need some assistance with relationship structure for MS Access 2010.

    The purpose / goal / outcome ... is to track vendor certification/acreditation compliance / cert expiry dates

    Key table/data sets
    - Company (includes all vitals and separately links to contacts within company)
    - Product Category (there are approx a dozen of these, but some companies can have more than one)
    - Products (each Product Category can have many Products)
    - Certifications (there are hundreds of certifications/specifications types ... some products have none, whilst others may have one or many)

    End goals:
    - A Form to Select for Companies which Product Categories they have (1 or more)
    - this then cascades to Select the types or Products within each Product Category for that Company (some may have only a few, some may have all)
    - based on the selected Products ... there cascades (automatically) the required Certifications for each Product selected
    - User input fields to enter actual Company certification numbers (or option for N/A) and the cert expiry date for each 'required certificate'


    - Notification can be programmed to alert for expired certificates.

    The purpose of the DB is to:

    - Monitor multiple companies for certification compliance to manufacture products
    - Monitor for expired certs
    -
    Provide other useful searches for compliant Companies by Product categories, Products or Certificate Types.

    Disregard any links shown, these are the tables I created to be linked.

    Click image for larger version. 

Name:	Capture DB Structure.JPG 
Views:	30 
Size:	47.8 KB 
ID:	35077

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Based on my understanding of your description this is what I came up with. (Full disclosure, im just an amateur)

    [deleted pic]

    I'm having a hard time deciding how CompanyCertifications should be related. I'm not a real big fan of the looks of that above.


    ---EDIT

    I like this relation ship design better than what I posted previously

    Click image for larger version. 

Name:	Certs.png 
Views:	31 
Size:	16.9 KB 
ID:	35079

  3. #3
    markizan is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2018
    Posts
    2
    This is a crude mock up of what I'm trying to achieve.

    From the users perspective, there will be already created data on the Vendor company done in seperate CMS forms

    In this screen, they would ideally be able to lookup the existing Vendor and be able to view a full list of 'potential' Product Categories and using check-boxes, select which ones apply to that particular Vendor.
    Note: this list should show all Categories to choose from & grow/shrink as Categories are added/removed from Category Table.

    Based on the Product Category selected (checkbox), this will then populate the related 'potential' Products withing the selected Category/Categories
    Note: this would grow/shrink based on the Product Category checkbox selected & any Products added/removed from Products table.

    Each 'potential & shortlisted' Product displayed will have a check-box to allow the user to choose which Products the Vendor can produce.
    note: this can be ticked on/off by user as the Vendor added/removes capabilities to produce the Products.

    Next using a (yet to be developed) query which associates 'Cert Types with Products', display a summary of the certs the Vendor requires to hold.
    Note: some products may share the same cert, so no need to repeat for each Product.

    Then allow the user to enter in the actual Cert Number & Expiry Date for the Certs Required

    Then a macro or VBA can be used later to compare cert expiry dates with current date to warn of expired certs ... etc.

    There is expected to be approx a dozen Categories; there would be approx 5 - 50 Products per Category; there would be >100 possible Cert Types.
    This tool is to make it much simpler to add new Categories & Products to a Vendor; know which Certs they must have, enter Cert numbers and track expiry dates ... plus give ability to look for all the Vendors for a Category/Product/Cert Type.

    Click image for larger version. 

Name:	Mock up of Vendor DB.jpg 
Views:	29 
Size:	101.5 KB 
ID:	35082

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    You certainly can achieve this functionality w/ the relationships I proposed in post #2. Though it might be simpler to achieve with multiselect listboxes vs subforms w/ checkboxes.

    I see that you are wanting to link a company directly to product categories, then to products. While that logic flows w/ the user input form you've posted I don't think it's appropriate for the actual data storage because you still have to somehow save which individual products a company has, this would create redundant data. That flow would only make sense if a company has ALL the products in a product category. A company's product categories can be looked up indirectly though associated products. This is why I arranged the relationships the way that I did.

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    See attached for example database. Example has NOT been tested for bugs and I make no guarantees.

    Cert.zip

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

Similar Threads

  1. Relationship Structure
    By Ramtrap in forum Database Design
    Replies: 5
    Last Post: 10-26-2017, 01:05 PM
  2. Composite key relationship structure
    By rbennion in forum Database Design
    Replies: 6
    Last Post: 07-07-2017, 09:08 AM
  3. Relationship Structure
    By buckwheat in forum Access
    Replies: 1
    Last Post: 07-12-2013, 01:12 PM
  4. Query relationship structure
    By Juicejam in forum Queries
    Replies: 15
    Last Post: 02-12-2012, 10:26 PM
  5. Structure/Relationship Question? Please Help...
    By Imgsolutions in forum Access
    Replies: 2
    Last Post: 06-27-2010, 09:52 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