Results 1 to 9 of 9
  1. #1
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89

    Many to many relationships

    Hi all,


    I received some guidance here previously and am grateful for forums like this. My other project is on hold because the future of my company is in question at the moment, but I still want to learn more. I'm struggling with how to set up a particular relationship that fits into the many-to-many category, I've read a lot of material on this but am really struggling to visualize how it can work. What I have is a system where I am matching crimp terminals to crimp tools. Each of the tools is able to process a variety of different crimp terminals (many terminals to one tool). So far the structure is working OK but I need to make some additions to make it more functional. The problem is as follows:
    - For some tools I have multiple instances of the same tool
    - Some terminals may be processed by more than one tool (Either/or rather than one after another)
    - Some tools have interchangeable die sets, in which case the terminal links to the die set then the die set links to the tool

    I know that for the second line I should have a junction table to create a one to many relationship, or a multivalue field, but I am having a really hard time seeing how that would go together. Any guidance on this would be greatly appreciated!

    Click image for larger version. 

Name:	tools.JPG 
Views:	25 
Size:	75.8 KB 
ID:	30271

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not sure how much I can help, engineering is not my forte!

    - For some tools I have multiple instances of the same tool
    This is two tables. One record can only contain one item, so if you have a table for tools then each one is a separate tool. To join them together, add a table "above" for categories (or whatever you call them) so that you can group a bunch of tools together under one heading.

    or a multivalue field
    Steer clear of these, they will cause issues and confusion.

    - Some terminals may be processed by more than one tool (Either/or rather than one after another)
    There would be a table containing this - tool and terminal. A list of each terminal along with each of the tools that can be used on it. If you need this kind of detail.

    Remove all spaces and special characters everywhere. ONLY use letters, numbers and underscore in tables, queries, forms, and all the fields that each contain. Also, do not use lookups within tables, comboboxes, etc. These must all be handled outside of table design, these will complicate things immensely. You won't find any of the answerers on this forum who use these things.

  3. #3
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    Quote Originally Posted by aytee111 View Post
    Not sure how much I can help, engineering is not my forte!


    This is two tables. One record can only contain one item, so if you have a table for tools then each one is a separate tool. To join them together, add a table "above" for categories (or whatever you call them) so that you can group a bunch of tools together under one heading.
    So, if I understand what you're saying correctly I would have a separate table with tool number as a foreign key and qty, as well as whatever primary key Access assigns? Hopefully I got that part, but have a follow on question: How would I account for those additional instances of a tool for maintenance purposes? I'm almost wondering if I just need to add a suffix to each tool number to allow it to appear more than once?

    Quote Originally Posted by aytee111 View Post
    There would be a table containing this - tool and terminal. A list of each terminal along with each of the tools that can be used on it. If you need this kind of detail.
    I do need that kind of detail as I often need to use a different tool depending on availability so I need to know all the options both ways. I suppose this is where I have a hard time seeing things - Would it be a table with a field for terminal number then several fields for tool numbers?

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I am hampered by my lack of understanding of tools, sorry. I was hoping someone more knowledgeable would jump in!

    So, tools require maintenance? If that is the case, you would need a separate record for each tool occurrence, not one record with a quantity. Each tool record would then become a stand-alone entity, with its own maintenance tracking. Can you provide examples, it would help. I am thinking there is a tool type and then multiple of the same in the second table. The suffix identifier - do you use anything at them moment to identify one of those tools, such as a serial number? Or is there a box full of them and people use whichever one they grab?

    For the terminals, the tool would not be carried on that table, unless you want a default (and it would be from the type table, I assume?). Then the second table would carry the terminal ID and the tool type ID for each of the tools that are applicable for that terminal ID. This table will only carry an autonumber ID, the terminal ID and the tool ID. In table design, you have one record per item, never multiple fields carrying the same data. You will end up with being able to query all the tools for a certain terminal, or the other way around, all the terminals where a certain tool can be used.

  5. #5
    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
    16,716
    khayward,

    In my view you are focused on HOW to do something with Access. What we need to understand is that something ---WHAT is it in simple terms that your "business" entails.
    Perhaps you could provide a "typical day at the office" view in simple English. And perhaps an example or 2 so that readers can appreciate what exactly you are trying to support with this database. It will help readers, but it will also help you clarify your thoughts.

    As for Access I recommend you do not have embedded spaces in field or object names(you will eliminate a major syntax error issue).. Also, no special characters eg #%-+* etc. If you want,you can use "_" underscore which Access understands.

    Good luck.

  6. #6
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    Thanks again for the responses. I'll try to explain what I do, though I will point out that the database serves a couple of different functions which aren't all related, I just wanted to keep those things together. I understand about the syntax also.

    So, a day at the office, per se, involves making wire harnesses and cables. We make hundreds, if not thousands of different ones, on demand, for lots of different customers. When it comes time to build something, especially something new, I have to look up tooling for the different connectors. Normally, with a new build, the process is documented so that the tooling information is in the documentation (Like a Lego instruction book) but normally is really an exception, we operate so quickly that stuff doesn't get written down as often as I'd like. Also, when a request for pricing for a new product comes through I have to look to see if we have the equipment to process the parts or not. Sometimes we don't have the exact right one, but will have something very similar that we can use as an alternate.
    I also have to keep up with maintenance on our equipment, each piece has it's own requirements and that information has to be logged to prove that we're doing the work
    I use the database to keep track of which wire crimp terminals go with which tools, so that when I have to build something I can look up the right tool for the part. Essentially I type in the crimp terminal part number and it tells me which tool to use.

    I hope that explains things, if not let me know, and thank you for taking the time to help!

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Would it not be better to have an Alternate table, so that each tool can have some records associated with it which can be used in its place. Instead of the table we were talking about above, where each terminal has multiple tools associated. I would think that the former is preferable and makes more sense. A terminal will then have one tool to use, but that could be used to find all the alternates for that tool.

    You have two subsystems here - one tracking build and one tracking tools and maintenance. You also mentioned a tool category or type, where does that fit in?

    Some tools have interchangeable die sets, in which case the terminal links to the die set then the die set links to the tool
    This hasn't been spoken of - is this correct? In your picture above terminal does not link to dieset.

  8. #8
    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
    16,716
    khayward,

    I did a little googling and have seen 1 episode of Wheeler Dealers on velocity TV dealing with wiring harnesses.
    I did find this site describing building a wiring harness for a motorcycle.
    My point is that there seems to be more to building a wring harness(es) than crimping terminal ends to wires.
    Are you sure your description of your proposed database is what you are really trying to solve.

    As I recall on the TV episode, the company had a number of "boards (4x8 sheets of plywood or so) which mapped out the lengths, gauges, intersections, terminals etc for a variety of automobile wiring harnesses.

    So, while terminals and crimp tools are an aspect of the "wiring harness manufacturing business" it seems to be only 1 part.

    You can build a 30000 ft scenario and focus on the terminals and crimping tools and replacements/alternatives. But who will deal with wire lengths, colours, gauges....?

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    From your explanations it looks for me that you have 3 different task chains here:
    1. Following where every tool is at given moment;
    2. Following tool maintenance;
    3. More vaguely some elements or ERP system (technologists deciding, which toolset to use for new production).

    I'm trying to sketch on fly, how I would start at least with 2 first.

    At start, for me it looks that your crimp terminals are tools too. At production side, I would add workstation as a place where all tools (crimp terminal included) are given. And depending on your production process, maybe Production Orders too (in case when at same workstation you can manufacture different products over time).

    As your main task is focused on tool, lt's start with them.

    You need a Tools table, but it looks for me, that you have a lot of them, so it will be reasonable to group them somehow. I.e let's define another table at start.
    tblToolGroup: TGID, TGName, TGComments. (I have an IT Devices database where I use a table like this, and there the group is 2-character string like "CD" (computer, desktop), "CS" (computer, server), "NM" (network device/switch, manageable) etc.), but you can define in any way you like. The main reason for using groups is, that you can then have a parent form - subform pair, where you select tool group in parent form (bound or unbound), and will have all tools of this group displayed in contionous subform. Btw, one group will be for crimp terminals.

    Another case to clear before defining tools table is die sets. For me it looks like tool complects, i.e. a die set consists x different tools, y pieces each. To keep dataflow simple, it is better to have all movements to next level in same structure entity. I.e. those die sets are tools too. Here you have a 2 possible solutions:
    a) you have a tblToolComponents table, where you register all "elemental" tools, a tblTools table, where you register all tools used in production, and a tblToolStructure, where for every tool in tblTools the list of components and their quantities are registered. NB! You have to register the structure for single-component tools also!
    b) you have tblTools, where are registered all tools used in production, and additionally all tools used in another toools, but not in production. And you have tblToolStructure, where for many-component tools their structure is registered.
    Variant a is simpler for designing various queries and reports later, but you need more work at registering new tools. Variant b is easier for tool registering, and takes less disk space too, but in queries and reports you have always to check, has the tool components or not, and to design depending on it. I'm leaving a decision to you, and assume you have a tools table.
    As you have to keep tools maintenence info, and tools movements, every tool must be registered as separate entity.
    tblTools: ToolID, ToolGroup, ..., ToolUse, ToolStatus.
    NB! ToolUse is optional field, where you can describe, is the tool for general use, for products of certain client, or for certain product.
    NB! The ToolStatus field is recommended, as it allows you to set filter on your tools subform, like to show only tools in stores, or only tools given out to workstations, or only scrapped tools, or all tools except scrapped etc. In my IT Devices database, I use statuses 1 - 99, where 99 is for scrapped/utilized/returned to leasing firm/etc. devices, and by default are shown all devices for given group with status < 99.

    In case of crimp terminals (and maybe in case of some other tools group too) you have a lot of information you don't need for other tools. For such groups, add group-based tool property table(s) - depending on your production schema such tables may be linked to tblToolGroup or to tblTools.

    Now you can start with moving tools into storage or production or into maintenance etc. So you need to register all those places, where tools are used or stored or whatever.
    tblWorkstations: WSID, WSName, ToolStatus, ...
    Workstations will be:
    store(s) where you keep unused tools;
    Workplaces in production where tools are used;
    Optionally maintenance station(s) when there may be cases where tool is out of use for longer time (short-time maintanance can be registered without moving the tool out of production);
    Out-of-Use - is used to scrap old tools.
    ToolStatus field determines the status, the tool is given automatically, when the tool is moved to new workstation.

    Now we can register tool movements. The first movement is whenever the tool is buyed (or produced locally). Tools can move into store, into production (to workplaces), into maintenance, or into scrap.
    tblToolMovements: TMID, TMDate, ToolID, WSID, IsCurrent (Edit: TMDate, ToolID and WSID combined together are defined as unique Index);
    Whenever the tool moves to new location, the tools new Status is calculated and stored into tblTools.
    Edit: Added a field IsCurrent. Whenever for ToolID a record is added or deleted, or TMDate is edited, for the latest record <= DATE() IsCurrent is set to 1, and for all other records for this ToolID IsCurrent is set to 0 (this field will be useful, when you want for some form/report to set filter so that only tools currently present on some workstation are displayed).

    To oversee tool maintenance, keep it simple and in single table.
    tblToolMaintenance: MntID, MntTyp, ToolID, SheduledStart, RegisteredStart, RegisteredEnd, Responsible, ...
    MntTyp has values for sheduled maintenance, for maintenance out of shedule, for repairing of broken tool etc.
    You can have a shedule table like in your shema in 1st post where you determine null-date and shedule step, and run some procedure which inserts rows into tblToolMaintenance with MntTyp set to sheduled maintenance and ToolID and SheduledStart prefilled.
    Responsible is selected Employee from tblEmployees/tblMaintenanceStaff. (Edit: you may consider to have EmployeeID in Employees table to avoid problems when you have 2 employees with same full name. and have separate fields for fore- and surname.)

    Edit: It looks like your database will have several users so it will better to split the database to front - and back-end. And as different user groups need differently organized information, you may consider to have different front-ends for yourself, for maintenance staff - they need information p.e. about tools sheduled for maintenence in near future, and about tools not maintenanced currently at sheduled time, and for technologists - they need p.e. information about tools used on certain workcenter (and for certain production order, when this info was added too), or about tools used for specific client or product.
    Last edited by ArviLaanemets; 09-14-2017 at 11:38 AM.

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

Similar Threads

  1. Replies: 7
    Last Post: 08-05-2016, 11:53 AM
  2. Replies: 2
    Last Post: 07-27-2016, 10:25 AM
  3. Relationships
    By DFeil in forum Database Design
    Replies: 2
    Last Post: 10-27-2015, 10:12 AM
  4. Explicit Relationships and Implicit Relationships
    By Dazza666 in forum Database Design
    Replies: 2
    Last Post: 07-17-2013, 02:11 AM
  5. One to many relationships
    By CoachBarker in forum Database Design
    Replies: 1
    Last Post: 09-13-2010, 09:01 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