Calculating royalties per quarter

[Jon Crain]

Ok, that sounds simple enough, but implementation in the database that I'm working on has been anything but.

Background...creating a royalties database for a record label owned by a university which needs to track royalties for artists, publishers, and organizations (like the different University programs that get certain percentages of sales). It get's even more fun when you add in single song download tracking per project, but we're not there yet. Did I mention this is the first database I've ever built?

Currently Projects and Organizations has a subtable that ties the two together and also allows a user to add in a percentage that each organization get's for that project.

Sales are tracked per quarter (the quarter is choosen from a drop down with a custom value list of current quarters {on a side note, if there is a better way to do this, I'm all for it}) After expenses are payed for, we need the royalty calculation to kick in, but I'm having trouble tying the two quarters together (Sales and Organization_Royalties) to summarize all the sales from different vendors for that quarter, check it against net profit and calculate the royalty.

I'd be happy to send a file/picture whatever you need to have this make a little more sense as it's a little tough to convey what I'm after. My thought is that it is tied to the way I'm choosing my quarter, but there should be a calculation to match it up either way.

Thanks in advance for any help. (I attached part of the T.O. diagram if that helps)

jon