Duplicates

[Dick7Access]

In my coin tracking db, I want to view only coins that have same

Country, year and face value. I tried a duplicate query but that just brings up duplicate
Countries and all the years, and all face values. I have duplicate coins, same country, same year, and same face value. How do I get such a list/query/?

 

[plog]

In my coin tracking db, I want to view only coins that have same Country, year and face value.

Just to state it better:

1. A duplicated coin has the same country, year and face value of at least 1 other coin.

2. You want a list of duplicated coins. If it is a unique coin it should not be in the list.

This will take a sub query to identify the duplicates:

SELECT coin_Year, coin_Country, coin_Value
FROM YourTableNameHere
GROUP BY coin_Year, coin_Country, coin_Value
HAVING COUNT(coin_Year)>1

Then to get all the other data in YourTableNameHere you make a query on it and the above query, linking them on the 3 fields in the above query. Bring in all the fields from YourTableNameHere and you have your list.

 

[Dick7Access]

Thanks

 

[Dick7Access]

A little closer, but not quite there.

SELECT coinyear, country, faceValue
FROM tblFcoins
GROUP BY coinyear, country, faceValue
HAVING count(coinyear)>1;

Here is the results: Notice that
It list (2) 1941 France but 1 is (1) Franc and the Second is (2) Francs
I want to see all the 1941 France that are 1 Franc, and all coins that have same Country, year and faceValue

Query1​
Year​	Country​	Face Value​
1941	France	1 Franc
1941	France	2 francs
1947	France	5 Franc
1949	Hong Kong	10 Cents
1951	Italy	10 Lira
1958	France	10 Francs
1958	Hong Kong	50 Cents
1959	Hong Kong	10 Cents
1966	Spain	1 Peseta
1969	Philippines	1 Sentimo
1971	Great Britian	1 Penny
1972	Jamaica	1 Cent
1975	India	2 Paise

 

[plog]

That's the first query I discussed. Now make the second one I outlined in the last paragraph.

 

[Dick7Access]

That's the first query I discussed. Now make the second one I outlined in the last paragraph.

I may not be understanding your directions.
(YOU) Then to get all the other data in YourTableNameHere you make a table on it.
first I don't want all the other data. Second by Saying "make a table on it" I am assuming you mean (make table query" Am I correct?
Third (YOU) “linking them on the 3 fields” I assuming that you mean the source for the "make table query" will be the original query. Am I correct?

 

[cheekybuddha]

PMFJI,

>> (YOU) Then to get all the other data in YourTableNameHere you make a table on it. <<

I don't think that's what plog said:
>> Then to get all the other data in YourTableNameHere you make a query on it and the above query, linking them on the 3 fields in the above query <<

You don't need to make a new table - just another query:

SELECT
  *
FROM tblFcoins t1
INNER JOIN (
  SELECT coinyear, country, faceValue
  FROM tblFcoins
  GROUP BY coinyear, country, faceValue
  HAVING count(coinyear)>1
) t2
        ON t1.coinyear = t2.coinyear
       AND t1.country = t2.country
       AND t1.faceValue = t2.faceValue
;

hth,

d

 

[theDBguy]

PMFJI,

>> (YOU) Then to get all the other data in YourTableNameHere you make a table on it. <<

I don't think that's what plog said:
>> Then to get all the other data in YourTableNameHere you make a query on it and the above query, linking them on the 3 fields in the above query <<

You don't need to make a new table - just another query:

SELECT
  *
FROM tblFcoins t1
INNER JOIN (
  SELECT coinyear, country, faceValue
  FROM tblFcoins
  GROUP BY coinyear, country, faceValue
  HAVING count(coinyear)>1
)
        ON t1.coinyear = t2.coinyear
       AND t1.country = t2.country
       AND t1.faceValue = t2.faceValue
;

hth,

d

@cheekybuddha Missing t2?

 

[Dick7Access]

PMFJI,

>> (YOU) Then to get all the other data in YourTableNameHere you make a table on it. <<

I don't think that's what plog said:
>> Then to get all the other data in YourTableNameHere you make a query on it and the above query, linking them on the 3 fields in the above query <<

You don't need to make a new table - just another query:

SELECT
  *
FROM tblFcoins t1
INNER JOIN (
  SELECT coinyear, country, faceValue
  FROM tblFcoins
  GROUP BY coinyear, country, faceValue
  HAVING count(coinyear)>1
)
        ON t1.coinyear = t2.coinyear
       AND t1.country = t2.country
       AND t1.faceValue = t2.faceValue
;

hth,

d

Thanks for answering. I will try it as soon as I finish feeding my face.

 

[isladogs]

Perhaps I'm missing something here but I can't see why you can't achieve this in one query using the duplicates query wizard.
Set all three fields as having potentially duplicate data. Job done.

 

[cheekybuddha]

Missing t2?

Ta, DBG!

Edited to correct.

 

[Dick7Access]

Perhaps I'm missing something here but I can't see why you can't achieve this in one query using the duplicates query wizard.
Set all three fields as having potentially duplicate data. Job done.

Thanks for answering!
Sorry to be so forgetful. 20 years ago I had built a great “Find Dups” in my church DB. I could view each church that had a dup by church name city and state in a split screen. I could then delete the one I didn’t like based on other data. However, I have deleted that query along the way, so I can’t go back and look at it. I have just googled on how to “Set all three fields as having potentially duplicate data.” Can’t remember, can’t find on Google. Please instruct.

 

[Dick7Access]

Wow! Something drastically wrong here. I just did a refresher on Find Duplicate Query. It should be a piece of cake. I made a find dup’s in several of my DB’s. Same thing, will not pull up duplicate records

 

[isladogs]

The wizard does make it very easy. You can if necessary tweak it further.
Hopefully you can complete this without further assistance. Let us know how you get on.

 

[Dick7Access]

The wizard does make it very easy. You can if necessary tweak it further.
Hopefully you can complete this without further assistance. Let us know how you get on.

Thanks for answering. I have no idea how to tweak a find duplicate query and could not find anything googling for information.

 

[cheekybuddha]

Hi, it's not clear from your responses exactly what is not working.

Did you try the query in Post #7 ?

What results are you seeing, and what results are you expecting? Remember, we can't see the data in your tables!

Perhaps post a sample db with just some representative data from tblFcoins and we can try and reproduce your issues.

 

[Dick7Access]

My sincere and humble apology, (Please forgive an old man). It wasn’t the query that wasn’t working. It was the person reading it. All I had to do was add ID to (other fields) and it gave me the desired results.

PS. I plan on publishing this once I get all my coins categorized anyways, so your welcome to look at it.

 

[isladogs]

Glad to hear you got it working.
For info, all I meant by 'tweak' in my earlier post was that having used the duplicates wizard, you could then make further changes to your query in design view if necessary.