Multi table Sum Query 1

[Torren]

Ok the three tables involved are
TblBikes containing information on the quadBikes in stock

TblHires containing hire information with the forign key BikeID

TblRepairs containing bike repair information with the forign key BikeID

What i want to do is group all by BikeID and calculate the

BreakEvenPrice = [tblBikes.CostOfBike]+SUM([tblBikes.CostOfBike])-SUM([tblHires.HireCost])


The problem Is as There is a one to many relationship between tblBikes and the other tables its confused me. So help would be greatly appreciated.

Thanks, Sci

 

[HandsOnAccess]

Why tblBikes is used twice in the expression?
Also tblRepairs is not used at all!
Anyway according to your expression, you can use DSUM Function

BreakEvenPrice = [tblBikes.CostOfBike]+DSUM("CostOfBike","tblBikes")-DSUM("HireCost","tblHires")

Actually this gives total sum.
In case you want Price for a particular BikeID,
You can use BikeID value in DSUM Function.

Hope this helps you...
Regards,

 

[Torren]

Yer it was meant to be CostOfRepairs not CostOfBike Again. My fault been workin on theis database 20hours straight im knackerd. but i really want to get this sorted. But basically what needs happening is all the Hire income of the a bike Must be grouped and subtracted from the cost price and the all the Repairs done on that bike must be Added to that to create a table displaying all the bikes in stock and the pric ethat that bike must be sold at to break even. Using DSum does not group the Repairs or Hires by the forign key in that table. But thanks for trying to figure out what im trying to say

 

[HandsOnAccess]

Here is the query, I have tested with dummy data, The results matches with your requirement

------------

SELECT tblBikes.BikeID, tblBikes.BikeNumber, tblBikes.CostofBike, Sum(tblHires.HireCost) AS HireCost, Sum(tblRepairs.CostofRepair) AS CostofRepair, [CostOfBike]+[CostOfRepair]-[HireCost] AS BreakEvenPrice
FROM (tblBikes INNER JOIN tblHires ON tblBikes.BikeID = tblHires.BikeID) INNER JOIN tblRepairs ON tblBikes.BikeID = tblRepairs.BikeID
GROUP BY tblBikes.BikeID, tblBikes.BikeNumber, tblBikes.CostofBike;

------------