Summing Across Fields (1 Viewer)

alexfwalker81

Member
Local time
Today, 10:13
Joined
Feb 26, 2016
Messages
93
Evening All,

I have a bit of a conundrum here. Have a look at the attached Excel file.

Columns C-Q show the stock which is committed for despatch on days 1-14. Column B shows the stock which is available. The yellow highlighted cells in J3 and N4 show the days on which there will be no stock available, assuming that stock is not refreshed before days 7 and 11 respectively roll around.

What I am struggling with is how I would add a field which could do a job equivalent to the yellow cells. In other words, cumulatively sum the fields from left to right, until the sum exceeds the value in column B. Once the value in column B is exceeded, this field must show the field name (the day) which takes us over the available stock.

The attachment is somewhat simplified. The live query that I'm building has 60 day fields, so doing something like (1+2+3 and so on) just isn't going to work. Also, I'm not sure that that would achieve anything other than summing the row anyway - it couldn't identify the day on which the available stock is exceeded.
 

Attachments

  • qry_extra_pack_gap.zip
    7 KB · Views: 112

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:13
Joined
Feb 19, 2002
Messages
42,971
Relational databases are not spreadsheets. Repeating groups (which is what your set of columns are called) are a violation of second normal form and therefore, NO RDBMS supports them. The proper schema uses a second table and each of the numbered columns would be a row in that table. Even after you fix the schema, you still can't solve the problem with SQL. You will need to use VBA. Once you are willing to modify your schema, we can help with the VBA. If you are not willing to modify your schema, you can still create a VBA function to solve your problem but I'm sorry but I won't help with it. I'm sure that someone else will. Since I am working essentially for free, I don't write custom code that violates normal forms since I would never write it for myself and therefore never have any use for it.

The custom VBA function needs to loop through the fields and add until your rule is met.
 

alexfwalker81

Member
Local time
Today, 10:13
Joined
Feb 26, 2016
Messages
93
Relational databases are not spreadsheets. Repeating groups (which is what your set of columns are called) are a violation of second normal form and therefore, NO RDBMS supports them. The proper schema uses a second table and each of the numbered columns would be a row in that table. Even after you fix the schema, you still can't solve the problem with SQL. You will need to use VBA. Once you are willing to modify your schema, we can help with the VBA. If you are not willing to modify your schema, you can still create a VBA function to solve your problem but I'm sorry but I won't help with it. I'm sure that someone else will. Since I am working essentially for free, I don't write custom code that violates normal forms since I would never write it for myself and therefore never have any use for it.

The custom VBA function needs to loop through the fields and add until your rule is met.
Thank you for your reply Pat. A VBA solution has felt inevitable from the moment I started working on this.

I appreciate that a database is not a spreadsheet, however I've developed the query in that way as a) I'm a relative novice, intermediate at best, and b) it makes the problem easier to convey to the audience on this forum.

I'm very willing to modify the schema, as I don't need it to look like this - I really just need to know the 'stock outage' day. I think I follow what you're saying about making each day a row in a table, so will play with this tomorrow, then perhaps I'll return to the forum and hope that I'm not in the unfortunate and offensive position of violating the second normal form.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:13
Joined
Feb 19, 2002
Messages
42,971
Great. Please, repost your question with the new schema.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:13
Joined
Jul 9, 2003
Messages
16,244
As Pat has pointed out, you are following an Excel type of design, and although this works perfectly OK in MS Access, it's just not the way to go because you will very quickly fall foul of some very difficult challenges, summing across fields being one of them...

For back ground information I suggest you read my blog on what I call "Excel in Access" HERE:-


I also provide a tool which will transpose your table into separate tables, to carry out this "Transposition" follow along my video instructions HERE:-


The YouTube Video's show you how to take advantage of the new structure.

The “Transpose Tool” is HERE:-


You can get the transpose tool for free by using the coupon code:- t0cpajo

• Get Uncle Gizmo's Latest MS Access YouTube's HERE:-

You must make these changes if you want to do any serious reporting on your data. If you don't take these steps now, you will suffer serious consequences!
 

alexfwalker81

Member
Local time
Today, 10:13
Joined
Feb 26, 2016
Messages
93
Great. Please, repost your question with the new schema.
I feel like I'm on a better track now. In the attached, columns A-C are the live query from my DB. Column D, highlighted in yellow is what I'm trying to achieve.

Can this be done? An expression in the query itself would be nice, but I'm happy deploying VBA (although I couldn't write it!)
 

Attachments

  • qry_future_despatch.zip
    8.4 KB · Views: 207

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:13
Joined
May 7, 2009
Messages
19,169
Create New Query based on qry_future, to add the Running Sum:

select ProductCode, days_to_show, SumOfOrderQuantity, (SELECT Sum(A.SumOfOrderQuantity)
From qry_future As A Where A.ProductCode =qry_future.ProductCode And A.days_to_show <= qry_future.days_to_show) As RunningTotal
From qry_future
Order By ProductCode, days_to_show;
 

alexfwalker81

Member
Local time
Today, 10:13
Joined
Feb 26, 2016
Messages
93
Create New Query based on qry_future, to add the Running Sum:

select ProductCode, days_to_show, SumOfOrderQuantity, (SELECT Sum(A.SumOfOrderQuantity)
From qry_future As A Where A.ProductCode =qry_future.ProductCode And A.days_to_show <= qry_future.days_to_show) As RunningTotal
From qry_future
Order By ProductCode, days_to_show;

I've tweaked the code to the below;

Code:
SELECT qry_future_despatch.ProductCode, qry_future_despatch.days_to_show, qry_future_despatch.SumOfOrderQuantity, (SELECT Count("1") 
FROM qry_future_despatch As A Where A.ProductCode = qry_future_despatch.ProductCode and A.days_to_show <= qry_future_despatch.days_to_show) As RunningTotal
FROM qry_future_despatch
Order By ProductCode, days_to_show;

But I get an error of 'Invalid argument to function' when I run that?
 

alexfwalker81

Member
Local time
Today, 10:13
Joined
Feb 26, 2016
Messages
93
Solved. I was trying to use this on a query which had a GROUP BY in it. I now use a Make Table to create a new table, query off that and it works perfectly.
 

alexfwalker81

Member
Local time
Today, 10:13
Joined
Feb 26, 2016
Messages
93
Solved. I was trying to use this on a query which had a GROUP BY in it. I now use a Make Table to create a new table, query off that and it works perfectly.
Spoke too soon - it counts, but I need the running total to SUM. Tried the obvious of changing Count to Sum, but didn't work!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:13
Joined
Feb 19, 2002
Messages
42,971
In order to make running totals using a query, you MUST have a unique identifier on which you can sort. Without a unique identifier, the join brings back the same records multiple times. I don't see one in the data set.

You should not have had to make a temporary table to do this. Temporary tables make bloat and that means that you need to be conscientious about compacting (it is dangerous to set the database to compact on close so don't do that) or use the technique I recommend which puts the temp tables in a completely separate database which is replaced each time you need to run the process so it never suffers from bloat.
 

Users who are viewing this thread

Top Bottom