Summing Across Fields

[alexfwalker81]

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.

 

[Pat Hartman]

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]

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]

Great. Please, repost your question with the new schema.

 

[Uncle Gizmo]

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:-

Excel in Access - Nifty Access

Excel in Access - Problem & Solution! One of the most frequent problems I see is people confusing MS Access with Microsoft Excel. This leads to fundamental design errors. Now, the problem is, this error "A Major Error" does not become apparent straight away. Unfortunately your database will work...

www.niftyaccess.com

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

Excel in Access (Parts 1, 2 and 3 as Video Instructions) - Nifty Access

Excel in Access (Parts 1, 2 and 3 as Video Instructions) Excel in Access (Part 1) Excel in Access (Part 2) Excel in Access (Part 3) MS Access Your Data With Sub-Forms How to use the Normalization Tool Convert an Excel "Contacts" Spreadsheet to MS Access Convert an Excel "Contacts" Spreadsheet to...

www.niftyaccess.com

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

The “Transpose Tool” is HERE:-

Nifty Transpose Tool - MS Access

Are you regularly tackling the challenges of disordered spreadsheet data? Seeking a solution that can provide a smoother, more efficient transition from spreadsheet structures to an effective database system? Here's your solution! Introducing the "Transpose Tool," also known as the "Database...

gum.co

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

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

Nifty Access - Microsoft

I have been answering questions about MS Access on various forms for 15 years! This experience has given me a unique insight to the problems people have when...

www.youtube.com

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]

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!)

 

[arnelgp]

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]

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;

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]

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]

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!

 

[alexfwalker81]

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!

Solved, corrected the syntax and now it sums correctly.

 

[Pat Hartman]

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.