How to calculate "total payment" over multiple records?

[Jenna]

I'm making a database for a literary magazine that tracks our contributing authors, the titles of their work and the issue each title appeared in, how much we paid each author, etc.

We pay authors by the page: $25 for the first page and $15 for every page after that. If every author had only one article per issue, "total payment for this issue" would be an easy calculation. However, many authors have several pieces in one issue. I'm giving each title its own unique ID, and currently I have a basic calculation that figures the subtotal paid for *that piece.*

How can I write a calculation that will search the database for other instances of the same author's work in a particular issue and then total the individual subtotals so that it returns a figure equal to "total payment for this issue"?

I'm sorry if this is confusing...I'm new and confused myself.

Thank you, fellow filemakers! I'm so grateful for you all! I hope I learn enough to be able to help someone soon.

[mr_vodka]

Is it $25 for the first page and $15 for every page after that for each piece or for an issue? IOW, is your calc already considering this pricing?

If so and you just want to add those calc up, you can create a summary field that totals that calc up.

Edited July 16, 2009 by Guest
added

[efen]

I don't know whether mr_vodka's reply has answered your question; I also don't know how you have set up your database and what your calculation for determining the amount to pay an author is.

See attached for my way of calculating the amount to pay without "searching". As mr_vodka, I couldn't decide whether you wanted to show the total when only the first page of the first article by an author was paid at $25 or $25 was paid for the first page of each article by the same author - so I have included both ways. How appropriate for your needs this is, I don't know.

Magazines.zip

[Jenna]

Yes, all articles are $25 for the first page and $15 for subsequent pages so your first portal, efen, is the applicable one. I'm checking it out now...

Thanks so much for taking the time!