Hi, I'm re-vamping a royalty payment database that tracks royalty data for various songs and the corresponding entities (vendors) who need to be paid. There are three main tables (songs, royalty data, vendors).
"Songs" table fields: ISRC, Track Title, Track Artist
"Royalty Data" table fields: ISRC, Income Source, Country, Quantity, Net Royalty
"Vendor" table fields: ISRC, Payee Name, Percentage
All of the relationships are linked via ISRC as the primary key.
I'm now trying to set things up so I can perform a query by Vendor:Payee Name on the Royalty Data table, but this presents a problem any time there's more than one vendor linked to the same ISRC (this is necessary when multiple vendors are splitting the royalties on a single song). For example, let's say it's vendors John Lennon and Paul McCartney who have respective percentages of 60% and 40% on a song. If John's record appears before Paul's record in the Vendor table, then any time I query for "Paul McCartney" on the Royalty Data table, it reverts back to displaying John's percentage.
I've been able to make things work with a portal solution, but unfortunately there are thousands of line items usually, which can't be dealt with easily in a portal.
Any ideas on how to make this work without a portal?