Easiest solution is to use a linked-record field rather than a single-select field for your ‘Want to buy’ classification. That is, define a second table called, oh, [Status]. [Status] might have only a single record, “Want to buy” — or it might have other records if you have other statuses: “Never listen to this again”, “OK for parties”, whatever. When you want to buy a track, link that track to the “Want to buy” record in the [Status]. table. Once that’s done,.you can go to [Status] and display all records linked to the “Want to buy” record, regardless of which table they come from.
But…
You don’t explain why you have three tables containing songs by artists you like. To me, saying you have three functionally similar tables suggests you have two tables too many. Now, I can understand a number of scenarios where this would be a valid arrangement, so it could be a valid structure. But if you’re creating a new table for each artist or album, I’d recommend reformatting the base.
For instance, you could create tables for [Artist], [Album], and [Track]. Each [Artist] table might link to one or more [Album] records; each [Album] record would link to one or more [Tracks]. In that case, using a “Want to Buy” drop down makes sense – and makes creating a list of tracks to go out and buy far simpler than in my original approach. However, since I don’t know the structure of the rest of your base, I can’t tell you which design would be best…
