appending different data from same field into a single field (I th

[Guest]

Hi, and thanks for reading my question.

I have 3 tables. The first I'll call "Top" for this explanation. It's the
main table and has the majority of fields I'll be needing for my query. It's
primary key I'll call "Server."

My "2nd" and "3rd" tables also have the field "Server" and have many entries
for each Server, thus I have a one-to-many relationship with both of these
tables and my "Top" table.

Here's the scenario, I've created a query using SELECT DISTINCT but I'm
still returning way too many duplicates of "Server" still. My "2nd" table has
Points of Contact. There are many points of contact for a single "Server." My
"3rd" table has Services provided by that server. There are many services for
a single "Server."

The query looks like this taking out the extra fields:

<Server> <Service> <Point of Contact>

Here's an example of my output, even with SELECT DISTINCT in my query:

1. Server1; Active Directory; Gerry Stew
2. Server1; Activer Directory; Tim Burkins
3. Server1; DNS; Gerry Stew
4. Server1; DNS; Tim Burkins

At last, my question, would it be possible to edit this query (or create a
view) so there is only one row for Server1, the <Service> fields are combined
to say, "Active Directory, DNS" and the <Point of Contact> fields are
combined to say, "Gerry Stew, Tim Burkins?" This would look like this:

<Server> <Service> <Point of Contact>
1. Server1; Active Directory, DNS; Gerry Stew, Tim Burkins

Thanks a lot!

Sincerely,
Blenvid

 

[John Spencer]

You can use code that MVP Duane Hookom has.

Quoting Duane Hookom

I use a generic Concatenate() function. The code is listed below with both
ADO and DAO. There are comments regarding which lines to comment or
uncomment based on which library you prefer. Access 97 is mostly DAO while
the default for 2000 and newer is ADO.

See:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

You would use that once for each of the calculated columns you have.

 

[Guest]

That's exactly what I'm looking for. Thanks a lot for the information John!

Sincerely,
Blenvid