tricky query-finding multiple duplicates

[Cowboy_BeBa]

hi,

so im beginning work on a query to help me clean up redundant data that is no longer needed, the query focuses on 3 tables and im not quite sure how to go about writing it

essentially ive got 2 tables, tblProducts and tblIngredients, in between them ive got tblRecipes, standard break/list table, only has 3 fields, prodID, ingID (the primary keys for tblproducts and tblingredients) and a percentage

we've got a few duplicat recipes with different product id's, however theyll have the same ingredients and the same percentage value of that ingredient (please keep in mind some recipes may contain as few as two ingredients, others may contain as many as 20), as there are dozens of recipes it will take a while to find the duplicates manually, does anyone know of a way to write a query that will help me identify which recipes are duplicates?

 

[CJ_London]

A query along these lines will tell you which products are duplicated. It does tell you twice so for example say product1=product2 then you will get two rows, one saying product1=product2 and the next saying product2=product1

SELECT tblRecipies.prodid, tblRecipies_1.prodid, Sum(tblRecipies.pcent) AS SumOfpcent
FROM tblRecipies INNER JOIN tblRecipies AS tblRecipies_1 ON (tblRecipies.pcent = tblRecipies_1.pcent) AND (tblRecipies.ingid = tblRecipies_1.ingid)
WHERE (((tblRecipies.prodid)<>[tblRecipies_1].[prodid]))
GROUP BY tblRecipies.prodid, tblRecipies_1.prodid
HAVING (((Sum(tblRecipies.pcent))=1))

You'll need to change pcent to your field name since you did not provide this. Also this example assumes percentages are stored as decimals - i.e. 50% is stored as 0.5

 

[vbaInet]

And are you 100% sure they'll all have the same percentage value? If not, with CJ's code use Count() in place of Sum() and set the condition to be > 1. That will give you a distinct list of all the recipes that are duplicated.

However, this sounds like you can improve on the design of your data entry form. You can add some code there that will stop duplicates from being added.

 

[Cowboy_BeBa]

AWESOME, thanks CJ that did the trick

vbaInet, youre half right, there was a problem but it wasnt with the form, it was the overall design of the db, we entered copies of recipes with different (but similar) product names to represent the different products we sell (some of which are exactly the same but come in different packaging and may include extra items, like sprinkles or an icing mix), we also had new products where the recipes only varied slightly ive just built a new version of the db that allows us to create a recipe and then assign that recipe to as many products as we need to, so now im just looking for all the duplicate recipes so i can delete them and and assign a new recipe to the product theyre associated with