Concatenate a list of names from same field

  • Thread starter Thread starter Wendy Simes
  • Start date Start date
W

Wendy Simes

I have looked at various examples but don't seem to be able to get a
result.
I have a table called AuthorLnkID table with two fields AuthorLnkID
and ItemLnkID. I want to tell a query to concatenate AuthorLnkID
where they have the same ItemLnkID.
The object of the exercise is to have under the title of the book the
author's names appearing in a single row in the final report.
 
Hello Wendy.
(Can we fix it? - Yes we can! ;-))

Wendy said:
I have looked at various examples but don't seem to be able to
get a result.
I have a table called AuthorLnkID table with two fields AuthorLnkID
and ItemLnkID. I want to tell a query to concatenate AuthorLnkID
where they have the same ItemLnkID.
The object of the exercise is to have under the title of the book the
author's names appearing in a single row in the final report.
Click to expand...

I guess that there also is a table "Author" with AuthorName (Text)
and AuthorID (AutoNumber) fields?
You could create a query (qryAutorsForItem) that supports a parameter
(parItemID) and that lists the names of the autors for that itemID:

PARAMETERS parItemID Long;
SELECT Author.AuthorName FROM Author INNER JOIN
AuthorLnkID ON Author.AuthorID = AuthorLnkID.AuthorLnkID
WHERE (((AuthorLnkID.itemLnkID)=[parItemID]));

Then create a function in a module that returns the list (using DAO):

Public Function AuthorsForItem(ItemID As Long) As String
Dim strNames As String
Dim rst As DAO.Recordset
With CurrentDb.QueryDefs("qryAutorsForItem")
.Parameters("parItemID") = ItemID
Set rst = .OpenRecordset(dbOpenForwardOnly, , dbReadOnly)
With rst
Do Until .EOF
strNames = strNames & ", " & !AuthorName
.MoveNext
Loop
.Close
End With
.Close
End With
Set rst = Nothing
If strNames <> "" Then
AuthorsForItem = Mid$(strNames, 3)
End If
End Function

You can use this function in a query or (I'd prefer) in the report.
 
Thanks for all your help but I think I am out of my depth here.
Yes there is a table for Authors called [Author List].
When I make the relevant changes to table names I get syntax error
messages starting with a error message for PARAMETERS parItemID Long;

Hello Wendy.
(Can we fix it? - Yes we can! ;-))

Wendy said:
I have looked at various examples but don't seem to be able to
get a result.
I have a table called AuthorLnkID table with two fields AuthorLnkID
and ItemLnkID. I want to tell a query to concatenate AuthorLnkID
where they have the same ItemLnkID.
The object of the exercise is to have under the title of the book the
author's names appearing in a single row in the final report.
Click to expand...

I guess that there also is a table "Author" with AuthorName (Text)
and AuthorID (AutoNumber) fields?
You could create a query (qryAutorsForItem) that supports a parameter
(parItemID) and that lists the names of the autors for that itemID:

PARAMETERS parItemID Long;
SELECT Author.AuthorName FROM Author INNER JOIN
AuthorLnkID ON Author.AuthorID = AuthorLnkID.AuthorLnkID
WHERE (((AuthorLnkID.itemLnkID)=[parItemID]));

Then create a function in a module that returns the list (using DAO):

Public Function AuthorsForItem(ItemID As Long) As String
Dim strNames As String
Dim rst As DAO.Recordset
With CurrentDb.QueryDefs("qryAutorsForItem")
.Parameters("parItemID") = ItemID
Set rst = .OpenRecordset(dbOpenForwardOnly, , dbReadOnly)
With rst
Do Until .EOF
strNames = strNames & ", " & !AuthorName
.MoveNext
Loop
.Close
End With
.Close
End With
Set rst = Nothing
If strNames <> "" Then
AuthorsForItem = Mid$(strNames, 3)
End If
End Function

You can use this function in a query or (I'd prefer) in the report.
Click to expand...
 
Yes I have been to the site mentioned but I really think I am out of
my depth here and will have to stick with the authors appearing on
seperate lines. I do not have enough programming experience.
 
Hello Wendy.

Wendy said:
Wolfgang said:
Wendy said:
I have looked at various examples but don't seem to be able to
get a result.
I have a table called AuthorLnkID table with two fields AuthorLnkID
and ItemLnkID. I want to tell a query to concatenate AuthorLnkID
where they have the same ItemLnkID.
The object of the exercise is to have under the title of the book the
author's names appearing in a single row in the final report.
Click to expand...
I guess that there also is a table "Author" with AuthorName (Text)
and AuthorID (AutoNumber) fields?
You could create a query (qryAutorsForItem) that supports a parameter
(parItemID) and that lists the names of the autors for that itemID:

PARAMETERS parItemID Long;
SELECT Author.AuthorName FROM Author INNER JOIN
AuthorLnkID ON Author.AuthorID = AuthorLnkID.AuthorLnkID
WHERE (((AuthorLnkID.itemLnkID)=[parItemID]));

Then create a function in a module that returns the list (using DAO):

Public Function AuthorsForItem(ItemID As Long) As String
Dim strNames As String
Dim rst As DAO.Recordset
With CurrentDb.QueryDefs("qryAutorsForItem")
.Parameters("parItemID") = ItemID
Set rst = .OpenRecordset(dbOpenForwardOnly, , dbReadOnly)
With rst
Do Until .EOF
strNames = strNames & ", " & !AuthorName
.MoveNext
Loop
.Close
End With
.Close
End With
Set rst = Nothing
If strNames <> "" Then
AuthorsForItem = Mid$(strNames, 3)
End If
End Function

You can use this function in a query or (I'd prefer) in the report.
Click to expand...
Click to expand...
Thanks for all your help but I think I am out of my depth here.
Yes there is a table for Authors called [Author List].
When I make the relevant changes to table names I get syntax
error messages starting with a error message for
PARAMETERS parItemID Long;
Click to expand...

Where did you enter this text?
Open a new query in design view, close the add table window,
Klick SQL View in the view menu, then enter the block with
PARAMETERS and SELECT.
Save that query as qryAutorsForItem.

For the function:
Create a new module and paste the function-code there.
 
Still trying
I typed in in the sql window the following which is amended to the
actual table and field names as I think you meant them to be???? But
when I run the query I get prompted to Enter a parameter value for
ParItemID and then a second prompt to Enter parameter value for Author
Title Link.ItemLnkID and the result is nothing on the List that
appears.

PARAMETERS parItemID Long;
SELECT [Author List].Author
FROM [Author List] INNER JOIN [Author Title Link] ON [Author
List].[Author ID] = [Author Title Link].AuthorLnkID
WHERE ((([Author Title Link].[itemLnkID])=[parItemID]));

Hello Wendy.

Wendy said:
Wolfgang said:
Wendy Simes wrote:
I have looked at various examples but don't seem to be able to
get a result.
I have a table called AuthorLnkID table with two fields AuthorLnkID
and ItemLnkID. I want to tell a query to concatenate AuthorLnkID
where they have the same ItemLnkID.
The object of the exercise is to have under the title of the book the
author's names appearing in a single row in the final report.
I guess that there also is a table "Author" with AuthorName (Text)
and AuthorID (AutoNumber) fields?
You could create a query (qryAutorsForItem) that supports a parameter
(parItemID) and that lists the names of the autors for that itemID:

PARAMETERS parItemID Long;
SELECT Author.AuthorName FROM Author INNER JOIN
AuthorLnkID ON Author.AuthorID = AuthorLnkID.AuthorLnkID
WHERE (((AuthorLnkID.itemLnkID)=[parItemID]));

Then create a function in a module that returns the list (using DAO):

Public Function AuthorsForItem(ItemID As Long) As String
Dim strNames As String
Dim rst As DAO.Recordset
With CurrentDb.QueryDefs("qryAutorsForItem")
.Parameters("parItemID") = ItemID
Set rst = .OpenRecordset(dbOpenForwardOnly, , dbReadOnly)
With rst
Do Until .EOF
strNames = strNames & ", " & !AuthorName
.MoveNext
Loop
.Close
End With
.Close
End With
Set rst = Nothing
If strNames <> "" Then
AuthorsForItem = Mid$(strNames, 3)
End If
End Function

You can use this function in a query or (I'd prefer) in the report.
Click to expand...
Click to expand...
Thanks for all your help but I think I am out of my depth here.
Yes there is a table for Authors called [Author List].
When I make the relevant changes to table names I get syntax
error messages starting with a error message for
PARAMETERS parItemID Long;
Click to expand...

Where did you enter this text?
Open a new query in design view, close the add table window,
Klick SQL View in the view menu, then enter the block with
PARAMETERS and SELECT.
Save that query as qryAutorsForItem.

For the function:
Create a new module and paste the function-code there.
Click to expand...
 
Hello Wendy.

Wendy said:
Still trying
I typed in in the sql window the following which is amended to the
actual table and field names as I think you meant them to be????
But when I run the query I get prompted to Enter a parameter value
for ParItemID and then a second prompt to Enter parameter value
for Author Title Link.ItemLnkID and the result is nothing on the List that
appears.

PARAMETERS parItemID Long;
SELECT [Author List].Author
FROM [Author List] INNER JOIN [Author Title Link] ON [Author
List].[Author ID] = [Author Title Link].AuthorLnkID
WHERE ((([Author Title Link].[itemLnkID])=[parItemID]));
Click to expand...

That's how my solution should work:
You create a query that uses the function I provided and passes an
ItemID to the function to retrieve the list of authors as one string.
The function uses that ItemID to fill out the parameter parItemID of the
above query to retrieve the list of author names to concatenate them.
When you double click the above query, you should be promted for
parItemID, that is by design. A second prompt should not appear,
so check the column name "itemLnkID" in the table "Author Title Link".

A query that uses the function could look like this (I guess you have
something similar to an Item table):
Select ItemID. ItemName, AuthorsForItem(ItemID) From Item
 
Back
Top