Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jun 2022
    Posts
    10

    Form to Show Kit Options

    Hi I work primarily with Medium Voltage (MV) cable and 'm trying to create a tool using MS Access in order to help myself on the engineering side to identify what parts will work with a particular cable and to help the sales team by identifying what kits are available for the cable. The file I have attached, "Splice Body Database" is where I've dumped all my tables so far. Given the volume of data I need to upload though I'll most likely need to separate the component tables from the cable tables into two different files and have the linked due to file size. Before I invest the time into that I want first see if what I'm trying to do is possible and the process / theory on how to do it so I can expand on it. Ok so here goes...

    From a day to day scenario we get requests where someone knows exactly what cable they are using and some that only know some information. For now let's focus on the situation on where they know the cable catalog ID. In my file I created a table "1C JCN Cables" which list some of the cables listed in a catalog sheet. That said not all the dimensions I need are available from the sheets and some calculations are needed. I got around this by utilizing the query I created called "1C JCN Cables Query". The query has all the dimensional / spec parameters neeeded for identifying suitable parts. From there I think it would be best to staty by identifing what splice bodies from the "Splice Body Parameters" table will fit on the cable, followed by what Constant Force Spring (CFS) from the "Constant Force Springs" table will fit with each splice body option, and finally list what Compression and / or Shear Bolt Splice Connectors will work for the application. For now I'd like the form to be able to output all this information into some type of report that can be easily viewed or printed and once I get that to work I can work on adding buttons to help narrow down options like (what type of connector is needed if any? Shear Bolt or Compression? etc.)

    Below is how the dimensions / spec parameters of the different items interact with each other.
    Dimensional / Spec Parameters:
    • Cable (Query) to Splice Body Parameters
      • The cable's "Insulation Diameter" must be >= to the Splice Body "Min Insulation Diameter" and <= to the Splice Bodies "Max Insulation Diameter".
      • The cable's "Jacket Diameter" must be < the Splice Body "Max Overall Diameter".
      • The cable's "Neutrals Over Jacket" must be < the Splice Body "Max Overall Diameter".

    • Cable (Query) to Constant Force Springs
      • For splice bodies with a Shielding Mesh: The cable's "Diameter Over Mesh" must be >= the Constant Force Springs "Min Application Diameter" and <= the "Max Application Diameter".
      • For splice bodies without a Shielding Mesh (Example: CUS54002): A Constant Force Spring is not needed.

    • Cable (Query) to Compression Splice Connectors - Note this table is incomplete at this moment, missing diameter info.)
      • The cable's "Conductor Type" needs to be identified so it can be compared to the correct Min / Max values in the Compression Splice Connectors table.
      • Solid and Compact Conductors would reference the "Min Compact Conductor" and "Max Compact Conductor" fields.
      • Class B and Compressed Conductors would reference the "Min Class B (Concentric) Conductor" and "Max Class B (Concentric) Conductor" fields.
      • From there the cable's "Conductor Size" needs to be equal to or fall in the range of the Min / Max fields.

    • Cable (Query) to Shear Bolt Splice Connectors
      • The cable's "Conductor Size" needs to be equal to, or fall in the range of the "Minimum Cable Size" and "Maximum Cable Size" fields.

    • Available Connectors (Compression and Shear Bolt) to Splice Body Parameters
      • Compression Connector:


        • The connector's "Length" must be <= the Splice Body's "Max Connector Length" and the connector's "Diameter" must be <= the Splice Body's "Max Connector OD.

      • Shear Bolt Connector:
        • The connector's "Length" must be <= the Splice Body's "Max Connector Length" and the connector's "Outer Diameter" must be <= the Splice Body's "Max Connector OD.
        • The connector's "Length with Ring" must be <= the Splice Body's "Max Connector Length" and the connector's "Diameter" must be <= the Splice Body's "Max Connector OD. (Note that a ring is not supplied with every shear bolt connector and will only be used based on certain cable conductor sizes. I have not added this information into the table at this moment).


    That pretty much sums up how a kit is selected for a given cable. At the moment this is a manual process that can lead to errors especially if cable dimensions are not provided and we are left to assume values. Any assistance I can get with setting up the foundation for this will be greatly appreciated.

    Splice Body Database.zip

  2. #2
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would suggest you stop for a while and fix issues with the design.


    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not begin an object name with a number.
    Do not use multi-Value fields, Calculated fields or Look up FIELDS in tables.

    The evils of lookup fields
    The Ten Commandments of Access

    Primary key fields should never be text type.
    See Table and PK design tips


    "Type", "Size" and "Width" are reserved words in Access and shouldn't be used for object names.
    "Description" is also a reserved word...plus it isn't very descriptive is it? "Description" of what??


    Any time I see "ID" in a field name (especially the PK field), I expect the field type to be numeric. (one of my conventions)


    Just a few things I saw looking at the dB....

  3. #3
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi
    Following on from what Steve has said the following is the ER Diagram for the Relationship between
    "tblConductors" and "tblJCNCables", togethr with other tables which replace Lookup Fields.
    Attached Thumbnails Attached Thumbnails ER Diagram.JPG  
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  4. #4
    Join Date
    Jun 2022
    Posts
    10
    Thank you for the feedback. I'll get to work on making the updates you've mentioned. That said, is what I'm trying to acheive possible? Also should I rename my Access file to not have spaces as well (SpliceBodyDatabase)?

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi
    It would help if you can explain how you would want to use a Form for Data Input.

    When you are trying to Identify a Part which links to a specific Cable which is made up of a number of Kits
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Prysmian001 View Post
    Also should I rename my Access file to not have spaces as well (SpliceBodyDatabase)?
    Windows allows spaces in file names, But I was taught not to use spaces in names.
    In Access, object names should never have spaces (IMO).


    Good luck with your project.........

  7. #7
    Join Date
    Jun 2022
    Posts
    10
    Quote Originally Posted by mike60smart View Post
    Hi
    It would help if you can explain how you would want to use a Form for Data Input.

    When you are trying to Identify a Part which links to a specific Cable which is made up of a number of Kits
    Hi Mike,
    Check out the presentation file I put together for an example of the forms I'm considering setting up in order to direct the user in the kit selection process. The examples I made are by no means the final draft so if you have suggestions please let me know. I'm still working on updating my table names and relationships as you and ssanfu suggested.
    Attached Files Attached Files

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    The PowerPoint presentation mentions on the 1st Slide looking for a Cable Type of "Jacketed Concentric Neutral 1 Core"
    The problem I have is that I cannot locate a table that contains the value "Jacketed Concentric Neutral 1 Core" ???

    How do you arrive at this value?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  9. #9
    Join Date
    Jun 2022
    Posts
    10
    Sorry. Probably should have explained that. A Jacketed Concentric Neutral is commonly abbreviated as JCN (see "1C JCN Cables" table or the "1C JCN Cables" query). I wasn't sure if I should continue to use abbreviations in the setup of the tables so I was considering writting out the full title. On the flip side is there a limit to how long the table names, etc. can/should be? Of course the names of the table need to be changed anyway to be more in line with best pratices. In the image you sent this would be the tbl1CJCNCables.

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Well you now have me completely baffled.
    In tbl!CJCNCables there is no field with "JCN" in it ??
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  11. #11
    Join Date
    Jun 2022
    Posts
    10
    The 1st form that has the user select the cable type is just so I can gather information as to which table I should be directed to as opposed to checking all of them for a catalog ID. My goal at the moment is to get the form to work by only running queries against the JCN table since the others have not been created yet. Once I understand the process I can modify the code as needed to reference the other cable tables. I'm thinking that whichever cable type is selected from the drop down list, it will be stored as a variable so it can be used when directing the query to the correct table when searching for the catalog ID.

    JCN is part of the table name, not a field. I'll need to separate the cable types into different tables since they don't have the same structure (cable layers). For instance a JCN cable can be externally grounding by taking the neutral wires out of the cable. Doing so will increase the overall diameter of the cable which is an important dimension to ensure a splice will be able to slide onto the cable. On the other hand a Copper Tape Shield (CTS) cable does not have neutral wires that can be pulled out. While it can be externally grounded the process does not impact the cable's overall diameter so there will be no need to run a calculation to increase the Jacket Diameter by the neutral wire diameter.

    To give you an idea of what I'm talking about check out this video. It's a installation video of our Compact Elaspeed Splice on a Flat Strap Cable. A Flat Strap Cable would be treated the same was as a JCN. Note that in the demo we do not externally ground it as it's only done depending on the customers needs.
    https://www.youtube.com/watch?v=vhFH68iOEEU

  12. #12
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Prysmian001,

    "1C JCN Cable" is very specific for a table name.


    Quote Originally Posted by Prysmian001 View Post
    I'll need to separate the cable types into different tables since they don't have the same structure (cable layers).
    "cable layers" means how the cables are constructed? Or the fields would be different?

    So how many cable types of MV cables are there?
    So far you have listed "1C JCN Cable" and "Copper Tape Shield (CTS) cable".
    Why would one table for both (or all) cable types not be appropriate? What are the attributes of each of the cables?


    -------------------------------------
    Just curious, did you draw the design on paper/ whiteboard/ a window/ cardboard/ etc BEFORE jumping into Access and creating tables?

    "Old Programmer's Rule" is this: If you can't do it on paper, then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.
    -------------------------------------



    I can see how this will be very useful... don't give up......progress is being made.

  13. #13
    Join Date
    Jun 2022
    Posts
    10
    Quote Originally Posted by ssanfu View Post
    @Prysmian001,

    "1C JCN Cable" is very specific for a table name.

    "cable layers" means how the cables are constructed? Or the fields would be different?

    So how many cable types of MV cables are there?
    So far you have listed "1C JCN Cable" and "Copper Tape Shield (CTS) cable".
    Why would one table for both (or all) cable types not be appropriate? What are the attributes of each of the cables?


    -------------------------------------
    Just curious, did you draw the design on paper/ whiteboard/ a window/ cardboard/ etc BEFORE jumping into Access and creating tables?

    "Old Programmer's Rule" is this: If you can't do it on paper, then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.
    -------------------------------------



    I can see how this will be very useful... don't give up......progress is being made.
    There are several different types of Medium Voltage (MV) Cable in the market. The common types that I deal with are listed below. My plan was to create a table to handle each type to keep the table size down and becuase each cable type have their own engineering standards to follow. That way if a catalog sheet is missing data I can estimate it by referencing the engineering standard value:
    Jacketed Concentric Neutral Cable (JCN) - Single Core
    Non-Jacketed Concentric Neutral Cable (NJCN)
    - Single Core
    Flat Strap Neutral Cable (FSN)
    - Single Core
    Copper Tape Shielded Cable (CTS)
    - Single Core
    Longitudinally Corrugated Cable (LC) - Single Core
    Paper Insulated Lead Shielded Cable (PILC) - Single Core
    AirGuard Cable - Sinlge Core
    Armored Cable
    - Sinlge Core
    Airbag / Non-Armored Cable
    - Sinlge Core
    Paper Insulated Lead Shielded Cable (PILC) - Triple Core
    AirGuard Cable
    - Triple Core
    Armored Cable
    - Triple Core
    Airbag / Non-Armored Cable
    - Triple Core

    The construction of each cable differs from each other resulting in different dimensions in some cases. For instance a JCN cable is cable in which the metallic shield is made up of small copper wires. If the splice needs to be externally grounded these neutral wires can be taken out of the cable and routed along the cable jacket. This will increase the overall diameter of the cable jacket which is a critical dimension I need to check. On the other hand a CTS cable has a metallic shield that is made of a thin sheet of copper helically applied to the cable. It's not possible to take this metallic shield out of the cable for externally grounding so I would just reference the jacket diameter in the cable's catalog sheet.

    Also make note of the "- Single Core" and "- Triple Core". In my tables I've been denoting this a 1C. This means that the cable consists of a single conductor. There are some cables that come with 3 conductors and each conductor needs to be broken out and separated prior to splicing. Those are commonly denoted as 3C cable.

    Most cables like the JCN, FSN, CTS, and LC have the same layers and just the metallic shield is changed. The layers are the conductor, conductor shield, insulaiton, semi-conductive shield, metallic shield, and the cable jacket. Chaning the metallic shield results in different dimensions along with different cable prep procedures.

    Cables like the PILC, AirGuard, Airbag and Armored have additional layers to make the cable more robust that are not present in other cable types. For instance and AirGuard Cable has an Airbag and AirGuard layer below the jacket while and Airbag Cable only has the Airbag Layer.

  14. #14
    Join Date
    Jun 2022
    Posts
    10
    Quote Originally Posted by Prysmian001 View Post
    There are several different types of Medium Voltage (MV) Cable in the market. The common types that I deal with are listed below. My plan was to create a table to handle each type to keep the table size down and becuase each cable type have their own engineering standards to follow. That way if a catalog sheet is missing data I can estimate it by referencing the engineering standard value:
    Jacketed Concentric Neutral Cable (JCN) - Single Core
    Non-Jacketed Concentric Neutral Cable (NJCN)
    - Single Core
    Flat Strap Neutral Cable (FSN)
    - Single Core
    Copper Tape Shielded Cable (CTS)
    - Single Core
    Longitudinally Corrugated Cable (LC) - Single Core
    Paper Insulated Lead Shielded Cable (PILC) - Single Core
    AirGuard Cable - Sinlge Core
    Armored Cable
    - Sinlge Core
    Airbag / Non-Armored Cable
    - Sinlge Core
    Paper Insulated Lead Shielded Cable (PILC) - Triple Core
    AirGuard Cable
    - Triple Core
    Armored Cable
    - Triple Core
    Airbag / Non-Armored Cable
    - Triple Core

    The construction of each cable differs from each other resulting in different dimensions in some cases. For instance a JCN cable is cable in which the metallic shield is made up of small copper wires. If the splice needs to be externally grounded these neutral wires can be taken out of the cable and routed along the cable jacket. This will increase the overall diameter of the cable jacket which is a critical dimension I need to check. On the other hand a CTS cable has a metallic shield that is made of a thin sheet of copper helically applied to the cable. It's not possible to take this metallic shield out of the cable for externally grounding so I would just reference the jacket diameter in the cable's catalog sheet.

    Also make note of the "- Single Core" and "- Triple Core". In my tables I've been denoting this a 1C. This means that the cable consists of a single conductor. There are some cables that come with 3 conductors and each conductor needs to be broken out and separated prior to splicing. Those are commonly denoted as 3C cable.

    Most cables like the JCN, FSN, CTS, and LC have the same layers and just the metallic shield is changed. The layers are the conductor, conductor shield, insulaiton, semi-conductive shield, metallic shield, and the cable jacket. Chaning the metallic shield results in different dimensions along with different cable prep procedures.

    Cables like the PILC, AirGuard, Airbag and Armored have additional layers to make the cable more robust that are not present in other cable types. For instance and AirGuard Cable has an Airbag and AirGuard layer below the jacket while and Airbag Cable only has the Airbag Layer.
    Cable Type Fields.zip
    Hi ssanfu,
    To your point it should be possible to include all the cable types on a single table. My concern was the size and that not all the fields will be utilized for some cable types. In the attached file I broke down the different fields I'd need to include for each cable type. For cables that are not a 3 core PILC cable the "Diameter Over Semi-Con Tube" would probably just be left empty because it's a value that won't apply to them. On the other hand all cable types will have a "Conductor Size", "Voltage and Insulation Level", etc. There are also some fields that will be calculated differently depending on the cable type and number of cores. That can be handled using IF statments though.

    Taking this "write it out" task further I put together a rough outline of the different components we stock that are dimensional dependant to be able to function properly. Right now I'm focusing on the Splice Kits but I'd like to extend it to our Termination, Deadbreak, and Loadbreak kits as well. This can be found in the "Diameter Dependant Components" tab in the attached file.

    How would you suggest I proceed with the cable tables? For a single cable type it's possible that the table would have thousands of entries. I was also trying to avoid long and complicated IF statements in the calculated fields to avoid errors. Lastly my intent was to store the cable tables in a separate Access file from the forms and components. The form will ask the user what type of cable they were working with and will in turn open/call that specific file containing that cable's table as opposed to referencing one giant table. In the meantime I'll work on putting together a flow chart on what needs to happen.

  15. #15
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The cables table is looking better. However,
    Quote Originally Posted by Prysmian001 View Post
    There are also some fields that will be calculated differently depending on the cable type and number of cores. That can be handled using IF statments though.
    That is your Excel brain talking. You have to forget about Excel. Access is a different animal. Might be calculations in a query or might be some UDF's.

    Is the table design starting to make sense?
    I started defining field names in the attachment.
    Attached Files Attached Files

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Show Only Available Options in a Combo Box
    By Ramtrap in forum Queries
    Replies: 6
    Last Post: 11-22-2017, 09:13 AM
  2. Look Up - show fewer options by matching criteria....?
    By synses in forum Database Design
    Replies: 3
    Last Post: 03-13-2017, 02:33 PM
  3. Replies: 4
    Last Post: 07-20-2016, 03:04 PM
  4. Replies: 2
    Last Post: 07-31-2012, 09:14 AM
  5. Don't show lookup list options in report
    By dara in forum Reports
    Replies: 1
    Last Post: 02-17-2010, 10:26 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