Running Sum Query (1 Viewer)

[access10000]

Hi

I have a query that shows part numbers with a total qty ordered for a specified time period. This first part of the query is fine and is made into a table.

I am then trying to use this table to create a running sum with the list sorted in descending order, and ultimately break the list where the sum reaches 80% of the total qty across the whole list. I have tried the following formula and the table has been indexed in the descending order it would need to be in. The IndexAlias has also been created and made into a fixed table prior to running the 'Running Sum' query.


RunTot: DSum("qty_ordered","Core Stock","[Index]<=" & [IndexAlias] & "")


I have taken this formula from the microsoft link below, (Method 2), and adapted it to my query. However, although I have a decent understanding of Access I do not understand the '&' in this formula. Removing this and the double quotes on the end returns an error and leaving them in returns zeros all the way down the 'RunTot' field. If anyone can help I would appreciate it as I am stuck on this.


http://support.microsoft.com/kb/290136


many thanks

 

[ajetrumpet]

I am then trying to use this table to create a running sum with the list sorted in descending order, and ultimately break the list where the sum reaches 80% of the total qty across the whole list.

So, does it work? I am a bit confused about what you are asking.

If you're asking for an explanation of WHY they have to use quotation marks to concatenate the field names, I have no idea. The quotation marks are probably the biggest nuisance in this program.

If you're saying that the query does not work, look at the sample. It uses the method, and works fine.

 

[Mike375]

Adam

The limitation I find is when the field has some equal numbers. In that case the total of that number is shown rather than the progressive total.

3......18
3......18
3......18
3......18
3......18
3......18
6......24

Is there away around that.

Also, if I select records (by query criteria) the total is the total of all the records.

I can achieve all I want with a macro that is SetValue and NextRecord and done as a RunMarco with conditional stop. But even for me that is crude

 

[ajetrumpet]

3......18
3......18
3......18
3......18
3......18
3......18
6......24

Is there away around that.

I doubt it, since the query has to evaluate horizontally, by record. There would be no way to check for duplicate records (vertical evaluation). I once wrote a loop function that created a RunningTotal column in a table, and then opened a query based on that column, but that's a terrible "cheat" method.

So, you've done this in an actual query with the SetValue action? How did you go about it?

 

[Mike375]

The attached has 2 macros.

Number is the ID number which as you can see is being used for sorting. Altough this is "diet" I use the same thing in my main insurance data base. I just use the Access Toolbar filter to group different policy types, mode of payments or whatever and then hit the Total label on the head for adding premium etc.

I think this is more of a "work sideways" than a "work around"

As a side note, if you have 10 text boxes that are part of one record (they have to for practical reasons be that way) and they are for appointment times, can you get them to sort A-Z across the row. In other words an appointment time is made for 8am but that appointment is entered at the end of the day etc. However, the appt times must go aross in ascending time as the "click to make ppt" is done. ie. the row is sorted sideways as the appt is entered

 

[Mike375]

I just tried that with 12000 records. The table has 10 fields and the query supporting the form has four calculated fields of which three are IIf

With Echo Yes it took just under 2 minutes to whiz down With Echo No it was about 50 seconds

 

[ajetrumpet]

I sure wish I could see the result of those Macros Mike.

You are using other objects though, so you are cheating!

BTW, there MAY be a way to do this by query only. I don't have time to screw around with it right now, but that doesn't mean I won't do it. I think you might have to use a combination of different criteria DSUM() functions spread across (maybe) 3 or 4 columns, but I'm not sure. I can't do it in my head!!!

Maybe you could do it by subtracting the current record's number from the DSUM of the column, and then doing something with that result in another column...???

Now that's a pickle!

 

[Mike375]

Here you go. Open the form Query1 and click the blue label Total.

Actually, I just did Find and Replace to clear the 12000 totals and it would only do just over 9500 and then I had do it again to get the remaining 3500

Did you figure how to sort sideways.

 

[Mike375]

Adam, in the interests of wasting time I just changed the GoToRecord to Previous and started at the Last.

I think it took longer to run, probably because it was going uphill

That gives progressive subraction

 

[ajetrumpet]

Dude, you're method is nuts.

and No, I haven't figured out the sideways method yet, but when i get a chance, you can bet that I am going to wrap my head around it. Weather or not I get all the way around is a different story though!

 

[ajetrumpet]

Adam, in the interests of wasting time I just changed the GoToRecord to Previous and started at the Last.

I think it took longer to run, probably because it was going uphill

That gives progressive subraction

I don't think that makes a heck of a lot of difference Mike, because you have so many actions you are running with each record anyway.

The speed would be different in query...

 

[Mike375]

The sideways sort is so simple as to be unreal. You may know too much about Access to get the answer

 

[Mike375]

Dude, you're method is nuts.

QUOTE]

But it works

What I find interesting is how people like to see things happening. There is about a dozen people use it for insurance stuff and that usually involves about 100-200 records being done at a time so it is only a second or two for the run. Everyone of them prefers it with Echo Yes

 

[ajetrumpet]

The sideways sort is so simple as to be unreal.

Unreal? It may be, but that doesn't mean that it's not possible!

You may know too much about Access to get the answer

That's a dangerous quote Mike! I wouldn't be saying that too many times. I might start thinking I know something.

Actually, if you want a comment about that, I don't think I know sh** about the program, but my knowledge of logic and mathematics (I would say) is out of this world. (just speaking the truth here)

As for Access, I don't even use this program at work. I use Accounting software, and Access is not designed to compliment that with any ease. I think it's great to create customized solutions, but other than that, no way!

 

[access10000]

Running Sum

Thanks for all the replies and suggestions, sorry I have only just picked them up. I have tried adapting all the ideas to my query and the SQL below fits best to what I need. Maybe others will find it useful?:

SELECT a.Part_Number, a.Qty_Ordered, a.Grouping_Code, a.Index, (Select Sum(Qty_Ordered) from [Order_Table] where [Grouping_Code] = a.Grouping_Code and [Index] <= a.Index) AS RunningSum INTO Order_Table_2
FROM Order_Table AS a
ORDER BY a.Grouping_Code, a.Index;

This takes one table and then makes a new one from it with a running sum added by using a subquery/alias, which can obviously be changed to a Select query or whatever. I have attached a sample file. It's Access 97 format so I am sure everyone can open it!

ajetrumpet - thanks for your sample file but I couldn't open it. I have probably got an older version of Access. Maybe my file has the same idea?

 

[ajetrumpet]

Access,

Your sample has the same problem that Mike and I were discussing earlier, but yours creates a different problem. If you have duplicate numbers in the column that is being summed. The count in the Running Sum column RESETS itself when it runs into the duplicate number. So, you can't use this.

I have attached the sample, to show you what I did. My first sample was in A2003, but this one is A2000 format. If you A'97 though, you won't be able to open either of them. So, if you can't, just trust me, what you have right now won't work.

I didn't think of doing it your way though (make-table query, with subquery), but it's obvious that it runs into the same problem as what I posted earlier. I think there is a way to get around the duplicate problem, but as of right now, I don't have the time to figure it out!

(Mike, take a look at the sample the OP put up here. What do you think of it? It is the same problem, isn't it?)

 

[Mike375]

Yep..............10 characters so I could post

 

[ajetrumpet]

Nice... Don't you hate that requirement? It really should be more than that.

 

[Mike375]

Hey Adam, you are probably having trouble with DSum at the moment because it is up and down and you are distracted by left to right

 

[ajetrumpet]

OK OK, enough of the smart a** comments my good friend. I'm not even working with the problem right now!

If I do get this though, you're going to put another star on my hat, correct? I just want to make sure that the promise is still there...