SUM command in Query not working

marv101 · Mar 9, 2006

Hey, I'm a noob on the forum so please bare with me.
For my ICT project, I have to create a new system for a company. I am doing a Video Rental Database. My user wants to be able to work out the Percentage Recovery on a film, so that he knows how much he has made. The equation for this would be:

(sum of costs to rent the film and fines)/cost to purchase film from supplier x 100

The cost to rent a film a night is usually £2.50, so the equation would work out how much we have made from renting the film out (as in all the £2.50s we got), plus any fines charged. Divide this by how much it cost the boss to buy the film from the supplier (usually around £31 or so), multiplied by 100 to give it as a percentage.

Rental costs (as in the £2.50s) and fines are in tblRental under "Cost" and "Fines". The cost to purchase the film is under "Cost" in tblVideo.

I want it so that when I type in the Video ID (primary key of tblVideo, shared primary key (foreign key to tblVideo) in tblRental) using a parameter criteria (eg. [Please type in Video ID]) that the Video ID and Title (from tblVideo) appear next to this calculated field.

My first attempt used the following expression:

Percentage Recovery: (tblRental!Cost+tblRental!Fines)/tblVideo!Cost*100

This worked fine, but only when the film had been rented out once, meaning there was only one entry of this film in tblRental. When it had been rented out multiple times, I got a table showing up with the film being displayed as many times as it had been rented, but with no sort of percentage recovery next to it. Obviously this is useless.

I then decided that I would need to tell Access to sum up all of the rental costs and all of the fines under the specific criteria the user types. So my next attempt used the following expression:

Percentage Recovery: (Sum(tblRental!Cost)+Sum(tblRental!Fines))/tblVideo!Cost*100

When trying to run this query, it tells the query does not include the specified expression "Video ID" as part of an aggregate function. Why though? I want it to add the costs where the criteria is the Video ID the user types, so Video ID shouldn't have to be built into the expression should it?

I don't know where I'm going wrong, but I need help and fast!

lespaul · Mar 9, 2006

SELECT VideoID, ((SUM(tblRental.Cost + tblRental.fines))/tblVideo.Cost)*100 As Percentage_Recovery
FROM tblRental
INNER JOIN tblVideo on tblRental.VideoID = tblVideo.VideoID
GROUP BY VideoID

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

PHV · Mar 9, 2006

What about this query (SQL code) ?
SELECT V.[Video ID], V.Title, 100*Sum(R.Cost+Nz(R.Fines,0))/V.Cost AS PercentageRecovery
FROM tblVideo AS V INNER JOIN tblRental AS R ON V.[Video ID]=R.[Video ID]
GROUP BY V.[Video ID], V.Title

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

Golom · Mar 9, 2006

You probably have something like

Code:

Select tblVideo.VideoID, VideoTitle, 
       (Sum(tblRental!Cost)+Sum(tblRental!Fines))/tblVideo!Cost*100 As [Percentage Recovery]

From tblRental INNER JOIN tblVideo
     ON tblRental.VideoID = tblVideo.VideoID

Where tblVideo.VideoID = & "'" & [Enter Video ID] & "'"

When you are using an aggregate function (and SUM is such a function) then you need to tell SQL over what group of records to compute the sum. To do that you use a GROUP BY clause and specify those fields whose values are to be constant within the group. In your case you need

Code:

Select tblVideo.VideoID, VideoTitle, 
       (Sum(tblRental!Cost)+Sum(tblRental!Fines))/tblVideo!Cost*100 As [Percentage Recovery]

From tblRental INNER JOIN tblVideo
     ON tblRental.VideoID = tblVideo.VideoID

Where tblVideo.VideoID = & "'" & [Enter Video ID] & "'"
[COLOR=blue]
GROUP BY tblVideo.VideoID, VideoTitle[/color]

because those two fields will be constant for the group of records that you are SUMming.

marv101 · Mar 9, 2006

Thanks for the very quick replies. Unfortunately I haven't got any working yet. Here is what I had originally (attempt 2) as SQL view:

Code:

SELECT tblVideo.[Video ID], tblVideo.Title, (Sum(tblRental!Cost)+Sum(tblRental!Fines))/tblVideo!Cost*100 AS [Percentage Recovery]
FROM tblVideo INNER JOIN tblRental ON tblVideo.[Video ID] = tblRental.[Video ID]
WHERE (((tblVideo.[Video ID])=[Please select Video ID]));

Can someone tell me what to edit from this?

PHV · Mar 9, 2006

SELECT tblVideo.[Video ID], tblVideo.Title, (Sum(tblRental!Cost)+Sum(tblRental!Fines))/tblVideo!Cost*100 AS [Percentage Recovery]
FROM tblVideo INNER JOIN tblRental ON tblVideo.[Video ID] = tblRental.[Video ID]
WHERE (((tblVideo.[Video ID])=[Please select Video ID]))
[!]GROUP BY tblVideo.[Video ID], tblVideo.Title[/!];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

Golom · Mar 9, 2006

Code:

SELECT tblVideo.[Video ID], tblVideo.Title, 
(Sum(tblRental!Cost)+Sum(tblRental!Fines))/tblVideo!Cost*100 AS [Percentage Recovery]

FROM tblVideo INNER JOIN tblRental 
     ON tblVideo.[Video ID] = tblRental.[Video ID]

WHERE (((tblVideo.[Video ID])=[Please select Video ID]))

GROUP BY tblVideo.[Video ID], tblVideo.Title

marv101 · Mar 9, 2006

None of them work. I just get told that the specified expression (sum(tblRental!Cost...........you know the rest) is not part of an aggregate function

PHV · Mar 9, 2006

Oops:
SELECT tblVideo.[Video ID], tblVideo.Title, (Sum(tblRental[!].[/!]Cost)+Sum(tblRental[!].[/!]Fines))/tblVideo[!].[/!]Cost*100 AS [Percentage Recovery]
FROM tblVideo INNER JOIN tblRental ON tblVideo.[Video ID] = tblRental.[Video ID]
WHERE (((tblVideo.[Video ID])=[Please select Video ID]))
[!]GROUP BY tblVideo.[Video ID], tblVideo.Title, tblVideo.Cost[/!];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

marv101 · Mar 9, 2006

Getting closer. No error messages (hurrah!), but Percentage Recovery is blank (boo

)

PHV · Mar 9, 2006

Perhaps this ?
SELECT V.[Video ID], V.Title, 100*Sum(Nz(R.Cost,0)+Nz(R.Fines,0))/V.Cost AS [Percentage Recovery]
FROM tblVideo AS V INNER JOIN tblRental AS R ON V.[Video ID] = R.[Video ID]
WHERE V.[Video ID]=[Please select Video ID]
GROUP BY V.[Video ID], V.Title, V.Cost

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

marv101 · Mar 9, 2006

I think you've hit the nail on the head! I've just tried it out for one of them, and I'm given a sensible answer. I'm now going to check for the other films, and see if the value given is correct.
Thank you so much for spending time on this. You're a god send and have probably saved my project!

marv101 · Mar 9, 2006

PHV, you are a GENIUS!
It works perfectly. Thank you so much.

Just to be cheeky now, is there any way I can get it to round up? If you can't be bothered to do it, don't worry. It's not essential, just would be easier on the eyes than a string of numbers.

Anyways, thank you!

PHV · Mar 9, 2006

SELECT V.[Video ID], V.Title, Round(100*Sum(Nz(R.Cost,0)+Nz(R.Fines,0))/V.Cost, 2) AS [Percentage Recovery]
FROM tblVideo AS V INNER JOIN tblRental AS R ON V.[Video ID] = R.[Video ID]
GROUP BY V.[Video ID], V.Title, V.Cost

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

marv101 · Mar 9, 2006

Dude, I absolutely love you. Works perfectly. I've slightly modified it so that one query requires the user to type the Video ID they want, and another so that it shows all of them.

Thank you again

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

SUM command in Query not working

marv101

Programmer

lespaul

Programmer

PHV

MIS

Golom

Programmer

marv101

Programmer

PHV

MIS

Golom

Programmer

marv101

Programmer

PHV

MIS

marv101

Programmer

PHV

MIS

marv101

Programmer

marv101

Programmer

PHV

MIS

marv101

Programmer

Similar threads

Part and Inventory Search

Sponsor