Query not returning expected results

[MarcieFess]

Background: I have a table that doesn't have unique records but it should. The only way to make the records unique is to combine 2 fields in an index. I've done that, but it won't let me make the index Unique because it says I have duplicate records. I don't see any.

Before modifying the table with the 2-column index, I had created this query:

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;

Running the query, I just don't see the duplicates.

Someone has suggested that I need to somehow change the Group By to group by the fields that will be grouped in the index (StoreKey and ProductKey). I have no idea how to do that.

Here is the issue. I have a table (Product) that contains 1 unique entry for every UPC. Information unique to that product is stored here. The UPC is unique, and ProductKey (an Autonumber field) is the primary key

I have another table (tblStoreProducts) that keeps track of which products are in a particular store. UPC is not unique, as it will occur in this table as many times as there are stores that carry that UPC. Likewise, ProductKey is not unique, for the same reason.

StoreKey is the store identifier. It, again, is not unique in this table as one store will have hundreds of product and thus an entry in that store for each product.



I created an 2-field Index on StoreKey and ProductKey but Access says I have duplicates so it won't let me make that a unique index.

How do I tweak the above query to find the duplicates? It appears to me that it's just showing me each store that a particular UPC exists in.

The only reason I have it linked to the Product table is so that I can pull in the product name to the results so I know what products we are dealing with.

Any suggestions?

Marcie

[Dal Jeanis]

First, I think you may have a misunderstanding of what a unique key is used for.
There's nothing wrong with tblStoreProducts having duplicates, for example, of StoreKey. In that table, StoreKey is a data field. If you feel you need a unique key in that table, then add an autokey field. I generally do, but it's not crucial. If you feel you need a unique index, then you can create a dual-field index field, but I generally don't do that. I would, instead, always read for existence before I tried to add or subtract. But, that's a style thing - read before, or error-trap after.
Here's an approach to solve your right-now problem.
1) Back up your database and do this in a test copy.
2) Create a second table with the same structure.
3) Add the unique dual-field index.
4) Insert the old table data into the new table. Some records will error off.
5) Update the new table with the SUM of the old table inventory records.
6) Replace the old table data with the new table data.
7) Add the unique dual-field index to the old table.

[Dal Jeanis]

This will find which Products are duplicated at each store. You'll have to add in code to get the store name, if you want it.

SELECT
   TP.ProductName,
   TS.ProductKey,
   TS.StoreKey,
   TS.UPC,
   TS.DupCount
FROM  
   Product AS TP
   INNER JOIN
     (SELECT [StoreKey], [UPC], First([ProductKey]) AS ProductKey, Count([ProductKey]) As DupCount    
      FROM [tblStoreProducts]
      GROUP BY [StoreKey], [UPC] 
      HAVING Count(*)>1) AS TS
   ON TS.ProductKey = TP.ProductKey;

[Dal Jeanis]

Got your message. Glad it worked. Closing thread.