Martin Courtney
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many thanks for the responses. I have gone with Jim's solution, as I find that easier to work with.
ASKER
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.
No, he was going the other way.
Jim.
Jim.
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,
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.