concatenate rows

  • Thread starter Thread starter db
  • Start date Start date
D

db

hopefully someone can help...

[ResID] contains resource ID, there are duplicates within this column
[Summary_ID] is a unique id for the ResID column
[DBTYPE] = the name of the source database the ResID came from, this is why
there are duplicates in ResID. for example, ResID = "55-1" and there are
three occurances of that ID because it's listed in three source databases:
UST, PRT, & NRCS.

so what i want is a table that will take this:

ResID Summary_ID DBTYPE
55-1 25 UST
55-1 26 PRT
55-1 27 NRCS
12-58 28 PRT
9-25 29 IPL
9-25 30 NRCS

and turn it into this:

ResID DB
55-1 UST, PRT, NRCS
12-58 PRT
9-25 IPL, NRCS


i've been trying to work through Duane Hookom's example found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

but haven't had much luck.

thanks for any help,
dave
 
Your Concatenate() syntax would be
SELECT ResID, Concatenate("SELECT DBType FROM tblYourTable WHERE ResID = """
& [ResID] & """") as DB
FROM tblYourTable
GROUP BY ResID, Concatenate("SELECT DBType FROM tblYourTable WHERE ResID =
""" & [ResID] & """");
 
It worked!! thanks duane. so, why does it work?


Duane Hookom said:
Your Concatenate() syntax would be
SELECT ResID, Concatenate("SELECT DBType FROM tblYourTable WHERE ResID = """
& [ResID] & """") as DB
FROM tblYourTable
GROUP BY ResID, Concatenate("SELECT DBType FROM tblYourTable WHERE ResID =
""" & [ResID] & """");

--
Duane Hookom
MS Access MVP
--

db said:
hopefully someone can help...

[ResID] contains resource ID, there are duplicates within this column
[Summary_ID] is a unique id for the ResID column
[DBTYPE] = the name of the source database the ResID came from, this is why
there are duplicates in ResID. for example, ResID = "55-1" and there are
three occurances of that ID because it's listed in three source databases:
UST, PRT, & NRCS.

so what i want is a table that will take this:

ResID Summary_ID DBTYPE
55-1 25 UST
55-1 26 PRT
55-1 27 NRCS
12-58 28 PRT
9-25 29 IPL
9-25 30 NRCS

and turn it into this:

ResID DB
55-1 UST, PRT, NRCS
12-58 PRT
9-25 IPL, NRCS


i've been trying to work through Duane Hookom's example found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

but haven't had much luck.

thanks for any help,
dave
Click to expand...
Click to expand...
 
The Group By gets rid of duplicates.

--
Duane Hookom
MS Access MVP
--

db said:
It worked!! thanks duane. so, why does it work?


Duane Hookom said:
Your Concatenate() syntax would be
SELECT ResID, Concatenate("SELECT DBType FROM tblYourTable WHERE ResID = """
& [ResID] & """") as DB
FROM tblYourTable
GROUP BY ResID, Concatenate("SELECT DBType FROM tblYourTable WHERE ResID =
""" & [ResID] & """");

--
Duane Hookom
MS Access MVP
--

db said:
hopefully someone can help...

[ResID] contains resource ID, there are duplicates within this column
[Summary_ID] is a unique id for the ResID column
[DBTYPE] = the name of the source database the ResID came from, this
Click to expand...
is
why
there are duplicates in ResID. for example, ResID = "55-1" and there are
three occurances of that ID because it's listed in three source databases:
UST, PRT, & NRCS.

so what i want is a table that will take this:

ResID Summary_ID DBTYPE
55-1 25 UST
55-1 26 PRT
55-1 27 NRCS
12-58 28 PRT
9-25 29 IPL
9-25 30 NRCS

and turn it into this:

ResID DB
55-1 UST, PRT, NRCS
12-58 PRT
9-25 IPL, NRCS


i've been trying to work through Duane Hookom's example found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

but haven't had much luck.

thanks for any help,
dave
Click to expand...
Click to expand...
Click to expand...
 
Back
Top