SQL Statement

Question

I suddenly find myself in a pinch...

I need a SQL statement which does a couple of things.  A table contains the fields First_Name and Last_Name.  I need the statement to first combine the two and then count how many times the combination occurs in the results.  For me the first part seems simple:

select Last_Name + ',' + ' ' + First_Name AS Full_Name FROM <table>

What I am have a time getting done is Grouping by the results and then counting how many times Full_Name occurs.

Oh yeah, I forgot...I am trying to do this in Excel 2002 VBA.

Thank you in advance.

Answer 1

The SQL would look like this:

SELECT [First_Name] & ' ' & [Last_Name] AS Full_Name, Count(*) AS N
FROM [TableName]
GROUP BY [First_Name] & ' ' & [Last_Name]

You can use DAO or ADO to connect to a database, and opening a recordset on the above SQL string.

---

Regards, Hans Vogelaar

Answer 2

I knew there was someone out there who could answer this!

Thank you very much!

Answer 3

One last thing...How to get only those records which appear more than once?

Answer 4

Use the following SQL:

SELECT [First_Name] & ' ' & [Last_Name] AS Full_Name, Count(*) AS N
FROM [TableName]
GROUP BY [First_Name] & ' ' & [Last_Name]
HAVING Count(*)>1

---

Regards, Hans Vogelaar

Answer 5

OK...so now the query is giving me fits!

By itself, as written, it works fine but I need to to add another field to the results (Email Address) but the table has disappeared from the query build view (MS Excel) and I have tried everything I know to get this field added.

The query should still combine First_Name and Last_Name, count the number of times the combination appears and return only those which appear more than once.  If possible I would like them in order of Full_Name, Email Address and Quantity > 1.

I know this is SQL 101, but I am stumped.

Answer 6

Try creating the query anew.

Bringing in the email address could be a problem - what do you want to do if there are records with the same full name but different email addresses?

---

Regards, Hans Vogelaar

Answer 7

Thank you for responding...It would be permissible to duplicate the email address since there are those which share email addresses for some reason.

Answer 8

Just re-read you question...It would be fine if same person has multiple email addresses.  Lord knows I have half a dozen myself...

Answer 9

OK, but if you add the e-mail field to the query and group on it, you'll get a separate record for each full name - email address combination, say:

Full_Name  Email                                  N
John Doe    john.doe @ hotmail.com    1
John Doe    john.doe @ gmaiil.com      3

That might not be what you want. Or is it?

---

Regards, Hans Vogelaar

Answer 10

This would be acceptable.  I can compare it to the results of another query to make sure which one is correct.

Answer 11

Try this:

SELECT [First_Name] & ' ' & [Last_Name] AS Full_Name, [EmailAddress], Count(*) AS N
FROM [TableName]
GROUP BY [First_Name] & ' ' & [Last_Name], [EmailAddress]
HAVING Count(*)>1

where EmailAddress is the name of the email field. If you prefer to have one record per full name, you could return an arbitrary email address:

SELECT [First_Name] & ' ' & [Last_Name] AS Full_Name, 
First([EmailAddress]) AS AnEmailAddress, Count(*) AS N
FROM [TableName]
GROUP BY [First_Name] & ' ' & [Last_Name]
HAVING Count(*)>1

---

Regards, Hans Vogelaar

Answer 12

Thank you.  I promise not to bug you again on this.