Results 1 to 12 of 12
  1. #1
    caniread is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    86

    Many to Many Search

    Hello everyone,

    I have a parts locator database that has a many to many relationship. I set up a search for a part but I also want to be able to look by location. When I use the relationship I setup it makes a duplicate entry since it needs to show all the locations that part is used. Is there a way to filter down to the locations used without the split form showing more than one entry per item number?



    Thank you for your help.

    Click image for larger version. 

Name:	Form2.PNG 
Views:	14 
Size:	168.8 KB 
ID:	37322
    Click image for larger version. 

Name:	Relationships.PNG 
Views:	14 
Size:	21.9 KB 
ID:	37320
    Attached Thumbnails Attached Thumbnails Form.PNG  

  2. #2
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi, leave out the asset number and use a DISTINCT predicate, or use a group by clause but than you won't be able to update the row.

  3. #3
    caniread is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    86
    I need to also filter by asset number. Is there a way to grab all the asset numbers for the part id and put them in a field separated by commas? This way I can filter based on a like search box.

  4. #4
    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
    Can you tell us in plain English WHAT you are trying to do?
    Give an example from the data in the graphics you attached in other post.


    Don't get too involved with HOW to do it until it is clear WHAT you want to find.

    An observation:
    Your tbl_parts seems to encompass several concepts that may be hiding other entities/things.

    It includes Item_NUM, PartsID and Location. I'm a little confused with Asset_Number in a table TBL_Locations_Used.

  5. #5
    caniread is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    86
    I have never been good at explaining these types of things. There is only two real tables that contain data. TBL_PARTS & TBL_LOCATIONS_USED. The form is filtering using the fields in the header. Everything is working except that a part can be used in many locations and a location can have many parts. Using the relationship in the picture I get duplicate item numbers as they have more than one location they can be used. I would like there to be only one entry per item number but still be filterable by the asset number (locations used). To then see all the locations used there is another form that shows a datasheet view of the locations. See pic below. Hopefully, this is a better explanation.

    Click image for larger version. 

Name:	Form3.PNG 
Views:	10 
Size:	194.6 KB 
ID:	37327

  6. #6
    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
    Hmm? Not much clearer I'm afraid.

    What does Location mean in tbl_Parts?
    What differentiates a Part from an Asset?

    There is an old saying with database (and lots of other areas as well) - if you can't describe it, then nobody can build it. I'm being a little blunt here -but you know what you are trying to do better than any reader or anyone else. So do your best- in plain simple terms -WHAT are you trying to do? What exactly was your objective when you designed/wrote the code you have to do the search? Readers should not have to guess what you were trying to do by looking at physical database code that isn't accomplishing what you intended/need.

    You know I want to find XXX. The info I need is "somewhere" in these related concepts. So I should.....
    Try again. We're here to help.

  7. #7
    caniread is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    86
    Item numbers are basically parts to fix machines. The machines are assigned asset numbers. So item numbers can be used on different machines (asset_number) and machines (asset_number) can have many item numbers. All the other fields are extra data that I think you can just ignore in this case. I used many to many relationship with a junction table for the data. In the split form I would only like to see one entry per item number but still be able to filter with the asset number. I do not need to see asset number on the form or datasheet split but just be able to filter using the asset number. See the pic below for the datasheet view I get now using the current relationship. One way I was thinking was to somehow combine the asset numbers for the item number in a single field separated by commas. Do not know how to do it or if possible.

    Hopefully, this makes more sense now.
    Click image for larger version. 

Name:	Datasheet.PNG 
Views:	9 
Size:	4.6 KB 
ID:	37329

  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
    Perhaps it's just a bad day, but I have a feeling you are making things difficult for yourself. I could be way off base (happens a lot), but I don't think you have a clear understanding of your data or the requirement based on your posts.

    Let me make a couple of statements and you can ignore them or revise them to meet/restate your requirement.

    You work in some sort of factory or repair shop. You have a number of machines that are made up of various parts.
    Each Part is a type of product within some category/class. (eg, PartX is a Motor, electric,AC/DC,input/output voltages etc..) So motor X or motor Y could be used to fix Machine Q....
    Parts are identified at the Class or Model level. (ex SamsungTV-- is general; model LED 36inch,120Volt is more specific). Machines (my TV analogy) have a unique serial number.

    With databases where each table represents some entity(concept/thing..) each entry/record in the table is about the same topic. And every record in that table has a unique identifier(primary key) that distinguishes it from every other record in that table.

    What seems to be unclear is exactly what each of your tables represents. And what exactly uniquely identifies records in each table.
    In many businesses where a number of similar PCs are purchased - each may be of the same model(eg Lenovo z710), but each has its own unique serial number. And in such companies each PC will be assigned a unique AssetNumber within that company.

    You seem to have Machines which reside in different Locations and possibly a list of Parts that exist in some Warehouse/Storeroom. When a Machine breaks down/requires fixing, you need to know which Part is broken, which Part(s) and/or equivalent Part(s) may be suitable for fixing, where the proposed Parts are currently Located.

    Now some of the above is rambling just to get you to sort out a correct path through this maze.

    Good luck.

  9. #9
    caniread is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    86
    Maybe a different subject will help. A customer can order many different item #'s and an item # can have many customers. How do you make a split form that can search by item# that does not bring up the customer ID every time they have ordered that item#? Just one entry if they have ordered it at least once. I assume it needs to be done in a query but I have been thinking how but have not been able to come up with anything.

  10. #10
    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
    Your question seems to have morphed into a format for presentation.

    But I don't think that is the natural flow from your original question that was about Search.

    I don't use split forms, but again I don't think that is the key part of your post.

    When you do a select query you get back a list of fields and values.
    You would have to post-process that output to get a format that posts the title once only and provides the related details.
    And that is a feature of Reports (sorting and grouping). You can find several videos on this on youtube.

    For tables/relationships/schema generally this Barry Williams' video is recommended.

    If you want to post a copy or portion of your database with instructions, I'd be glad to look at it.
    If you want to revise my ramblings in post 8 to get a description of your requirement, I'll look at that as well.
    Good luck.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    So why do the two records for same item show the same location?

    I suppose form can use an aggregate (GROUP BY) query but that would not be an editable dataset. You could view data but not edit anything.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    caniread is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    86

    Database attached

    Here is the database. FRM_SPLIT will be the main form. When you double click the record selector it will open in detailed view. On the split form I want to see just one entry per item number but be able to search by asset number. I really do not have anything in place for that yet. The location used in the header is where I was going to put the filter. If you can concatenate all the asset numbers in the WHERE_USED field then I can use that. I was looking at the code you gave me but was not sure where everything goes. I will keep looking at the code to see if I can figure it out.
    Attached Files Attached Files

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

Similar Threads

  1. Replies: 3
    Last Post: 08-21-2018, 03:11 PM
  2. Replies: 3
    Last Post: 09-12-2016, 11:49 AM
  3. Replies: 8
    Last Post: 09-02-2015, 03:00 PM
  4. Replies: 1
    Last Post: 03-26-2015, 11:08 AM
  5. Replies: 3
    Last Post: 01-04-2015, 06:09 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