Using a query to move data to new columns

[emunson]

Hi,

I have an access database with multiple tables that I need to export into a single spreadsheet. The tables are set up with one-to-many relationships between them; for example, in my Collection table, I have a song ID and the title of the song (each song only appears once in this table). In my Composers table, I have the song ID along with a composer's name. In the Composers table, songs can have multiple composers, in which case a given song will appear in multiple records, each time with a different composer's name.

So when I run a query that appends the names from the Composers table to the songs from the Collection table, I end up with each song having an extra record for each additional composer. Incidentally, I don't know whether there is any limit to the number of composers a song may have.

Is there a way to write a query that can put each individual composer in a separate field, but keep them all in the same record? I need all of the information for each song to appear on a single line.

So, this is what I'm getting now:

CollectionID | Title | Composer
20 | No one will ever know my heart | Foree, Mel
20 | No one will ever know my heart | Rose, Fred
21 | Pal of my cradle days | Piantadosi, Al
22 | Perfect song | Breil, Joseph C.
23 | Say mister have you met Rosie's sister | Harrison, Charles, 1883-1955
23 | Say mister have you met Rosie's sister | Rose, Fred
24 | Three little words | Ruby, Harry

And this is what I need:

CollectionID | Title | Composer1 | Composer2
20 | No one will ever know my heart | Foree, Mel | Rose, Fred
21 | Pal of my cradle days | Piantadosi, Al


22 | Perfect song | Breil, Joseph C.
23 | Say mister have you met Rosie's sister | Harrison, Charles, 1883-1955 | Rose, Fred

If this isn't possible with a query, could you recommend another approach?

Thanks,
Liz

[NoellaG]

Hi,

using VBA you can concatenate all composers for 1 songs in a string, and then fill a temp table with the values you want.

gr
NG

[maximus]

I have two tables
tblSong: ID(AutoNumber PK), SongName (Text)
tblComposer: Link_ID(Number), NameofComposer (Text)

The table tblComposer is linked to tblSong one-many. So one song can have more than 1 composer.

I have attached a sample DB check query5 which shows the name of the composers in a Single row separated by ","

I have used the following function:

Option Compare Database
Dim intMyID As Integer
Dim strSQL As String
Dim strComposerString As String
Function StrComposer(intMyID) As String
strComposerString = ""
strSQL = "Select * From tblComposer Where [Link_ID] =" & intMyID
Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.EOF And rst.BOF Then
Exit Function
End If
Do While Not rst.EOF
If Len(strComposerString) > 0 Then
strComposerString = strComposerString & ", " & rst!NameofComposer
Else
strComposerString = rst!NameofComposer
End If
rst.MoveNext
Loop
Set rst = Nothing
StrComposer = strComposerString
End Function


This function basically does what NoellaG has so wonderfully explained.


Refer to the attached db.
Check Module2 for the function
Query5 to Check the Result