How to query for duplicates using two fields?

[Ian Watt]

I wonder if anyone can help?

I have a large table of un-normalized data. The table is called
Stonelist.

Three of the fields, and there are more, are called Graveyard,
Surname, and Stone. (There is also a unique ID primary key field).

The data represents the index to inscriptions on grave stones in
various locations.

So the data looks a bit like:-

Surname | Graveyard | Stone
================================
Smith | Kent | 21
===============================
Jones | Kent | 21
================================
White | Newcastle | 109
================================
Brown | Newcastle | 109
================================
Green | Newcastle | 109
=================================

(actually there are 10,000's of records and they aren't so easy to
read as that!)

I want to write a query which will tell me how many stones in whatever
graveyard have two or more surnames on them.

I'm very rusty at creating queries in Access (and even rustier in
SQL).

Ideally I'd like to check what the maximum number names on any stone
is, as well as how many have 2 names, how many have three ...... etc
up to the max - but I'd be happy just with the original query above.

Many thanks

Ian

 

[John Spencer]

SELECT GraveYard, Stone, Count(Surname) as RecordCount
FROM Graves
GROUP BY Graveyard, Stone
HAVING COUNT(SurName) >=2

In the design grid
-- Add your table
-- Add the three fields
-- SELECT View: Totals from the menu
-- Change GROUP BY TO Count under the Surname field
-- Enter the following under Surname

=2

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Ian Watt]

John

Many thanks for the rapid response.

The query works, but unfortunately it counts all the entries of
"Brown" on Newcastle Stone 109 as separate names. The query says there
are 53 surnames - which is accurate in terms of records - but if I
manually check those records there are 11 separate surnames (with
Brown appearing 20 times, Browne appearing 15 times etc).

So I appreciate that this was not my original question, but how can I
find grave stones which have more than two unique surnames occurring
on them?

Would I be better to create a new table, copying into it just the
Surname, Graveyard and Stone (omitting other fields) then finding and
removing duplicates, then running your query?

Of is there a simpler way?

Many thanks

Ian

 

[John Spencer]

IF your field and table names consist of only letters, numbers, and
underscore characters you can do this in ONE query. If not, then two
queries.

Query One: Save this as QUniqueNames
SELECT DISTINCT GraveYard, Stone, Surname
FROM Graves

Now use that to get your unique count
SELECT GraveYard, Stone, Count(Surname) as RecordCount
FROM QUniqueNames
GROUP BY Graveyard, Stone
HAVING COUNT(SurName) >=2

If your field and table names follow the naming convention mentioned
above you could do

SELECT GraveYard, Stone, Count(Surname) as RecordCount
FROM (SELECT DISTINCT GraveYard, Stone, Surname
FROM Graves) QUniqueNames
GROUP BY Graveyard, Stone
HAVING COUNT(SurName) >=2


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Ian Watt]

John

Thanks very much again.

I'll try that and see how it comes out.

I'm very grateful.

Ian