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
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
Last Comment
Gordon Hughes
ASKER CERTIFIED SOLUTION
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.
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
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
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.
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.
ASKER
It put me on the correct path to sort the issue
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