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