Converting ArtistNames to a sortable format

[Chris_A1]

Hi

I have a music database in which I want to convert [ArtistNames] into a format which will sort correctly.

For example I need to convert:
1. Michael Jackson to Jackson Michael
2. Matt Schwartz presents Sholan to Schwartz Matt Scholan
3. Porn Kings Vs Flip & Fill feat. 740 Boyz to Porn Kings Flip & Fill Seven Hundred and Forty Boyz

I know how to do 1. and I know how to remove the words 'presents' and 'feat.' individually but how can I put all the expressions together? And I've got no idea of where to start with 3.!

Thanks for any help anyone can give me.

Chris.

 

[Fornatian]

This link leads to a post with a conversion function to change numbers to words.

As for the more general problem, is there any other field which defines whether it is a persons name or a string of words or numbers, if you you can establish the rules of engagement and identify the right action to take then you are getting closer to an acceptable solution.

I would first establish fields with numbers in, surely they can't be someone's name (unless of course you're talking about royalty). These would be one group to straight convert without reversing with relative confidence.

After that I don't know where to continue - anyone else?

 

[Chris_A1]

would you suggesting adding another field saying what type of artist it is.

e.g
SoloArtist (like Michael Jackson) or
Duet (like Paul Van Dyk featuring Saint Etienne)
Group (like The Chemical Brothers)

Do you think that would make it easier then to convert the name to a format which I could sort.

 

[neileg]

Access can do lots of straight logical tests, but fuzzy logic and artificial intelligence are not part of the core product!

You say you can deal with Michael Jackson to Jackson Michael but what are you going to do with Eddie van Halen? Is this to become van Halen Eddie, or Halen Eddie van or even Halen van Eddie?

How will you resolve material that was published under the name Little Stevie Wonder with later work published by Stevie Wonder? Same artist, different name, different sort order? Or Prince for that matter?

Even if you restructured to use the categories you suggest, are you gong to sort The Chemical Brothers under T or C? And what about The The!

There is no logical structure to the artists names. The human brain can have a go at resolving the data in the way you suggest, but I guess if I was to try it, I would get wrong results because I have no background knowledge of the artists.

The only way I see to resolve this is to key in another field that holds a different name for sorting. This is often used in directories so that you can sort Bob Wilson in the right way, even though his proper name is Robert Wilson.

 

[Chris_A1]

Thanks very much for your help. Maybe I am trying to do much.

I already have a field called ArtistSortName but I was hoping not to have to use it. The database contains 1425 different artists up to now!

Another problem I have is:

Track: Battleship Grey Artist: DJ Tiesto feat. Kirsty Hawkshaw

I need to have the track 'Battleship Grey' listed under DJ Tiesto AND Kirsty Hawkshaw.

Do you think this is possible?

 

[Fuga]

If I understand it correctly. 1 artist can have many albums, 1 album has many songs, 1 album can have many artists, 1 song can have many albums etc...

I would consider having three tables and two join tables.

1 artisttable
ArtistID
Artistname
info
info
.
etc

2. Albumtable
AlbumID
info
.
etc

3. Songtable
Songid
info
.
etc

1st jointable
artistID
albumID

2nd jointable
albumID
SongID

If you have compilations, you might want a third jointable or something

3rd jointable
ArtistID
songID

These are just my thoughts, I´m not sure this structure is optimal.

As for the sorting, theoretically you might be able to do some kind of loop using th Instr() function to find some mutual part of the names but I doubt that the results will be satisfactory and reliable. If there are "only" 1500 records, it might even be faster to change them manually.

Fuga.