Avatar of Gordon Hughes
Gordon Hughes
Flag for United Kingdom of Great Britain and Northern Ireland asked on

how to expand a wizard query

I have a table where I want to indentify duplicates, for which I can create a query using the query wizard
which give me the following formula for the 'Manufactures Item No' field
In (SELECT [Manufactures Item No] FROM [A DairyCrest Master List] As Tmp GROUP BY [Manufactures Item No],[Manufacturer] HAVING Count(*)>1  And [Manufacturer] = [A DairyCrest Master List].[Manufacturer]) And <>'#'
What I would like to include where the 'New Description is not the same, but don't know what the query should be
Gordon
Microsoft Access

Avatar of undefined
Last Comment
Gordon Hughes

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
peter57r

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Gordon Hughes

ASKER
Hi peter57r

Good to hear from you
Not sure what to do with your formula
I have a query which identifies the duplicates by the Manufactures Item No' field I have named this query
Duplicates in A DairyCrest Master List by Man Items No

I can create another query based on this query which currently shows the same data, but do not know where to put the formula you have shown, the field I am trying to show only where the diffeneces is call 'New Description'

Please bear with me
Gordon
Gordon Hughes

ASKER
Peter57r

This give me the same New DescriptionsIn (SELECT [New Description] FROM [A DairyCrest Master List] As Tmp GROUP BY [New Description] HAVING Count(*)>1 )
Need the reverse of this
Gordon
peter57r

Forget about your current query- it can't be made to do what you want.

You have to create two new queries as I indicated in my previous response.

First create a simple groupBy query. Save as qry1
Select the Item No and New description fields and then convert the query to a groupby query.
This will show you a list of all combinations of the two fields that has been found.

You then create another groupby query.Start from qry1.
Add both fields to the result grid and again convert to a groupby query.
This time change the New Description column to Count.  And set the criteria for this column to >1

That will show you all the values of Item no which have two or more descriptions.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Gordon Hughes

ASKER
It put me on the correct path to sort the issue