October 15th, 2013, 10:30 AM
Help Limiting Records
My main table is the Product table. The parameter for the UPC field is Indexed Yes, no duplicates.
I have a table that creates a unique store key for each individual store location.
tblStoreProducts links to Product by UPC and to tblStoreInformation by StoreKey
tblStoreProducts has a primary key called StoreProductKey and it's an Autonumber field.
I need help in limiting each UPC to only occur once in a particular store. I'm not sure how to do that, since the UPC cannot be set to "no duplicates" since the same UPC could occur in 100 different stores, and I cannot set StoreKey to "no duplicates" since each store may have thousands of products.
Is it possible to limit records based on 2 fields instead of just 1?
I'm working in Access 2010
Thank you for your input.
October 15th, 2013, 12:04 PM
yes, access can do that
Originally Posted by Marcieface
you have to create a two-column index with no duplicates
i can't remember how to do that with the access wysiwyg, but i know it's possible
October 15th, 2013, 03:35 PM
Here's my SQL for the query before I created the 2-column index:
Originally Posted by r937
SELECT tblStoreProducts.UPC, tblStoreProducts.ProductKey, Product.ProductName, tblStoreProducts.StoreProductKey, tblStoreProducts.StoreKey
FROM Product INNER JOIN tblStoreProducts ON Product.ProductKey = tblStoreProducts.ProductKey
WHERE (((tblStoreProducts.UPC) In (SELECT [UPC] FROM [tblStoreProducts] As Tmp GROUP BY [UPC] HAVING Count(*)>1 )))
ORDER BY tblStoreProducts.UPC
I created a 2-column index called UniqueProduct
Access won't let me make it Unique because Access says there are duplicates. I ran the above query (created before I had created the 2-column index) and I'm not seeing any duplicates. I am seeing multiples of each ProductKey, but only once in each store.
How can I determine where the multiple products are that are preventing me from making that index unique?
October 15th, 2013, 03:44 PM
isn't gonna do it
WHERE (((tblStoreProducts.UPC) In
(SELECT [UPC] FROM [tblStoreProducts] As Tmp
GROUP BY [UPC] HAVING Count(*)>1 )))
you're just pulling all UPCs that are in more than one store
HAVING COUNT(*)>1 is correct, but what you want is a GROUP BY on the two columns (same two columns as the index)
Comments on this post