Results 1 to 12 of 12

Tables and Relationships

  1. #1
    egnsantiago's Avatar
    egnsantiago is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2019
    Posts
    9

    Tables and Relationships

    Hi all,

    I'm trying to created a db to manage component obsolescence. I already create a few tables and forms but before I continue I would like know if I'm on the right path or if I need to change, split or add more tables. In a nutshell, here is how I want the database to work:

    *An Enterprise Part Number (EPN) may have 1 or more Manufacturer Parts Number (MPN). The component database will have one line item (row) for each MPN.
    *When all MPNs for the same EPN are obsolete, an obsolescence case needs to created for all the MPNs that are obsolete (same case ID).


    *If a an alternate part is found, the new MPN is added to the component database as a new line item for the same EPN and the case is closed. The new line item can't be related to the obsolescence case. When the new part goes obsolete, a new obsolescence case shall be generate for the new part only.

    Here is how the relationship looks at this time. Am I on the right path?
    Click image for larger version. 

Name:	relationship1.PNG 
Views:	49 
Size:	22.9 KB 
ID:	37388

  2. #2
    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
    14,016
    You should not have 2 objects in your database with the same name.
    eg table ObsoCases and form ObsoCases

    Field names should not have embedded spaces( eg CAGE code) nor special characters (eg DB_ID#).
    Uses alpha and numeric chars only, don't start with a number.

    For learning the essence of tables and relationships, spend 45 minutes and work through this tutorial from RogersAccessLibrary. You will learn by doing the exercise; and what you learn can be used with any database.

    See the links in my signature for other reference material.
    Good luck.

  3. #3
    Join Date
    Apr 2017
    Posts
    923
    For start little nit-picking! You have components table, not components database in your relationships scheme!

    Instead of obsolete cases table, I think enterprise parts table is better solution.

    tblEnterpriseParts: EP_ID, EPN, EP_Status, ...;
    tblManufacturers: Manuf_ID, CAGE, WebPage, ...;
    tblManufacturerParts: MP_ID, MPN, EP_ID, MP_Status, ... (As in your case any manufacturer part is registered with only one enterprise part, you can do with single table here - no need for additional junction table.)

    You'll have forms:
    fManufacturers (it is what I call registry form - used by user only when some manufacturer info is added or updated
    fEnterpriseParts (a single form)
    fManufacturerParts (The best solution will be to have this form as linked continuous subform in fEnterpriseParts. So when you select an enterprise part in fEnterpriseParts, a full list of manufacturer parts is displayed in subform. And whenever you add a new manufacturer part into subform, it is automatically linked with active enterprise part in fEntwerpriseParts.)

    For fManufaturerParts form, you need some event (e.g. AfterUpdate for control linked to MP_Status field), which checks number of parts in tblManufacturerParts with same EP_Id and obsolete against number of parts in tblManufacturerParts with same EP_Id whenever the status of active manufacturer part is changed. When both counts are equal, EP_Status for according EP_ID in tblEnterpriseParts is set to obsolete, otherwise not obsolete.

    You also can add an open event to main form (or run some scheduled task at time when nobody uses the database) which recalculates the EP_Status for all entries in tblEnterpriseParts - for case the form event for specific MP_ID didn't work properly for some unexpected reason.

    Edit: No need to have manufacturer name in any other tables except tblManufacturers. I forms you can use combo box linked to Manuf_ID - the users sees manufacturer name instead.

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,258
    From your field name 'manufacturer' I suspect you are using lookups in your table design. Just because you can, does not make it a good idea.

    Perhaps it is not necessary but it looks to me like your 'component database' table should be showing a many to many relationship with an EPN table which I suggest would among other things contain your name for the item. (Much as Arvil has suggested). And then the relevant fields in 'ObsoCases' can be included in the 'component database' table.

    Don't know what CAGE Code is, if a location of the EPN, should probably be part of the 'component database' table

  5. #5
    egnsantiago's Avatar
    egnsantiago is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2019
    Posts
    9
    Hi all,

    How is this looking now? The only reason I have "Status" column on "tblEnterpriseParts" is because I thinking on adding a code to show the status of the MPN with highest LC_Code. Should I do that on a Form instead?

    Click image for larger version. 

Name:	relationship2.PNG 
Views:	36 
Size:	16.6 KB 
ID:	37435

  6. #6
    Join Date
    Apr 2017
    Posts
    923
    Quote Originally Posted by egnsantiago View Post
    The only reason I have "Status" column on "tblEnterpriseParts" is because I thinking on adding a code to show the status of the MPN with highest LC_Code. Should I do that on a Form instead?
    To show users tables only adds confusion for them. To allow them to edit tables directly will be a reason for lot of problems in future. A reasonable approach is hide the object pane from users entirely, and let them interact only through forms.
    You can show on forms anything you are able to read from tables, or calculate - so yes, you can display e.g. MPN with highest LC_Code in enterprise part form.

    In my previous post, I advised for Status field in tblEnterpriseParts a different role - it will indicate, has the enterprise part at least one not obsolete manufacturer part listed or not. Belive me, this advice was for reason! It is easiest way to discover obsolete enterprise parts, and to filter them out in any queries you are using in your application.

    From your picture I see, that you mean to register an obsolescence case, and the link it with tblManufacturerParts through CaseID. No need for CaseID in tblManufacturerParts. Instead you add an FK field MPN_ID into tblObsolescenceCases. When you have on form fEnterpriseParts a continuous subform with fManufacturerParts as source, you can have a hidden unbound control on fEnterpriseParts where MPN_ID for active manufacturer part in subform is written by Current event of fManufacturerParts. And then you can have another subform in fEnterpriseParts, linked with unbound control in parent form, where the info from tblObsolescenceCases for active manufacturer part is displayed. You can make this form disabled or invisible, when manufacturer part is not obsolete. When the part was declared obsolete and then not obsolete for several times, you can browse through all obsolescence cases for this part, add a new case, or edit existing one (when having possibility for multiple obsolescence cases for manufacturer part, it will be reasonable to order obsolescence cases in subform by Obso_iD DESC - so current obsolescence case is displayed by default).

  7. #7
    egnsantiago's Avatar
    egnsantiago is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2019
    Posts
    9
    Quote Originally Posted by ArviLaanemets View Post
    To show users tables only adds confusion for them. To allow them to edit tables directly will be a reason for lot of problems in future. A reasonable approach is hide the object pane from users entirely, and let them interact only through forms.
    You can show on forms anything you are able to read from tables, or calculate - so yes, you can display e.g. MPN with highest LC_Code in enterprise part form.

    In my previous post, I advised for Status field in tblEnterpriseParts a different role - it will indicate, has the enterprise part at least one not obsolete manufacturer part listed or not. Belive me, this advice was for reason! It is easiest way to discover obsolete enterprise parts, and to filter them out in any queries you are using in your application.

    From your picture I see, that you mean to register an obsolescence case, and the link it with tblManufacturerParts through CaseID. No need for CaseID in tblManufacturerParts. Instead you add an FK field MPN_ID into tblObsolescenceCases. When you have on form fEnterpriseParts a continuous subform with fManufacturerParts as source, you can have a hidden unbound control on fEnterpriseParts where MPN_ID for active manufacturer part in subform is written by Current event of fManufacturerParts. And then you can have another subform in fEnterpriseParts, linked with unbound control in parent form, where the info from tblObsolescenceCases for active manufacturer part is displayed. You can make this form disabled or invisible, when manufacturer part is not obsolete. When the part was declared obsolete and then not obsolete for several times, you can browse through all obsolescence cases for this part, add a new case, or edit existing one (when having possibility for multiple obsolescence cases for manufacturer part, it will be reasonable to order obsolescence cases in subform by Obso_iD DESC - so current obsolescence case is displayed by default).
    Man, this is a lot for me to digest but here we go.
    Quote Originally Posted by ArviLaanemets View Post
    To show users tables only adds confusion for them. To allow them to edit tables directly will be a reason for lot of problems in future. A reasonable approach is hide the object pane from users entirely, and let them interact only through forms.
    You can show on forms anything you are able to read from tables, or calculate - so yes, you can display e.g. MPN with highest LC_Code in enterprise part form.

    In my previous post, I advised for Status field in tblEnterpriseParts a different role - it will indicate, has the enterprise part at least one not obsolete manufacturer part listed or not. Belive me, this advice was for reason! It is easiest way to discover obsolete enterprise parts, and to filter them out in any queries you are using in your application.
    Yes, my plan is to later add forms. In regards to the Status column, I renamed it as EPN_Status avoid confusion. This is going to be a conditional field that will indicate Obsolete only when all the MPNs related to the EPN are obsolete. Is this OK?

    Quote Originally Posted by ArviLaanemets View Post
    From your picture I see, that you mean to register an obsolescence case, and the link it with tblManufacturerParts through CaseID. No need for CaseID in tblManufacturerParts. Instead you add an FK field MPN_ID into tblObsolescenceCases.
    I added the CaseID in tblManufacturerParts because a CaseID may be related to multiple MPNs. Would I still be able relate do that if I add the FK field MPN_ID into tblObsolescenceCases?

    Quote Originally Posted by ArviLaanemets View Post
    When you have on form fEnterpriseParts a continuous subform with fManufacturerParts as source, you can have a hidden unbound control on fEnterpriseParts where MPN_ID for active manufacturer part in subform is written by Current event of fManufacturerParts. And then you can have another subform in fEnterpriseParts, linked with unbound control in parent form, where the info from tblObsolescenceCases for active manufacturer part is displayed. You can make this form disabled or invisible, when manufacturer part is not obsolete. When the part was declared obsolete and then not obsolete for several times, you can browse through all obsolescence cases for this part, add a new case, or edit existing one (when having possibility for multiple obsolescence cases for manufacturer part, it will be reasonable to order obsolescence cases in subform by Obso_iD DESC - so current obsolescence case is displayed by default).
    This would absolutely be AWESOME! But before accepting this challenge, do I need to change anything else on the tables?

    tblEnterpriseParts
    tblManufacturerParts tblManufacturers tblObsolescenceCases
    EPN EPN MFR_Name Obso_ID
    EPN_Description MPN CAGE_Code CaseId
    EPN_Status MFR Webpage Task
    MFR_Description Case_Status
    Status Comments
    LC_Code Resolution
    MPN_ID D_Created
    D_Closed
    MPN_ID

  8. #8
    Join Date
    Apr 2017
    Posts
    923
    Quote Originally Posted by egnsantiago View Post
    In regards to the Status column, I renamed it as EPN_Status avoid confusion. This is going to be a conditional field that will indicate Obsolete only when all the MPNs related to the EPN are obsolete. Is this OK?
    It's OK. And you can do same with other statuses too - it will be easier for you later to write VBA code when you know exactly to which status you refer without checking the tables.

    Quote Originally Posted by egnsantiago View Post
    I added the CaseID in tblManufacturerParts because a CaseID may be related to multiple MPNs. Would I still be able relate do that if I add the FK field MPN_ID into tblObsolescenceCases?
    At start you must be clear, what will be the real procedure to register a manufacturer part as obsolete (and to remove the obsolescence, when this is allowed).
    a) The part is set as obsolete (or obsolete part is set as active). There may be added some additional info like date from which the part is obsolete, who made the decision, etc. With this procedure, you need a table, let's have a name tblMPInfo, with autonumeric PK MPI_ID, MP_ID as FK, and needed fields for info. User simply changes the status of part, and in linked form adds additional info;
    b) There is a need to set one or several parts as obsolete (or set obsolete parts as active again). To decide, do this or not, some procedure is started and some decision is made. Based on this decision, a part (or some parts) are set obsolete or active. You need a separate table where you register all info about this decision, let's it be tblStatusDecision with autonumeric PK SD_ID, and needed fields for info. And you need a table, where you connect all manufacturer parts with this decision (a junction table), let's it be tblMPSD, with autonumeric PK MPSD_ID, and MP_ID and SD_ID both as FK's. When the decision is made, user changes part(s) status(es) according decision (this is easiest way to manage this), and in separate form based on tblStatusDesicion and with linked subform based on tblMPSD, links all involved parts with current status decision;
    c) The combination of a and b. SD_ID is added as FK into MPInfo, i.e. it is used to group additional part info with some official decision. User waits for decision, registers it, sets the satus(es) for every part involved, enters additional info for every part, and for every part involved links this info with decision.

    Quote Originally Posted by egnsantiago View Post
    ...do I need to change anything else on the tables?
    Add autonumeric id for every table, and use them as FK's in other tables. Your app will be faster - especially when tables have more rows. When you need to ensure any other fields to be unique and always filled, then define it as an unique index - i.e. for tblEnterprises have EP_ID and PK, and EPN as indexed unique field. And when you have id from another table as FK, there is no need for another info (like manufacturer description in tblManufacturerParts) from linked table added.

  9. #9
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,258
    This is going to be a conditional field that will indicate Obsolete only when all the MPNs related to the EPN are obsolete. Is this OK?
    To add to Avril's comments, this is a calculated value (it should be a simple count). So does not need to be stored - unless you want to make it obsolete regardless of whether you still have manufacturers for the item. If you do need to keep it, rather than using a 'yes/no' flag, use a date field instead. If not obsolete, the field will be null.

  10. #10
    Join Date
    Apr 2017
    Posts
    923
    Quote Originally Posted by Ajax View Post
    So does not need to be stored - unless you want to make it obsolete regardless of whether you still have manufacturers for the item. If you do need to keep it, rather than using a 'yes/no' flag, use a date field instead.
    In this case having a calculated value to be stored into field may be useful, as probably there will be need for queries with current status in WHERE clause. Having several manufacturer parts set obsolete/active at different times can make such queries cumbersome.

    I myself am trying to avoid Boolean fields too. Usually I use smallint values like 0 and 1 instead. On forms, I use then combo boxes to get Yes/No or TRUE/FALSE displayed.

  11. #11
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,258
    Usually I use smallint values like 0 and 1 instead
    in Boolean logic, 0 = false, anything else (except null) is true. so you could set an unbound checkbox control to

    =dcount("*","childTable","FK"=" & me.PK)

    which will show ticked if child records exist in a related table

    the criteria can of course be more complex.

    I still maintain don't store a value if it is just a calculation - if it has another purpose (such a obsolete even if it can still be obtained) then that is another matter - you could use the dcount then as a validation, i.e. highlight records where an item cannot be obtained, but not flagged as obsolete. Or perhaps highlights records where marked as obsolete, but saleable stock is still held

    As to smallint/Boolean, yes I do the same when appropriate but depends what other systems I am working with (Sql server true is 1, not -1 for example). And in many cases then 'when' is just as important, hence use a date

  12. #12
    egnsantiago's Avatar
    egnsantiago is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2019
    Posts
    9
    Let me explain the obsolescence process with more detail. Lets say I have an EPN and a few MPNs for the same EPN. The EPN is an internal part number, the MPN is the PN assigned by the manufacturer. In this example, I have 3 equivalent parts from 3 different manufacturers and I can use any of this parts on the application.
    EPN MPN MFR Status
    25002341-001 JK367TPA XXXX Active
    25002341-001 LT46G32 YYYY Active
    25002341-001 SM1.26BT ZZZZ Active








    If two of the MPNs go obsolete, the EPN still remains active because one supplier is still manufacturing the part, but when all the MPNs go obsolete, then the EPN is obsolete. As a result, I need to generate a case number and link the three MPNs to this CaseId. If I find a new supplier for the same part, then I add a new line to the table and the EPN is active again. If not, the case is closed and the EPN remains Obsolete. All the case details are documented in the ObsolescenceCase for future reference. In the event the new MPN goes obsolete, a new case is generated, but the new CaseID is linked only to that MPN, not to the previous 3 MPNs.
    EPN MPN MFR Status
    25002341-001 JK367TPA XXXX Obsolete
    25002341-001 LT46G32 YYYY Obsolete
    25002341-001 SM1.26BT ZZZZ Obsolete
    25002341-001 MM-PT-456 AAAA Active









    I also have EPN Description and MPN Description because sometimes the MPNs are modified resulting in a need to change the Description.
    I will Add autonumeric id for every table, and use them as FK's in other tables.

    Since, I'm new to Access, this will probably take me a few days to incorporate all of your recommendations.

    Thanks!

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

Similar Threads

  1. Tables and relationships
    By k11ngy in forum Database Design
    Replies: 10
    Last Post: 01-14-2019, 03:54 AM
  2. Many to Many Relationships among 8 tables
    By Pilotwings_64 in forum Database Design
    Replies: 9
    Last Post: 10-30-2010, 03:12 AM
  3. 8 Tables with Relationships
    By bigdogxv in forum Access
    Replies: 1
    Last Post: 12-09-2009, 09:37 PM
  4. Tables Vs Relationships
    By Bruzer in forum Database Design
    Replies: 8
    Last Post: 09-06-2009, 04:39 PM
  5. I need help on relationships for tables.
    By justin.w in forum Access
    Replies: 0
    Last Post: 10-16-2006, 10:57 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
  •  
Tech Forums: Microsoft Office Forums