Sum of values in linked tables

[Aenon]

Greetings!

I have uploaded the database I am speaking of. Access it through the link below. Please look at this file for details in the structure of my database to get the necessary info if you are able to help me.

I am having an issue with a query im trying to set up in a hose tracking database that i have created. The hoses are used to transfer cuttings from drilling operations. I have made a set of tables and linked them together in a way i thought was practical. Now i am trying to make a query that will show all the hoses, and sum up the total amount of cuttings each of the hoses have transported even if this number is zero. I have tried two different methods in order to achieve my goal without success. In my database i have tried using the "total" function but the database will not display any hoses which have not yet been used. This result can be found in qryTotalTransfer. Also, in Query1 i tried using an If function as well as DSum function in order to sum the total usage of my hoses. However, this made the query list each hose a number of times equal to the number of projects it has been used on, and the unused hoses were still not listed. Anyone out there with mad skills at this that can assist me?

I am a total newbie at access so please excuse me if my problems are stupid

use the below link to download the file as it is too big to be uploaded here.
http://dl.dropbox.com/u/55503312/Overboard Hose Tracking.accdb

Thanks in advance!

 

[jzwp22]

First, create a simple query that sums the transfer cuttings by hose ID

query name: qrySumOfTransByHose

SELECT tblTransfers.Hose, Sum(tblTransfers.Transferred_Cuttings) AS SumOfTransferred_Cuttings
FROM tblTransfers
GROUP BY tblTransfers.Hose;


Now create another new query that LEFT joins the hose table to the query above (select the fields you want from both sources). You will need to evaluate if there is a sum or not using the IsNull() function nested in an IIF() function. The query would look like this (I only included a couple of the fields from the hose table for illustration purposes)

Query name: qryFinal

SELECT tblHoses.Hose_ID, tblHoses.Serial_No, tblHoses.Certificate_No, IIF(isNull(qrySumOfTransByHose.SumOfTransferred_Cuttings),0,qrySumOfTransByHose.SumOfTransferred_Cuttings) As HoseSum
FROM tblHoses LEFT JOIN qrySumOfTransByHose ON tblHoses.Hose_ID = qrySumOfTransByHose.Hose;



By the way I did a compact and repair on the database to get the size down and then zipped it. The database is attached.

 

[Aenon]

Great! You sir, are king Thanks a lot! Just the solution i was looking for.

 

[jzwp22]

You're welcome; good luck with your project.