Creating Access query for music playlist conversion to CSV

I am struggling with a basic design concept.  
I have been given a set of data which has a music artis and one or more songs for each artist.  I need to create a CSV that I can import into a website which will make the playlist.

Artist 1.    Song 1.   Song 2.     Song 3.  
Artist 2.    Song 1.   Song 2.
Artist 3.    Song 1    Song 2.      Song 3.     Song 4.  

In order to get this to work I figure that I need to create a query which will return the artist name in column A with the respective song in column B, thus making each row a single artist and song

Artist 1  Song 1
Artist 1  Song 2
Artist 1  Song 3
Artist 2  Song 1
Artist 2  Song 2
Artist 1  Song 3

I am familiar with MS Access basics (though obviously I have some knowledge gaps) and MS Excel.

Access 2016 (on office 365).
Windows 10

The website (Playlist Converter) will require this format.  The list of songs is quite large hence I am looking for a DB solution to the problem.

Many thanks for any assistance in advance

Martin CourtneyTeaching AssistantAsked:
Who is Participating?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
In order to get this to work I figure that I need to create a query which will return the artist name in column A with the respective song in column B, thus making each row a single artist and song

 Yes.   This is the start of a good design, but what you really want is:



which joins the first two in a many to many relation; an artist can have many songs, and a song can have many artists.   But you could start with what you outlined.

  As far as the output, you have two choices:

1. Use of VBA code to write the CSV file, looping on your table of records.


2. Using the Code here:

 To generate the second column.   You would drop this code in, then in a query call the function to return all the songs.

You will need a separate query for each column of the input file that contains songs. Your example shows 4 columns but it could be 10 or 30.  The concept is the same.  You can put all this together into a union query and then export the union query.  Union queries will balk at too many inner queries.  If you run into that, you can break the union into two unions - songs 1-15, songs 16-30 and then union the two unions.

Select Artist, song1 From yourtable
Union Artist, song2 From yourtable
Union Artist, song3 From yourtable

Union queries contain multiple sub queries.  each sub query MUST contain the same number of columns in the same order and the data types must match.  In your case, the fields will be text so there won't be a problem but if you ever need to make a union that includes dates or numeric values,  all the queries must select the "date" field as the "third" field.  Think about one list being stacked on top of another.  The lists don't make sense if the columns get scrambled.
Martin CourtneyTeaching AssistantAuthor Commented:
Many thanks for the responses. I have gone with Jim's solution, as I find that easier to work with.
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Martin CourtneyTeaching AssistantAuthor Commented:
Fortunately I have played around with VBA before so Jim's solution did make sense and the guide was very useful too.
I guess I'm confused.  Jim's solution takes a normalized schema and concatenates multiple rows into one.  I thought you wanted to go from an unnormalized recordset to a normalized one with one row per song.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
No, he was going the other way.

apparently.  But I just read the post again and I still read it as going from many to one but since Martin says your solution worked he must be going from one to many,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.