Results 1 to 13 of 13
  1. #1
    jdowell is offline Novice
    Windows 8 Access 2007
    Join Date
    May 2016
    Posts
    5

    Setting up complicated relations in Access

    Morning,



    Here's what I am trying to do, I want to make a database that will compare a list input and work out the best way to fill the requirements. For example, I have 3 leads:
    Lead 1 has A, B and 2 C type connections
    Lead 2 has A, D and 3 E type connections
    Lead 3 has 2 B, D and E type connections

    I want to be able to enter what I need, for example B and E, and it pull the best fit record (Lead 3 in this case). Also, I would like it to pull multiple leads if there isnt a single one that fits the criteria, e.g. I need C and D, I need to use lead 1 and 2.

    Is this possible, and if so how?

    I have dabbled in Access for data storage and forms before, but nothing like this as of yet

    Any help is appreciated
    Kind regards
    James

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    anything is possible - how is your data stored? Note it should be in two tables - one for lead description and one for the number and type of connections e.g.

    tblLeads
    LeadPK autonumber
    LeadDesc text

    tblConnections
    ConnectionPK autonumber
    LeadFK long
    ConnType text
    ConnQty integer

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Code:
     Also, I would like it to pull multiple leads if there isnt a single one  that fits the criteria, e.g. I need C and D, I need to use lead 1 and  2.
    Why not 1 and 3?

  4. #4
    jdowell is offline Novice
    Windows 8 Access 2007
    Join Date
    May 2016
    Posts
    5
    Apologies 1 and 3 would be fine, however I would like it to use the minimum amount of leads possible as i have over 140 currently in use.

  5. #5
    jdowell is offline Novice
    Windows 8 Access 2007
    Join Date
    May 2016
    Posts
    5
    Currently the data isn't stored in Access, it is all stored in an Excel format, I will have to build the data entirely from scratch, which is no issue. I wanted to be sure of how to implement it prior to inputting the data

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Build your tables as Ajax suggested. Then prototype the approach with some sample data and some sample scenarios.
    Make sure you can get the result you want in your prototype BEFORE doing major input and design.

    With the tables suggested, you should be able to confirm that the prototype works, or adjust it until it does, and use your prototype as a guide to your final solution.

    Do this in incremental steps. It's much easier to test and adjust if you
    -know what you are trying to achieve
    -build and test each step
    -then build on what you have that works.

    A poor strategy, that we see all too often, "I have all these intricate forms and I have inputted all of my data, but it doesn't do XXX....."

  7. #7
    jdowell is offline Novice
    Windows 8 Access 2007
    Join Date
    May 2016
    Posts
    5
    This was going to be my approach, I just dont know how I am going to implement the lookup portion primarily. Prior to this I have always used forms to retrieve data on a 1-1 basis, whereas I want this to do more than that. I know that I can make it simply look up all records containing connector A for instance, but making it look up the least records needed to fit a product that has A D F and 2 H connectors on is the bit I am unsure on.

    To put it into perspective we have over 700 connectors it could potentially be in stock currently, which can fluctuate to over 1000 in a matter of days. As business is growing quite quickly we will have more leads added daily and bespoke products made every few hours

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm not suggesting you shouldn't use a form or form/subform. That is likely where you final solution will take you.

    But for prototyping, create the tables and populate same with 10 records (some manageable number)
    make a parameter query that let's you enter your "mock search term(s)"

    [psuedocode]
    Select * from your query where Lead has your [mock criteria]

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Looks like a google type search criteria, from best possible match to worst possible match, in your example you have 1 'good' match (where all criteria are met by a single item) but you have 3 otherwise equally likely second choices, all of them involving 2 leads (1 and 2, 1 and 3, 2 and 3) your algorithm to set this up needs to have some very structured rules or it's going to be horrendously long to process all the possibilities, you're looking at (with 10 leads only) a total of:

    10 - Every lead matches the possible connections
    90 - 2 possible leads for the possible connections
    720 - 3 possible leads for the possible connections
    5040 - 4 possible leads for the possible connections

    what I'm trying to say is this blows up as a factorial, in other words if you had 10 connectors and ended up going to 10 different leads you'd potentially have 10! combinations or 3,628,800 possible permutations to check which is totally impractical. I think I'd view this as a set of tiered responses, in other words test to see if the connections can be met by a single lead, if you get 0 matches, then move on to the possible 2 lead combinations, if you get no matches there then move on to the 3 lead combinations and stop when you have a 'top 5' or 'top 10' matches so you don't bog down your system. I do not see this as a series of queries, I think you might need to use vba to sort out your search algorithm.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    The other consideration is 'which end'. A lead with 4 connectors - is that 3 at one end and 1 at the other? 2 and 2? and if out of these 4 connectors there is a pair - are they at each end or both at one end? I'm presuming some assumptions can be made re male and female connectors but this would also need to be factored into how the data is represented and its relationship. It also presumably matters a) what is physically in stock and b) order quantities (a customer wants 10 leads all with A and B connectors - will they be happy with 5 straight A/B connectors, 2 with extra connectors and 3 made up from cable pairing?) It can be done and there are plenty of analogies from logistics but it is not a simple task.

    I agree with rpeare - a tiered response is required. I had a little play around to find exact matches which is pretty straight forward, pairing is a bit more complicated but left me with an unsatisfactory answer - primarily due to the male/female issue - finding a cable which match on connection A is easy - but male/female?

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    In my view, jdowell has more analysis to do. And to do that somewhat efficiently, mock something up and try it. Learn the details/intricacies of what you are trying to accomplish and build progressively. Don't assume that anyone can design and build what you are dreaming of, especially if you can't describe the details in simple terms.

    I'm sure there are lots of similar examples from various disciplines. However, I don't think that jdowell or any readers could write the detailed specs sufficient to hand off to a professional developer at this point.

    The prototype approach was an effort to have the OP try a few things and get more details of the project before jumping too deeply too quickly into a "design" that had not been tested.

    Proficiency in Excel does not mean proficiency in database/Access. The different object models and the basic short and wide vs long and thin is all new to people starting with database.

  12. #12
    jdowell is offline Novice
    Windows 8 Access 2007
    Join Date
    May 2016
    Posts
    5
    In terms of cable genders I am adding them all as individual parts as we are a manufacturer and the gender classes it as a different part in its entirety. These leads are never sold, they are for internal use for testing products (1 lead will allow it to be hooked up to a test machine to electrically test our products). The lead will always have the same connector one end to hook up to our test machinery, the plugs on the cable are there solely to hook up what we sell for testing purposes. To build on what the scenario is, we make a product with A B and 2 C on, we need a lead with D E and 2 F to hook it up to the electrical tester. The problem is the variation in test leads we already have, I simply want a lookup database that can tell me if a test is capable with what we already have rather than making new leads for every product as a lot of what we sell is bespoke. Our products we sell won't need a record in the database, it is solely to work out which test leads we need to test a product with A B C connectors on.

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    These terms are used in your post and there is no clear distinction of what each is or how they relate to each other. Are they all relevant to your "business issue" or can some be dropped for clarity?
    cable
    lead
    connector
    plug

    Readers are not necessarily aware of the terminology associated with your business, so a diagram depicting the various "things" may be very helpful.

    It seems they are all "parts" or attributes of parts, but clarity here will help get focused suggestions.
    You say
    The lead will always have the same connector one end

    lookup has a special meaning in Access. To avoid the possibility of confusion I'd use "reference database".

    Product
    It seems that each product, or product group/category, has its own connector configuration. So recording
    connectors by product, product group/category may be appropriate. As new products or groups are identified, their "connector configuration" could be added to your reference table.

    I'm not meaning to be picky, just want to help you get some focused options, so the more clarity in requirements, the better.

    Note: I did a quick search on connector which broadens the topic rather than focusing.

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

Similar Threads

  1. Setting/Displaying records in complicated form
    By Monterey_Manzer in forum Forms
    Replies: 7
    Last Post: 04-26-2013, 05:26 PM
  2. Replies: 3
    Last Post: 03-06-2013, 02:33 PM
  3. Replies: 13
    Last Post: 05-23-2012, 09:42 AM
  4. Microsoft Access Relations Ships and Forms
    By rajgoyal00 in forum Forms
    Replies: 6
    Last Post: 11-21-2010, 10:09 AM
  5. Complicated ASP SQL to Access db
    By KLynch0803 in forum Programming
    Replies: 0
    Last Post: 01-31-2010, 08:32 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