Using the sum of a row in a calculated field

[Gabba0708]

My goal is to use the sum of a column in a table and then use that sum in a calculated field and create a new column, so it would look something like [column_1]-sum[column_2], I tried doing this in a table and also in a query but have not had any success, any help or guidance would be much appreciated.

[CJ_London]

quite simply, that is not what tables are for which is simply storing the raw data. Your approach is an excel approach which does not work with databases. Look at using queries for your calculations.

If you want an example query - provide some sample data and table name(s) and the outcome required from that sample data. Don't use screenshots of the data as responders will want to copy/paste it, not manually type it all in.

[Gabba0708]

Thanks for the reply, but yes you are correct, the example that I was provided was one from an excel spreadsheet so I based my approach off of that. But the desired result is that as I add additional inputs to the Material Consumed column, that it would sum that column and then subtract it from Estimated Length, so [Estimated Length] -Sum[Material Consumed] and from that have a new column Estimated Remaining, I provided the results from the spreadsheet just as a reference. I also showed what my approach was when attempting to make a new column in the query which gave me an error. I also attached a sample database with the same values from the spreadsheet. Any and all help is appreciated as I am a beginner with access.

[Micron]

Wrong approach, so I didn't download your db. Best not to store the results of calculations in tables. This link is old, but the principles still apply.
http://allenbrowne.com/casu-14.html

This should be some starting number minus the difference between in/out fields, or sometimes even better, the sum of one field that is +'s and -'s. In the first case, that would be Length + Sum(InField)-Sum(outField) WHERE the ins/outs are only related to the material belonging to a specific ID.
In the latter case, it would simply be Length-Sum(mtlTransaction) where mtlTransaction is like +10, -50, -25 and so on. For a given material ID that started with 400, the answer would be 335. I have no idea how you get some of those numbers that you show.

Also, you should review how to name db objects.
- http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

[Gabba0708]

Thanks for the insight, I will be sure to use queries from now on with regards to calculated fields.

And while I may require it where the ins/outs are only related to a specific ID, for the time being I do not require that.

But as far as the answer being 335, I am not sure I am following, If I start with a length of 400 and then subtract the sum of the materials consumed that is also 400, then it would be 0. and since the sum of the materials consumed sits currently at 400, then the Estimated Remaining just becomes Length - 400. I hope that clears up what I am attempting to do

[Micron]

Those were my numbers.

+10, -50, -25 and so on. For a given material ID that started with 400, the answer would be 335.

400+10=410
410-50=360
360-25=335

In your example I think I see how 800-100 becomes 700 on the next line but I can't see how -100 more becomes 400.
By specific ID I had in mind where the table would show the materialID (maybe 52) and not the name of the material (e.g. 1/2" diameter nylon rope). I might be at a disadvantage as a result of not seeing inside your db, so apologies if I'm off base.

[Gabba0708]

Oh sorry, I thought you meant that 335 should be present in my data somewhere.

Ah I think I understand where the confusion lies, the column Estimated length is a user input, and does not rely on the previous value, so the only calculated field in the data is Estimated Remaining, hope that clears it up.