Sum of selected rows of a columns data

[Aenon]

I have an issue i cant seem to resolve where I am trying to sum some datacells in a column based on a foreign key.

I have 3 tables. One table is for my assets, one table is for different projects my assets can be assigned to, and a third table linking each project and asset and lists material transferred by that asset on that project, also a many to many relationship.

tblAsset.ID is PK in tblAsset
tblAsset.Serial contains the serial number of the assets
tblProject.ID is PK in tblProject
tblTransfer.Project is FK linked to tblProject.ID
tblTransfer.Asset is FK linked to tblAsset.ID
tblTransfer.Transfers lists how much material istransferred by my asset on that project.

What i want to do is create a query that show the Serial of each asset, and sums the transported material of all the projects it has been to.

===========================
Example:
Asset1 (ID=1) has been to project P1(ID=1) and transferred 10 tons, P2 (ID=2)transferring 14 tons, and P3(ID=3) transferring 5 tons.
Asset2(ID=2) has been to project P1(ID=1) and transferred 16 tons and P3(ID=3) transferring 3 tons.

My tblTransfer looks like this:
ID | Asset | Project | Transfer
1 | 1 | 1 | 10
2 | 1 | 2 | 14
3 | 1 | 3 | 5
4 | 2 | 1 | 16
5 | 2 | 3 | 3

And I want my query to show this:
Asset Serial | Total Transfers
Asset 1 | 29
Asset 2 | 19
==============================

Hope this is possible. Appreciate any help or inputs!

 

[MStef]

Look at "DemoTransferSumA2000.mdb" (attachment, zip).
Look at tblTransfer, Query1. Run Query1.

 

[Aenon]

Hah! Cant believe it was that simple! Thanks mate!

 

[Aenon]

I have a followup question. The asset table was made into a form for user interaction. Basically this form contains all the information from the tblAssets. But i am trying to add a new column with the information from this query so that every asset also show total amount of material transferred. But no matter how i try to get that data over into my other form it doesnt show it correctly. Anyone know how to do this?

 

[Aenon]

Figured out that it was rediculously simple to do that after all. I just had to include all the fields in my query and base my form on the query instead of the table... I have another issue with this however, and i hope someone can help me with it. I used the structure provided by MStef in his file for my query, but all assets that had not been on a project yet was not included when i ran the query. Is it possible to change query somehow to make it include all the assets even if the total sum of material transported is 0?

 

[MStef]

Hello Aenon!
Look at Demo (a new one), (attachment, zip).
I usually use this method.
Open Form1 and see.

 

[Aenon]

You are awesome MStef! I took the function you used in the totals celland i figured i could use it for in a query that had the exact structure i wanted. However, i cant make it work exactly the way i want. Right now i am just trying to list the total cuttings for each hose in a normal spreadsheet using a function. For the moment i am trying to write (this if from my actual database):

Total_Transfers: DSum("Transferred_Cuttings";"tblTransfers";"Hose =" & Tables!tblAssets!Hose_ID)

Where you can se that my assets are hoses, and the material transported is "cuttings". However, it keeps getting changed to

Total_Transfers: DSum("Transferred_Cuttings";"tblTransfers";"Hose =" & [Tables]![tblAssets]![Hose_ID])

Now i dont really know exactly what all of this means. Correct me if I am wrong, but from my deductions i believe that the exclamation point is used for an absolute path or something, where dot is more if access already know where to find the path? And the "&" sign is used to infer that this is an adress??

When i run the query, and then I am prompted to give the Hose_ID before the query runs. Consequently, every row shows the total transfers for the asset whose ID i typed in.

Further on, when i finally get this function to work, i was hoping it would be possible to include it in an IIF statement. Something like this:


Total_Transfers: IIF( IsNull( DSum("Transferred_Cuttings";"tblTransfers";"Hose =" & Tables!tblAssets!Hose_ID) ); 0 ; DSum("Transferred_Cuttings";"tblTransfers";"Hose =" & Tables!tblAssets!Hose_ID) )

My final goal in this is to make sure that even if a hose does not yet have any transfer date logged on it, that it will show up in my query, whith total transfer as "0", instead of not showing up at all.

I realize that there might be some fractures in my way of thinking, as a hose that does not already show up in my tblTransfers will not be included in my query calling this table. However, I was hoping there was some workaround where i could force the query to include all the hoses from my tblHoses, and list the total transfers of the hoses that actually had been used.

Man that was a long post.... Anyway, thanks in advance for any assistance!

 

[MStef]

Make it as I make in my Demo. It must work.

 

[Aenon]

I cant really make it just like you do in the demo, as it wont include all the hoses that i have which i am missing data on, but are still laying around at different locations as spares. And I dont really understand that whole event thing that you made in the "asset" box.

The reason i want it in my query like this is that the form will then display all the totals for all hoses at the same time too, and it can also be displayed in a report very neatly. This will make it easy for any users of the database to spot all the different hoses that they can use without having to select an asset with the mouse cursor.

Is it impossible to make it work the way i envision?

 

[MStef]

It is my mistake in the Sub.
Private Sub Asset1..........
Rename in
Private Sub Asset..........

 

[Aenon]

Um, that did not really that much sense to me :S Are you trying to point out some mistake in your demo? If that is the case i have no idea where that sub-stuff is.

Could you just nudge me in the right direction considering my query? So that it will do the job i want it to do?

Thank you!

 

[MStef]

I usually do it on this way. I have no time to do it on another way.