Creating Access query for music playlist conversion to CSV

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

Example
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
Martin CourtneyTeaching Assistant (Autism)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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:

tblArtist
tblSong

tblArtistSongs

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.

or

2. Using the Code here:

https://www.experts-exchange.com/articles/2380/Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

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

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
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.

UnionQuery:
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.
0
Martin CourtneyTeaching Assistant (Autism)Author Commented:
Many thanks for the responses. I have gone with Jim's solution, as I find that easier to work with.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Martin CourtneyTeaching Assistant (Autism)Author Commented:
Fortunately I have played around with VBA before so Jim's solution did make sense and the guide was very useful too.
0
PatHartmanCommented:
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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
No, he was going the other way.

Jim.
0
PatHartmanCommented:
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,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.