SQL query for Royalties

[Lana21]

Hi guys,

I have constructed a database which is basically sales related. It is basically used for keeping tracks of Books, where the artist gets royalties.

I want to, in the most logical way (perhaps a monthly report) be able to keep track of how much the Author of the book is owed. All of the sales are recorded into tables, and the query should be able to add up the total amount of books sold for each Author and then, for example, take 25% of that and add it to some sort of report (or possibly a table).

Example:
Table
R. Dahl - £25
D. Brown - £20
D. Brown - £20
R. Dahl - £25

The query will work out D. Brown has sold £40 worth of books that month, and then would be owed 25% of that. It will be a set amount of royalties for each Author by the way.

I appreciate any input, as not only am I stuck about the best way to record this, but also putting it into place.

Thanks alot,

Lana.

 

[reclusivemonkey]

You can group by Month simply by using a function on the date (Month([DateField]). So, if you make a query that groups by this function on your date, and add the royalty formula and change the value to "Sum" you should have what you want.

 

[Lana21]

Thanks for the quick response.

So the query would record a sum of each Author for each month?

So it would be something like:

R. Dahl - Jan - £40
D. Brown - Jan - £45
R. Dahl - Feb - £25
D. Brown - Feb - £45

etc. etc.

Would this have to be written in SQL?

Thanks again,

Lana.

 

[reclusivemonkey]

Yes thats right.

I doesn't have to be written in SQL. You can make a query the way you normally do. Drag and drop your Authour field. Enter the royalty field (something like

Royalty: [Sales]*0.25

Then

Month: Month([Date])

Then just click on the "Totals" button at the top of the query window and change the Total field on "Royalty" to Sum (leave the other two on Group by). Its that easy.

 

[Lana21]

Excellent, that seems just what I want! One slight problem at the moment is this:

The royalty section is adding up EVERY order, and not just the ones of the related Author. Code:

SELECT tblAuthors.lngAuthorID, tblAuthors.strAuthorName, Sum(([curUnitPrice]*0.25)) AS Royalty, Month([dtmOrderDate]) AS [Month]
FROM tblAuthors, tblOrders INNER JOIN tblOrderDetails ON tblOrders.lngOrderID = tblOrderDetails.lngOrderID
GROUP BY tblAuthors.lngAuthorID, tblAuthors.strAuthorName, Month([dtmOrderDate]);

I guess I'm obviously doing something wrong!
Lana.

 

[reclusivemonkey]

My SQL is non-existent, I use the Access Query Builder, its much easier. Try using the query builder like I suggested. You can still look at the SQL code once you've done to see what the correct syntax is.

 

[Lana21]

The code was actually from what I built, I'm not that good with SQL...haha.

The problem is the sum is relating to the month, and not the Author. So all Authors will appear under the same month with the combined amount they are all owed.

Thanks.

 

[reclusivemonkey]

If you create the query as per my instructions you should have the Royalties grouped by Author and Month, just as you requested. Thats if your tables are set up correctly. Can you zip up your DB and attach it to the thread?

 

[Lana21]

Aha! It is now working. I had to add the actual book to the query with it!

This now means an artist can appear more than once in a month, but it's much more flexible as it can give reports into such details as royalties per books (which can easily be combined for report form!)

I'm sure something else will go wrong somewhere, but I'll cross that bridge when I come to it.

You've been a great help!!

Thanks so much,

Lana.

 

[reclusivemonkey]

Aha! It is now working. I had to add the actual book to the query with it!

This now means an artist can appear more than once in a month, but it's much more flexible as it can give reports into such details as royalties per books (which can easily be combined for report form!)

True, I am glad you have come to an acceptable solution. However, unless you are using a different ID for each Author writing a book, you should still be able to get a Sum for each Author for each month...

You've been a great help!!

Thanks so much,

No problem