Compare complex items, query duplicates, etc.

[dotails]

I have a database with 1000+ cables in it that I want to compare and look for duplicates or list cables that will work for each function.
To do this I have tables for a list of connector types, a list of functions a cable can perform, a list of compatible products.
I will go through each line in the cable database and examine the cable schematic and select each of the broken down specs from drop down menus like that seen above. But let's say I have a cable with a 10PCBL connector and x5 3PACP connectors how do I select that it has 5 of one of the connector types?

[Micron]

Advise forgetting about multi value fields,which you seem to have created. At a minimum, it seems you'd need a
- table for cable types
- table for functions
- table for connector types
- table for products
- one or more junction tables (e.g. cable/connector)
but it's impossible to say without understanding the business at hand. While it seems what you've asked for is a way to restrict subsequent choices based on a prior choice, this is neither done in tables (use forms) nor would any experienced db developer attempt to do this directly on tables (to say nothing of using multi value fields) so really, you need to rethink your approach and design. If you want help on exploring that, divulge exactly what is the business at hand. Be very concise, explicit and explain it fully in simple terms with no jargon. While we might know a thing or two about db design, chances are that we know nothing of that business, nor can we see what you're working with. If you need sources to read up on normalization or good design, we can start there.

[orange]

dotails,
Further to micron's good advice and comments, we only know what you tell us. We (me for sure) do not understand your business nor cable functions nor connectors. Readers can't offer focused advice until we understand your set up and the issue. So please lead us through " a day at the cable manufacturing office" type scenario. Make it real simple so we grasp the essentials.

Good luck and welcome.

[dotails]

We make cables for customers but we have a special type of problems.
These cables can be broken down into connector types and function(what they are used for).

Here’s an example of a cable drawing.
You can see on the left a 26 PIN CIRCULAR connector
and on the right three of the same TRAILER PLUG connectors.
All three legs function as POWER on this cable.


Here’s an abstract chart to point at specific problem cases below.


FIRST PROBLEM CASE
Let’s say someone orders the cable PN3(from the chart above)
We don’t have that already built in inventory and we don’t have the parts to build it.
So we tell the customer they have to either wait or pay extra for expedited shipping.
However we have a basicly identical cable PN6 that will work as it has both the same connectors and same function.
But we don’t have a way to know that unless we manually check or memorize the hundreds of cable drawings.

SECOND PROBLEM CASE
Let’s say someone orders cable PN2
We don’t have that already built in inventory and we don’t have the parts to build it
However we have a cable PN1 or PN4 on the shelf that will work for the customers application.
Again with no way to know this without brute force.

[orange]

You say you have a database with more than 1000+ cables, but we are seeing the use of multivalued fields which most developers would suggest you avoid.
Based on your sample Problem Cases, it appears that your database does not meet your requirements.

My recommendation is that you must identify all of your requirements before designing and developing your database.
If you need to know which cables are interchangeable, you'll have to define what that means exactly in your business.
It appears that you need an inventory of Parts and, also what Parts are required for each Cable type.
It is not clear from the info so far what you do to create a new cable, or choose a replacement for any specific cable.

There are several details missing in my view. I found this via google and that's just major electrical connectors.

I'm wondering what process(es) you/your company goes through when this occurs:

We don’t have that already built in inventory and we don’t have the parts to build it.
So we tell the customer they have to either wait or pay extra for expedited shipping.

Can you show us more of your current database design - especially tables and relationships?

Your graphics are interesting, but we know little to nothing about the cable manufacturing process nor what the components of each drawing.

But we don’t have a way to know that unless we manually check or memorize the hundreds of cable drawings

.
I think that is the basis of what your database must deal with --the cables, connectors, parts, drawings and all the interactions.
Some search facilities; inventory management; cable design and interchangeable parts.....

I also note that you have just recently joined the forum - welcome.
Can you tell us a little about yourself and how/where you fit in the business and the database?

Good luck with your project.

[dotails]

It's very simple, as simple as legos,

A time sensitive customer orders a cable by number like (PN4 from the chart above)
If our inventory software says the cable is already in-stock we ship it right away <this is a success
If it is not in stock, we open this part number's cable drawing/list of materials
The drawing tells us the connectors(in my example drawing there was a 1 circular and 3 trailer connectors)
The drawing also tells us the functions(in my example drawing all 3 groups of wires are for POWER)
If we have the connectors in stock we build the cable and ship it right away <this is a success
If the connectors are not in stock we inform the time sensitive customer there will be a lead time as we wait for the connectors to be ordered <this is still success
Sometimes we happen to notice there was another cable with the same minimum connectors, same minimum functions between those connectors, but different part number, that would have worked for the customer <this is unacceptable FAILURE

The chart above shows the assembly process, really simple, just stick some functions(wires) between some connectors and ship it, if it has extra connectors and functions that's ok too, the customer just won't use those.

As for how our database design looks now I would pretend we haven't started.
We've been experimenting and have an imported table with all our cable part numbers, locations, quantities, and other details just to have them handy in one place,
then we have a table listing all the individual possible connectors
another table for all the possible functions.

[orange]

It's very simple, as simple as legos,

Really?
Do you understand the design and engineering and quality control and marketing that went in and continues with legos?

We've been experimenting and have an imported table with all our cable part numbers, locations, quantities, and other details just to have them handy in one place,
then we have a table listing all the individual possible connectors
another table for all the possible functions.

Sounds like trial and error without a plan or target. That's not how database works.


Here's a link to some Database Planning and Design info that should be helpful to you.

Start with a list of some of the questions you need answers to.
Work with models, sample data and test scenarios on paper.
Understand and analyze the things involved and how they fit together.
Make mock up reports of what you need from your proposed database.
Make sure you can get answers you need from the models and scenarios.
When you have the blueprint tested and vetted, then it's time to develop the database.

You may also get some ideas from this post.

Good luck.

Recent article on Lego: https://www.topgear.com/car-news/fut...tion-equipment

[dotails]

@orange
You missed the part where I said we haven't started yet, we ran some experiments and imported some data to see the limitations of the software. now that we have an idea of what the software can do we were seeking some tips for how to solve our unique use case problem with design.

As an engineer I am aware of design and qc etc. What I meant was "handling" the cable assembly is as easy as handling legos. This was in response to your comment , "It is not clear from the info so far what you do to create a new cable, or choose a replacement for any specific cable. we know little to nothing about the cable manufacturing process nor what the components of each drawing." I was trying to reassure you and not overwhelm you.

I've broke down the problems clearly. I am seeking a specific design for a database for this unique problem. Thanks for the link. I do need more help.

[orange]

Yes, I guess I missed the part where you said "we haven't started yet".

I must have misunderstood your opening statement.

I have a database with 1000+ cables in it that I want to compare and look for duplicates or list cables that will work for each function.

What exactly is your role in the project?

[Micron]

To add to the design suggestions (of which I already stated that mv fields are to be avoided) I'll say this: my original answer about table requirements still applies in a general sense, albeit some of the colouring stuff is still a bit ambiguous. In the least, it seems you need:
- a table for connector types. Aside from PK fields such as auto numbers (for which I won't repeatedly refer to hereafter) maybe this has
---- a connector type field (round, square, oval, whatever).
--- number of pins
--- anything else related to the connector
- a table for functions. However, a yellow line means nothing to me, so I can't suggest what fields it might have
- a table for cables. It would have to contain the connector ID, the function ID (as a FK) and the drawing and/or part number. Is PN a table of assemblies you make? Is there a drawing number table? I don't know. If yes, then those PK id's are FK values in tblCables.

Hopefully you can (or will when you review the tutorial links Orange provided) see that this is more of a db design problem rather than a question on how to do something specific in Access. To know how to best design it would require a much more in depth treatise from you on specifics about the business and what's supporting it now. The main take away might be that each facet of the business probably has one or more entities (which are your tables), each of which has one or more attributes (which are characteristics of the entity and become the table fields). For example, Employees are an entity. They have gender, employee number, home address, phone number, etc. as attributes/fields. Their age is NOT an attribute (because it's ever changing, thus is calculated by comparing their DOB to the current date). There should only be one entry in such a table, for each person. Any attribute that would require a second record for the same person, in 99.99% of cases, does not belong there.

I could go on and on, but here's a few links I often provide beginners, some of which don't apply to table relationships, but will nevertheless help you to avoid some common pitfalls.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html

One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html

[dotails]

@Orange I apologize for the confusion, when I said, "we have a database with 1000+ cables in it" I meant we have a list.
Role? I'm somebody with a problem hoping to be a problem solver.

[orange]

dotails,

No apology is necessary. I am just trying to help tailor your expectations with the effort that is required.
A list is a long, long way from a well-functioning, operational database supporting the business of a viable, manufacturing company.

Can you show us some of the details re:

I've broke down the problems clearly

.

Have you also documented what would represent a solution to these problems? In simple English terms.

I'm sure you, as an engineer, know that a significant endeavour requires a statement of the problem; some elements of possible solution; analysis and feasibility of options; resourcing; design, prototyping and testing; acceptance; documentation; training.... and most of all a project plan. These are all pieces you will experience to some level in your proposed database project.

You may even find that reviewing operations and sales will led to a need for better forecasting of orders and planning for parts acquisition to optimize resources. Your database may be a trigger for revised management practices and manufacturing processes.

Good luck.

You may find the dialog in this thread interesting.

[dotails]

There was and unacceptable failure I mentioned and the solution that seems most obvious is a search field which I input a part number and it returns a list of comprable part numbers. But if you have a better idea I'm all ears.

[orange]

You might get some ideas from this generic data model from Barry Williams' site. It shows manufacturing products with a link to drawings. It also has some other entities that seem extraneous to your needs. Depending on your set up and needs, it seems you could find drawing components via some search mechanisms if you were to adapt this model to the missing parts of your current database/proposed database.
Good luck with your project.

[dotails]

Thanks orange, I'll pass it along, I don't need links to drawings, but maybe something on that page will be useful.