Results 1 to 10 of 10
  1. #1
    DanM89 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2016
    Posts
    4

    Multiple one-to-many relationships on a single form

    I'm building a database to carry out a process called (P)FMEA - a quality procedure in the automotive industry.



    Within an (P)FMEA, you look at possible problems in a process and break them down into possible causes and possible solutions, so I have ended up with a data structure to match this, i.e. a string of 5 one-to-many relationships:

    A Process (tblProcess) contains many Steps (tblFMEAProcessSteps).
    A Step contains many Requirements (tblFMEARequirements).
    A Requirement contains many Potential Failure Modes (tbFMEAlPotentialFailureModes).
    A Potential Failure Mode contains many Potential Causes (tblFMEAPotentialCauses).
    A Potential Cause contains many Recommended Actions (tblFMEARecommendedActions).

    When working in Excel, in the Process column, you combine the cells to match the row with its corresponding Steps and so on through the relationship, so that if you have 30 recommended actions related to a Process, the cell with the Process in is 30 rows.

    Is there a way to do this in a form? If not, is there another way to do this?

    Ideally, I would have multiple layers of sub-forms to achieve this, but you cannot have a subform in a subform, so I cannot achieve it that way.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    but you cannot have a subform in a subform
    you can - I believe the limit is 7. You can also have subforms in continuous forms (not datasheets), but can be a bit cumbersome to use.

    Depends on the view you are trying to achieve - you want to see all 30 recommended actions at the same time? have a subform with a multi table recordsource. You want to be able to maintain them? use cascading listboxes/combos, or perhaps a combination of the two

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Start by getting your tables and relationships designed (and tested) to meet your business rules. You seem to have the rules identified. Can you show us a jpg of your tables and relationships?
    Save building forms until you are certain that your tables/relationships are correct.

  4. #4
    DanM89 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2016
    Posts
    4
    Ajax,

    Thank you for the help; the relationships are shown in the attached.

    I was writing the first comment from memory, so some of the names are slightly different (for example process is also known as APQP and NPI).

    There is a relationship running off to the top-left that is not related to the FMEA process I'm working on at the moment.

    I hope this helps.

    Quote Originally Posted by orange View Post
    Start by getting your tables and relationships designed (and tested) to meet your business rules. You seem to have the rules identified. Can you show us a jpg of your tables and relationships?
    Save building forms until you are certain that your tables/relationships are correct.
    Attached Thumbnails Attached Thumbnails Reltationships.jpg  

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    a couple of points:

    rather than using the meaningless fieldname 'ID', give it a meaningful one such as NPIID, StepID etc, and either call it the same in your family key field or as I do, use the suffix PK instead of ID for the primary key and FK suffix for the foreign key so you know which side of the relationship it relates to. This will also help clarify the fact that ProcessStep in tblProcessSteps is text and in tblRequirement it is a number - kind of indicates you are using lookups in tblRequirements which will only come back to bite you in the future. If you are using them, remove them.

    Also you should not start field names with a number as you have for field names like '1_1_2...' or use non alphanumeric characters such as in Control(s)Dete....

    having file names such as Severity and Severity2 or Occurrence and Occurrence2 implies data is not normalised so need to be clarified

    You haven't explained how you want to use them and without some example data, difficult to advise any more than I already have.

  6. #6
    DanM89 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2016
    Posts
    4
    Thanks for the advice on the field names; I'll make those changes now.

    To explain Occurrence vs Occurrence 2: When you first start an FMEA, you assess how likely an issue is to occur (1-10 scale based on an internationally accepted definition). Then, you carry out actions to reduce the likelihood of occurrence and re-assess to give it another value (hopefully lower). So Occurrence is the likelihood at the start of the process and Occurrence2 is the likelihood of an issue as assessed right now. Does this explain the data structure for this?

    I have attached the template for a FMEA made in excel; it shows the relationships between records by merging the row of the "one" side of the relationship over all of the rows for the "many" side of the relationship; this is what I would like to achieve with the database.

    P.S. RPN is a calculated value (SEV*OCC*DET) that I will add in the query.
    Attached Thumbnails Attached Thumbnails FMEA Example.jpg  

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    much clearer thank you

    So back to the original question - are you trying to create a form in excel that looks like your attachment? Excel lends itself to presenting data horizontally, whilst databases present vertically. So not sure it can be done.

    Going vertically, you could have a main form for the first 3 columns, a subform for the next 7 columns which in turn has a subform for the last columns - clicking on one of the rows in the first subform would refresh the last subform, so in a form you would not be able to see all the data in one go.

    To see all the data, you would need to build a report - and a report can be displayed as a subform in a form, but you cannot edit the data in a report.

    As I've asked before - depends on what you are trying to do. Excel combines data storage and presentation in one view whilst databases store data in tables and use queries, forms and reports for presentation.

  8. #8
    DanM89 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2016
    Posts
    4
    I'm leaning towards Access, because the data needs to be presented in a number of different formats and reports, not just this one. The other formats are a lot simpler (for instance, a report showing all recommended actions by failure mode and a report showing all open recommended actions by project and or by assigned person).

    Unfortunately, what wasn't clear in the example spreadsheet was that you can have multiple Requirements for each Process Step and multiple Potential Failure Modes for each Requirement, taking me to four nested subforms, which Access will not do (unless there is a way to do this that I don't know).

    Was subreport in a form implemented in 2010, as I thought 2007 didn't have this option; if I'm wrong, do you know how to add a subreport to a form?

    My best attempt so far has been a stepped report with buttons that link to forms for adding and editing records at each level. It works, but it looks really odd (see Attached).
    Attached Thumbnails Attached Thumbnails rDFMEA.jpg  

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Was subreport in a form implemented in 2010, as I thought 2007 didn't have this option; if I'm wrong, do you know how to add a subreport to a form?
    I jumped 2007 when moving from 2003 to 2010 so don't know. In 2010, just drag your report on to the form (for continuous forms it needs to go in the footer or header and although access will change it to a single form, you can change it back again)

    As I said, consider changing the orientation from horizontal to vertical.

  10. #10
    tmd63 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Oct 2016
    Location
    UK
    Posts
    16
    I would also add that coding conventions for naming are a great help when looking at field names. dtm = date time, lng = long number, str = string text, int = interger number, dbl = double long number, obj = object and lng<tablename>ID for the autonumbered primary key (ie lngtblNPIID) this then helps with the table it relates to and reminds you it is a number and the ID says it is a primary key.
    see http://access.mvps.org/access/general/gen0012.htm

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

Similar Threads

  1. Replies: 5
    Last Post: 09-29-2015, 11:08 AM
  2. Replies: 9
    Last Post: 02-04-2015, 03:35 PM
  3. single form multiple database
    By sms2720 in forum Programming
    Replies: 7
    Last Post: 12-14-2011, 01:26 PM
  4. Creating multiple records from a single form
    By secretary in forum Forms
    Replies: 8
    Last Post: 07-18-2011, 04:03 PM
  5. Single Form, Multiple Options
    By forrestapi in forum Forms
    Replies: 4
    Last Post: 06-30-2011, 07:09 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