Page 1 of 3 123 LastLast
Results 1 to 15 of 44
  1. #1
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86

    Unique values across 2 fields from different tables

    Hi



    I have two fields from different tables that need to have unique values between them. Is there a way to achieve this?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Values in each table cannot be in other table? Why? What is data structure?

    Probably not without VBA code.
    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.

  3. #3
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86
    I have 2 tables as follows...

    Click image for larger version. 

Name:	Query.PNG 
Views:	33 
Size:	7.5 KB 
ID:	30442

    The 'product' & 'allocated_bin' fields in 'tblProduct' are unique to prevent products being double located.
    There is an exception to this rule which I want to allow for, sometimes we use a 2nd location for a product as an overflow.

    They way I've thought about working around this is add a 2nd field 'allocated_bin_2' to allow for the 2nd location or to create another 'tblProduct2' and list the overflow locations in there.
    Either way I must be able to make the allocated bins unique between the 2 fields/tables and to be able to show them in a merged query list as one set of locations.
    I experimented with both but have hit snags either way.

    Do you have any ideas as the best way to implement this?
    Last edited by anthgav; 09-24-2017 at 04:33 PM.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The second field should be workable if you never need more than two locations. A UNION query can rearrange the two fields to show as one list. Otherwise, use a dependent table to save a record for each bin associated with a product.
    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.

  5. #5
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86
    I've decided to use 2 fields. I'm currently struggling to make the values across the 2 fields unique.

    Here is my relationship, tblAllocatedBin & tblAllocatedBin_1 are actually the same table.
    Click image for larger version. 

Name:	Query.PNG 
Views:	32 
Size:	16.6 KB 
ID:	30459

    I found information about making 2 fields unique and have implemented it in the indexes window below...
    Click image for larger version. 

Name:	Query 2.PNG 
Views:	30 
Size:	12.1 KB 
ID:	30460

    Despite this configuration I am still able to select an ID in allocated_bin_2 that exists in allocated_bin_1 field. Any idea why?

  6. #6
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86
    I think I misunderstood that last process, I thought it meant no duplicates over the 2 fields but but it's actually if a pair of the 2 fields match. So I need to find another way of doing it.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Right, don't want the same bin in both fields for the same product. Use a combobox for each bin field. Can either rely on user to not replicate entries or use combination combobox properties and code to manage. Set the second combobox as disabled by default. Use combobox Conditional Formatting to enable second combobox only if value selected in first combobox. Set up each combobox RowSource to include only items not selected in other field. This called cascading or dependent comboboxes, only in this case the dependency is bi-directional. Keep in mind cascading comboboxes with alias don't work nice in continuous or datasheet form.
    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.

  8. #8
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86
    I have set up the 2nd combo box which controls the 2nd bin field. I'm able to filter any bins that already exists in the first field with this query...
    Code:
    SELECT tblAllocatedBin.allocated_bin_id, tblBin.bin, tblBinType.bin_type, tblProduct.product
    FROM tblBinType RIGHT JOIN (tblBin LEFT JOIN (tblAllocatedBin LEFT JOIN tblProduct ON tblAllocatedBin.allocated_bin_id = tblProduct.[allocated_bin_1]) ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON tblBinType.bin_type_id = tblAllocatedBin.allocated_bin_type
    WHERE (((tblAllocatedBin.allocated_bin_id) Not In ([tblProduct].[allocated_bin_1])))
    ORDER BY tblBin.bin, tblBinType.priority;
    I want to filter any fields that exist in allocated_bin_1 and allocated_bin_2. I've tried different clauses to try and achieve this but I can't get that to work. How do I write the WHERE clause to filter out both fields for the combobox?

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Instead of Not In, try <>.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    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.

  10. #10
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86
    I tried <> but the combo box list goes empty. I've attached my database, the form in question is 'frmProduct'. I have a query in Secondary Bin that filters entries from the Master Bin field but I can not get it to work for filter 2 fields.
    In the table 'tblProduct' there are 2 fields called 'allocated_bin_1' and 'allocated_bin_2', these are the 2 fields I'm trying to filter out of the combo box list.

    Thanks

    Planner.zip

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Didn't provide the linked worksheet. So don't have that data for testing. But might not be needed.

    I had to run Compact & Repair because VBA was not finding the Access objects library.

    The combobox RowSource criteria must reference the form and combobox, not the table and field: <>[Forms]![frmProduct]![allocated_bin_1]. Sorry I did not notice that in your posted SQL, should have but a case of seeing what was expected. That should prevent same bin selected for both allocated fields.

    Now I suppose a bin should not be available for selection if it is already used for any record in either of the allocated fields? This gets complicated.

    Advise giving bound controls a name different from the fields they are bound to, such as: cbxBin1

    JOIN between tblBin and tblAllocatedBin should be changed to: "Include all records form tblAllocatedBin ..."

    Why are allocated_bin_1 values duplicated in the dropdown list for two different bin descriptions? And the second combobox has multiple allocated_bin_1 and bin pairs. Should there be duplicate pairs in tblAllocatedBin?
    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
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86
    I've attached the linked worksheet, sorry I missed that off.

    Product Data.zip

    I realised the importance of naming the bound controls as i started using Access after a while and it's on my to do list, I'll make that a priority to make things easier for me.

    Certain bin numbers can have more than one location in them, it depends how many bin types I allocate to that bin number, for example I could have 4 bins named QCEBIN with a 5th bin called BOTTOMCATEYE all in the same bin number, it's purposely setup like that.

    What I need to achieve on this form is for Primary Bin & Secondary Bin combo boxes to list available bins and to filter out bins that are already allocated to stock. So both combo boxes need to filter out 'tblProduct.allocated_bin_1' & 'tblProduct.allocated_bin_2' fields from the drop down list.

    I hope all that makes sense.

  13. #13
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86
    What's puzzling me about this is that this query works...
    Code:
    Not In ([tblProduct].[allocated_bin_1])
    but when I try it on the bin 2 field...
    Code:
    Not In ([tblProduct].[allocated_bin_2])
    it doesn't work.


    Why would it work on one field but not another?

  14. #14
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Rather than have a LOT of empty fields in one table, I think I would have had a table for overflow since it seems to be the exception, not the rule. Methinks that is more normalized anyway. A query could pull any product from both tables, showing overflow as a separate field. It would also easily allow you to find the overflow only, by querying that table without having to look in the main table for Is Not Null. This would also easily permit removing overflow stock first since all you'd have to do is remove the record rather than update the main table overflow data with Null.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86
    Quote Originally Posted by Micron View Post
    Rather than have a LOT of empty fields in one table, I think I would have had a table for overflow since it seems to be the exception, not the rule. Methinks that is more normalized anyway. A query could pull any product from both tables, showing overflow as a separate field. It would also easily allow you to find the overflow only, by querying that table without having to look in the main table for Is Not Null. This would also easily permit removing overflow stock first since all you'd have to do is remove the record rather than update the main table overflow data with Null.
    Thanks for that, I'll have a go that way tomorrow. So are you saying the query isn't working on the overflow field because of the empty values?

    Sent from my HTC 10 using Tapatalk

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Matching 2 tables with no unique fields
    By ernest_rhaniel in forum Queries
    Replies: 15
    Last Post: 03-21-2017, 11:28 PM
  2. Replies: 1
    Last Post: 02-09-2016, 09:03 PM
  3. Select unique values from multiple fields
    By MrDummy in forum Queries
    Replies: 2
    Last Post: 12-07-2015, 01:42 PM
  4. Replies: 5
    Last Post: 03-17-2014, 04:02 PM
  5. Unique Values and Boolean Fields
    By Triad in forum Forms
    Replies: 1
    Last Post: 07-15-2010, 06:28 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