Merging Rows with Similar Values

[dannylam4]

Hello, I've got a question about merging/concatenating rows. There's a similar topic here: Combining Multiple Rows of one Field into One Result but I didn't know if I should hijack it. Basically, I have a single table in Access that looks like:

Name - - - Address - -Email - - - - -Comments
John Doe - 11211 - - - [e-mail] - lad
John Doe - 41541 - - - [e-mail] -asd
John Doe - 12345 - - - [e-mail] -ask

And what I basically want it to look like is:

Name - - - Address - - - Email - - - - - - - - - - - - - - - - - - - - - - - Comments
John Doe - 41541 - - - - [e-mail], [e-mail], [e-mail] - - lad, asd, ask

----------------------------

So I've tried this:
SELECT MAX(Table1.Address), MAX(Table1.Name), Max(Table1.Name & Table1.Email) AS emails
FROM Table1;

Similar to the solution given by kyjabber of:
1. SELECT CompanyName, Max(Concat(CompanyName,Product)) AS Products
2. FROM t_CompanyCategoriesProducts
3. GROUP BY CompanyName;

Except with & instead of Concat (because they don't have Concat in Access?), but this only seemed to produce a merging of columns, not rows:

Expr1000 Expr1001 emails
41541 John Doe John [e-mail]

Which is kinda what I had expected.

[Scott Price]

How do you know that John Doe is one person rather than three people with the same name?

When you answer this, remember that YOU may know, but how does the database know? At this point you have listed three identical names, with different addresses and different email addresses and different comments.

Any solution to your question is going to have to first of all answer this! Otherwise you will have a total mess with your database.

Regards,
Scott

[dannylam4]

How do you know that John Doe is one person rather than three people with the same name?

When you answer this, remember that YOU may know, but how does the database know? At this point you have listed three identical names, with different addresses and different email addresses and different comments.

Any solution to your question is going to have to first of all answer this! Otherwise you will have a total mess with your database.

Regards,
Scott

Right, I realize this; this is just a small snipplet of our database. I suppose, in order to make it more logical, we would have a personID. So... it'd be something like:

Name - - - Address - -Email - - - - -Comments - - ID
John Doe - 11211 - - - [e-mail] - lad - - - - - - - 1
John Doe - 41541 - - - [e-mail] -asd - - - - - - - 1
John Doe - 12345 - - - [e-mail] -ask - - - - - - - 0



Name - - - Address - - - Email - - - - - - - - - - - - - Comments - - ID
John Doe - 41541 - - - - [e-mail], [e-mail]- - lad, asd - - - - 1
John Doe - 12345 - - - - [e-mail] - - - - - - - - - - ask - - - - - - - 0

[NeoPa]

Danny, I appreciate that you have asked this question correctly (no hijack :)), so I will jump in and assist if I can. I should be able to as I was quite heavily involved in the earlier thread.

Now, what I need you to do first is to read through the whole thread, as I believe you will find your explanation of why Concat() will work for you.

It's not a native Access function at all, but one you add to your database as described in the thread. Please post back any questions if there's something you're not sure about.

[NeoPa]

I should add that you will need to link across to Combining Rows-Opposite of Union (as in kyjabber's thread) to get the information you are looking for.

[dannylam4]

Ahhh, well that certainly makes more sense... I thought that Code was doing the same thing as the SQL code, but with VB. I got the concat working though! Thanks a lot for that. And... everything looks great! Thanks a bunch guys, I appreciate the help. Curse me for not reading enough, huh? Haha

[NeoPa]

No worries Danny.

It hardly makes you stand out anyway does it ;)