Finding duplicates

[kadams0920]

Hi,

I need to identify from a table that includes fields, product and license key, if there are any products that have the exact same set of license keys. So for instance:
product A has license keys 2, 3, 4
product B has license keys 3, 4, 5
product C has license keys 7, 8, 9
product D has license keys 2, 3, 5
product E has license keys 3, 4, 5
product F has license keys 6, 7, 8
product G has license keys 2, 3, 5

How can I determine from a query or through code that products B and E have the same set of license keys, and products D and G have the same set of keys? Each product can have more than 3 license keys or rows of data - I just provided it that way for simplicity's sake. Can this be done?

Thanks for your help.
Karyn

 

[ajetrumpet]

use the find dups query that is built in. select the keys fields, regardless of how many there are. then in select the product name in the fields where it asks you to include ANY OTHER FIELDs in the query

 

[kadams0920]

Thanks Adam, but it doesn't appear to be working. I created a find duplicates query from the query wizard and included ProductName and LicenseName. Here is the SQL query:

SELECT data_ProdLic.[ProductName], data_ProdLic.[LicenseName], data_ProdLic.ProductName
FROM data_ProdLic
WHERE (((data_ProdLic.[ProductName]) In (SELECT [ProductName] FROM [data_ProdLic] As Tmp GROUP BY [ProductName],[LicenseName] HAVING Count(*)>1  And [LicenseName] = [data_ProdLic].[LicenseName])))
ORDER BY data_ProdLic.[ProductName], data_ProdLic.[LicenseName];

Am I missing something? the ProductName won't be a duplicate. It's the set of licenseNames that I'm looking for duplicates - the ProductName will be different.

Thanks.
Karyn

 

[Brianwarnock]

Are you sure you used the find duplicates wizard, there is so much wrong I would start again and carefully follow the prompts

Brian

 

[kadams0920]

I'm attaching the database for your review. I followed the find duplicates query wizard, but am not getting the results that I want. Can you please take a look at the query and let me know what I'm doing wrong?

Much appreciated.

 

[ajetrumpet]

I'm attaching the database for your review. I followed the find duplicates query wizard, but am not getting the results that I want. Can you please take a look at the query and let me know what I'm doing wrong?

Much appreciated.

karyn,

I will respond to you later on today or tomorrow if someone doesn't help you before then. pretty busy today

 

[Brianwarnock]

Sorry I do not have 2007

Brian

 

[ajetrumpet]

karyn,

if your upload is an ACCDB, i won't be able to open it either. sorry

 

[spaddhu]

Hi. Try this query. I simulated a dupfind query in one of my databases and got the syntax. I couldn't however open the .accdb attachment.

SELECT [ProductName],[LicenseName]
FROM data_ProdLic
WHERE [ProductName] In (SELECT [ProductName] FROM [data_ProdLic] As Tmp GROUP BY [ProductName],[LicenseName] HAVING Count(*)>1 And [LicenseName] = [data_ProdLic].[LicenseName])
ORDER BY [ProductName],[LicenseName];

 

[spaddhu]

But I'm not sure if this approach is going to work if your table consists one product & license pair as one record; i.e. only two columns in the table, with products repeating for multiple licenses.
If that is the case then if
Pdt A - Lic 1
Pdt A - Lic 2
Pdt B - Lic 1
Pdt B - Lic 3
is going to give you that Pdt B duplicates Pdt A for Lic 1, without considering the others. Am not sure of this. Revert once you find out...

 

[kadams0920]

Sorry for not responding - I've had a few days off for the Thanksgiving holiday. I'm back at this and appreciate your help. Here is a converted 2003 database.

spaddhu, your query didn't work. Thanks for trying though. I hope we can figure this out.

Karyn

 

[Brianwarnock]

Your SQL should be either

SELECT First(data_ProdLic.LicenseName) AS [LicenseName Field], Count(data_ProdLic.LicenseName) AS NumberOfDups
FROM data_ProdLic
GROUP BY data_ProdLic.LicenseName
HAVING (((Count(data_ProdLic.LicenseName))>1));

or

SELECT data_ProdLic.LicenseName, data_ProdLic.ProductName
FROM data_ProdLic
WHERE (((data_ProdLic.LicenseName) In (SELECT [LicenseName] FROM [data_ProdLic] As Tmp GROUP BY [LicenseName] HAVING Count(*)>1 )))
ORDER BY data_ProdLic.LicenseName;

Dependent on the listing you require

Brian

EDit having reread post#1 I think that we are all maybe on the wrong tack, but I wont be able to have another go today, maybe tomorrow.

 

[Brianwarnock]

Well your Licence keys turned out to be text and many products have many licences , one has 35.

To do what you want I think that you would have to build a string of the licences for each product

eg for Aspen Air Cooled Exchanger

AcolPlusAspenMetalsAspenProps

The licences would need to be in alphabetic order within Product, then you can look for the duplicates.

The build of these strings would need to be done by code.

You might be better building a conversion table to convert the licence text to a unique number and using those .
You would have to delinate these 1,12,321

SELECT data_ProdLic.LicenseName INTO tblLicenceNumber
FROM data_ProdLic
GROUP BY data_ProdLic.LicenseName
ORDER BY data_ProdLic.LicenseName;

Then in design view of this table insert a column set to Autonum.

Best of luck as I'm running out of steam here.

Brian

 

[apr pillai]

I made an attempt to find a solution to your problem.

1. Created a Query Grouped on License Keys and taking count and filtering the license keys having more than one instance in the table.

2. Created a second Query using Query1 as source along with the Table and cross-linked on License keys that matches and inserting other required fields.

Your database with Query1 and Query2 is attached. Please check the contents of Query2.

I hope this will solve your problem.

 

[Brianwarnock]

I don't think that is what the poster wants, the 2 below match on 2 Licence names but not the 3rd so no match, maybe kadams0920 will come back and confirm this.

ProductName..................... LicenseName
Aspen Air Cooled Exchanger.. AspenProps
Aspen Air Cooled Exchanger.. AspenMetals
Aspen Air Cooled Exchanger.. AcolPlus
Aspen Fired Heater .............FiredHeater
Aspen Fired Heater .............AspenProps
Aspen Fired Heater .............AspenMetals


Brian