Songwriter Conundrum - How do I calculate this please?

[peter_lawton]

If any one can come up with a better subject title I will repost. Thanks

I have been collecting income from Harry Fox (a collection agency for money generated by record sales) for three songwriters (33.3% each). I divide the income into three and pay them. An easy enough calculation.

However one of the writers now gets paid directly so I only collect on behalf of two of them.

The table structure is
a) SongID
b) junction table SongID / WriterID / ShareID
c) WriterID
d) ShareID

A simple example might be

001 Elephant Song

101 Fred Smith Collected by Me
102 John Brown Collected by Me
103 Bert Jones Collected Directly

201 33.3%

One to many joins as appropriate.

Before I could just split the income according to the Writer’s Share but now, although the share of the Song remains thirds I have to distribute the income I collect 50/50 between two of the Writers.

The fields for ‘Writer Share of Song’ and ‘Who Collects’ already exist. I could create a new field for Writer Share of Income but was curious to see if there was a way of calculating ‘Total No of Writers’ divided by ‘Number of Writers for Whom I Collect’ using the structure I already have.

Even as an intellectual puzzle I'd be very interested in any possible solutions as it impacts on my database designs generally.

Thanks peter

[jzwp11]

You should be able to calculate what you need. You'll need a couple of counting queries combined into a third query.

First, a query to get the number of writers for each song.

query name: qryCountOfWriters
SELECT songID, count(writerID) as TotalNumberOfWriters
FROM yourjunctiontable
GROUP BY songID

Assuming that the Who Collects field is in the writers table
query name: qryListOfWritersForWhomICollect
SELECT writerID
FROM writerTable
WHERE WhoCollects="Collected by Me"

query name: qryCountOfWritersForWhomICollect
SELECT songID, Count(writerID) as TotalNumberForWhomICollect
FROM yourjunctiontable inner join qryListofWritersForWhomICollect on yourjunctiontable.writerID=qryListOfWriterForWhomI collect.writerID
GROUP BY songID

...but was curious to see if there was a way of calculating ‘Total No of Writers’ divided by ‘Number of Writers for Whom I Collect’ using the structure I already have

I'm not sure what the ratio: [Total No of Writers]/[Number of Writers for Whom I collect] will tell you. Let's say a song has 3 writers but you only collect for two of them; your expression would be 3/2 or 1.5. Wouldn't just the total # of writers (3) and the number for whom I collect (2) be sufficient to tell you that you have to split the funds between 2 people?

If that is the case, just bring the two counting queries together as follows:

SELECT qryCountOfWriters.songID, qryCountOfWriters.TotalNumberOfWriters, IIF (isnull(qryCountOfWritersForWhomICollect.TotalNumb erForWhomICollect),0,qryCountOfWritersForWhomIColl ect.TotalNumberForWhomICollect) as MyWritersForWhomICollect
FROM qryCountOfWriters left join qryCountOfWritersForWhomICollect on qryCountOfWriters.SongID=qryCountOfWritersForWhomI Collect.SongID

[peter_lawton]

Thanks very much - I'd forgotten about the count function. Haven't used it before. I'll give a try today.

[peter_lawton]

worked a treat, thank you very much

peter

[jzwp11]

Glad it worked out for you! Good luck with your database.