#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    9
    Rep Power
    0

    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.

    Marcie Fessler
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    Originally Posted by Marcieface
    I need help in limiting each UPC to only occur once in a particular store.
    yes, access can do that

    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    9
    Rep Power
    0
    Originally Posted by r937
    yes, access can do that

    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
    Here's my SQL for the query before I created the 2-column index:

    Code:
    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?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    this part...
    Code:
    WHERE (((tblStoreProducts.UPC) In 
      (SELECT [UPC] FROM [tblStoreProducts] As Tmp 
       GROUP BY [UPC] HAVING Count(*)>1 )))
    isn't gonna do it

    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

    • Marcieface agrees : Thank you! Your input put me on the right track. It's all fixed now and unique indexes are in place.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo