Hi
I have two fields from different tables that need to have unique values between them. Is there a way to achieve this?
Hi
I have two fields from different tables that need to have unique values between them. Is there a way to achieve this?
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.
I have 2 tables as follows...
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.
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.
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.
I found information about making 2 fields unique and have implemented it in the indexes window below...
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?
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.
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.
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...
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?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;
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.
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
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.
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.
What's puzzling me about this is that this query works...
but when I try it on the bin 2 field...Code:Not In ([tblProduct].[allocated_bin_1])
it doesn't work.Code:Not In ([tblProduct].[allocated_bin_2])
Why would it work on one field but not another?
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.
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?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.
Sent from my HTC 10 using Tapatalk