Concatenate a list of names from same field

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.
 
W

Wolfgang Kais

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.
 
W

Wendy Simes

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...
 
W

Wendy Simes

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.
 
W

Wolfgang Kais

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.
 
W

Wendy Simes

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...
 
W

Wolfgang Kais

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Format part of a concatenated control 1
How do I concatenate records? 20
Trouble concatenating using tblName.FieldName 8
Descriptive headings in report header derived from report's name 7
Name Concatenate 2
2 Concatenating Fields Showing Different Font 2
concatenating text boxes that contain expressions 4
Concatenation in a Totals query 4

Top