runningsum subtracted from total funds

[blewis81]

Need a query that will sum all the "extendedprice" fields (i.e. total cost) from records with a specific project number. THEN, that running sum needs to be subtracted from an amount of money so that at any given time or at each new record the user knows how much money is left. I have the query for the required sums...but not sure how to use in next step... use as a subquery or somehow use in an expression?

[CJ_London]

what is the difference between a running sum value and how much money is left? wouldn't they be the same?

if the 'amount of money' is the budget in a different table, just include in your query.

might be an idea to provide some example data, the expected outcome from that data together with the sql to the query(s) you are using

[blewis81]

Running sum of order totals subtracted from amount of money have on hand (budget) to spend...
Added to the query successfully. How do I get the "difference" calculation between the two? Done in the query? Outside the query? Sorry, very NOVICE here.
Example data: orders totaling 904.59, 82.96, 125.88, 1218.85, 516.93 (Totals row in query sums them to 2849.21). Now added to the query a column with how much money is in the pot to use: 682,000. I need the 2849.21 (which will continually grow with the running sum) to be subtracted from the 682,000.

Not to sound dense but don't know what/where "sql to query" is. I am guessing that is the part I need help to complete. THANK YOU

[CarlettoFed]

If you attach an example file it is perhaps easier to help you.

[CJ_London]

don't know what/where "sql to query"

open a query, click on the sql option (left on ribbon or bottom right of access window)

Still don't know what your data looks like - provide it, don't describe it - or as carletto suggested, upload a copy of the db (remove irrelevant tables/queries/forms/reports, change data if confidential, compact and repair then zip and you can upload). You know and understand it, we haven't a clue without a clear example and we need to get a picture in our head about what the data looks like.

tblSomething
ID, desc, amount, date
put values here that cover all the eventualities you need to handle such as duplicates or missing data/records

qrySomething
desc date, sum(amount)
show sql to query, and the outcome you are actually getting and if required the outcome you actually want

this is what I want from the above data
????

edit: at the moment the best I can suggest is use a form with a subform but no idea how you would construct it to do what you want

[blewis81]

SELECT qry_Procurement_All.WBS, qry_Procurement_All.Vendor, Sum(qry_Procurement_All.ExtendedPrice) AS SumOfExtendedPrice, qry_Procurement_All.PR_Number, qry_Procurement_All.Procurement.Procurement_ID, qry_Procurement_All.PO_Number, qry_Procurement_All.CustomerRequestDate, qry_Procurement_All.Requestor, [ODC Calculation].[ODCs Received]
FROM qry_Procurement_All, [ODC Calculation]
GROUP BY qry_Procurement_All.WBS, qry_Procurement_All.Vendor, qry_Procurement_All.PR_Number, qry_Procurement_All.Procurement.Procurement_ID, qry_Procurement_All.PO_Number, qry_Procurement_All.CustomerRequestDate, qry_Procurement_All.Requestor, [ODC Calculation].[ODCs Received];

This is the Query (SQL requested). Query is called "qry_ProcurementID_Subtotal". Trying to figure out how to upload DB now.

[blewis81]

The main DB ("Geoff Haney") and my linked access tables (ProjectProcurement) attached for visual. Praying I did it right.

As mentioned above, the query is called "qry_ProcurementID_Subtotal" I need the "Totals" line from that Query to be subtracted from the Table "ODC Calculation" (field- "ODCs Received"). I want the "Totals" line from the Query to populate the "Used" field in the Table "ODC Calculation" as well, but couldn't figure out how to get the DB to do that. Currently, the number is manually entered.

As always, appreciate your patience/help! I hope I am providing what you are requesting and doing so in the correct way.

[CarlettoFed]

The ProjectProcurement_bl_2020rev_AccessForum.accdb file should be saved in an older version such as 2010 because not everyone has the latest one and therefore it is not possible to manage it to help you.

[CJ_London]

Agree with carletto - you need to save your backend as an earlier version

[blewis81]

Says I cannot save this database in an earlier version, because it uses features that require the current file format. Is there another way to accomplish this? I can attach screen shots of the setup?.? Sorry