Need help with a calculation please

[Paula OConnor]

: Let's see if I can explain what I need...

I work at a music publishing company & we receive royalty payments which are shared between us, the writers, other publishers, etc. Many times the payments are made per CD, but are then split many different ways, therefore it is never a set amount of entries for that one CD. My question is this: Is there a calculation that would figure out the total amount paid PER CD, without adding up each payment per person?

Here's maybe a less confusing example:

CD #1004 (may have 10 entries for a total of $50 for that one CD)

CD #1005 (may have 20 entries for a total of $100)

Is there a formula that would add up just $50 plus $100 & not add $50 or $100 per entry?

Thanks in advance. It's probably simple - I just can't seem to figure it out!

[Korky2]

I think you need to study Summary Fields and their uses.Look in 'Help' and search under 'summary'.

You need to define a summary field which gives the subtotal of the field containing the split amount for each group of records when sorted by CD Number.

[Korky2]

I've attached a file to demonstrate the above.Select 'Scripts,Sort by CD#' to see the results. In layout mode, see how the summary field is placed in a summary part.

Hope this helps.

Royalty.zip

[Paula OConnor]

Thank you so much for your trouble, but I think maybe I'm not explaining it correctly. I've attached a shortened version of my table to show exactly what I need.

Take CD# 142 for example. The total foreign amount we were paid for that CD was 97.07. That amount was shared by 2 artists. Therefore, the foreign amount (97.07) is listed 2 times. What I need the foreign total column to do is take the repeating CD#'s & just give me the amount paid for that particular CD, not add the 97.07 plus 97.07, etc. In other words, I need a calculation that will take the foreign amount paid for each CD & divide it by the number of repeating CD#s.

And now I probably made it even more confusing! :

CopyAce.zip

[bruceR]

This is a moderately complex relational design problem. You need a CD table, a tracks table, an Artists table, and a Track_Artist_Role table.

[David Jondreau]

To answer your direct question, you need to add one more table. A CD table. That CD table will have a couple fields, like CD No (as your current table which is a "CD Royalties" table) and a calculation field "CD total".

If you relate the CD table to the CD Royalties table (via the CD No) to add up the related CD Royalties.

[Paula OConnor]

Awesome ~ Thanks so much for the help!

[bruceR]

This is a moderately complex relational design problem. You need a CD table, a tracks table, an Artists table, and a Track_Artist_Role table. For instance, contrary to advice given above, Foreign Amt and Total Tracks are properties of the CD, not of the track.

SEE ATTACHED.

CopyAce.zip

Edited January 4, 2008 by Guest

[Paula OConnor]

This is great! Thanks so much for going through all that trouble :

[bruceR]

You're welcome. It's a starting point and you'll have to add the interface features to make it work of course.

[Paula OConnor]

Yes, but you made it very easy to understand, so I'll have no problems with the rest of it. Thanks again!