Song royalty calculation database -- should be simple, but...

[swyant]

... but I'm not very good at this. My friend receives an Excel spreadsheet with a bunch of info on it, but the columns he cares about are:

1) the name of a band (it can be anything, and there isn't a fixed table or list of these)

2) a royalty payment amount for a song or video by that band, from a YouTube or restaurant play. These come in as separate amounts per play, from a few pennies to a few hundred dollars. Again, there may be one payment per artist, or dozens of payments.

He needs a total of all the payments for each artist, so he can send them a check, minus his commission for doing the work. He has asked me for help, and I don't know the answer. I've been through The Missing Manual, and I can't seem to find anything that applies to his problem, nor have I found anything on line. ANY pointers would be appreciated. Thank you.

[bcooney]

If you are looking for a template, you're probably out of luck. I think that you will need to build your own solution.

You'll need, at minimum the following tables:

Bands (key field BandID)

Plays (each record contains a BandID)

Payments (each record contains a BandID, Date)

Payment Line Items (PaymentID, PlayID)

I'd suggest that you import the excel spreadsheet into a PlaysImport table. Then you need an interface that will allow you to assign the proper Band ID and copy the Play data into the real Plays table.

After that, you'd create a new Payment record, add Payment Line Items from unpaid Plays. The total of the Playment Line Items, minus commission, is the check amount.