Combine select records from multiple tables into one list?

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…

Thanks for the input! I have three similar tables because that’s just what I started with. I thought one table for tracks by Artist 1, another for Artist 2, etc. because over time I’d end up with a massive list. I know I can filter that list for specific features, like if I have artist and album names for each track, but I was concerned it would get unwieldy.

Initially I just wanted a list of artists and the date I last looked for their releases. Then it grew into a larger idea and I wanted a way to list future purchases. So I started reading about linking so I could have one table with artist info and additional tables with track information. This hasn’t panned out as expected.

Here’s a quick implementation of the base structure I mentioned. It may be completely inappropriate for your use, but it follows the Artist>Album>Track hierarchy I described. Note the <Buy These Tracks> view in the [Track] table. :winking_face:

(That’s a read-only share with copying allowed; duplicate it to your workspace to be able to examine and modify the fields.)

(Music from the always-wonderful Philip B. Price can be found here. :winking_face: )

Wow, you’ve gone above and beyond my friend! Thank you so much for the demo. I’ll definitely be exploring this more and thinking over the best way to proceed.