Quick Subtotals using FindPrevious

[InstructionWhich7142]

Updated (Quick Subtotals using FindPrevious) now using Seek

Updated - New code in last post

Hi,

I'm trying to do this:

Dim db As DAO.Database
Dim Rst As DAO.Recordset

Set db = CurrentDb
Set Rst = db.OpenRecordset("sndbasis")

Do Until Rst.EOF

 Rst.Edit

 Rst.Fields("Demand").Value = Rst.Fields("Demand").Value + (Rst.FindPrevious "pstk = '" & Rst.Fields("pstk").Value & "'", Rst.Fields("Demand").Value)

 Rst.Update

   Rst.MoveNext
Loop

Rst.Close
Set Rst = Nothing

I know it's not right, but I don't understand how it works well enough to know how I should have written it

I'm trying to replace this:

Rst.Fields("Demand").Value = DSum("[requirement]", "sndbasis", "[cldate]<=#" & Format(Rst.Fields("cldate").Value, "mm/dd/yyyy") & "# and [PSTK] = '" & Rst.Fields("pstk").Value & "'")

Which is SOOOOOOO SLOW, and which I couldn't make update itself

By doing FindPrevious to get the last value, then adding it to the current value and writing the new total back to the same field

 

[spikepl]

1. Your code is a mess indeed. I have no clue what it would do. A .Find (or .FindPrevious) sets the current pointer to the record it finds, and in your loop I cannot even begin to imagine what happens. You'd need to open two recordsets on the same data to do this like this.

2. A recordset from a table has no inherent order (if "sndbasis" is a table), so this is a dangerous way of doing things. You should use a query with ORDER BY stipulated as the source for a record set, otherwise "previous" has no fixed meaning.

3. If you elect to do this looping through a recordset (not efficient) , then what prevents you from
keepin a running tally of the stuff you want to sum?

4. You can make a query with a subquery, where the subquery generates the whatever sum. That is much faster than just looping over a recordset. Check out this http://allenbrowne.com/subquery-01.html - look at the "Year to date" example.

 

[InstructionWhich7142]

1. Your code is a mess indeed. I have no clue what it would do. A .Find (or .FindPrevious) sets the current pointer to the record it finds, and in your loop I cannot even begin to imagine what happens. You'd need to open two recordsets on the same data to do this like this.

ah, I didn't realise .FindPrevious would move the pointer, that would indeed fail hard!

2. A recordset from a table has no inherent order (if "sndbasis" is a table), so this is a dangerous way of doing things. You should use a query with ORDER BY stipulated as the source for a record set, otherwise "previous" has no fixed meaning.

I would love to do all this in a query (that table is just from a MakeTable query) how ever I can't find a way to do the recursion I need within a select query, I asked about that here:
http://www.access-programmers.co.uk/forums/showthread.php?t=223117
and have been working on query based alternatives (as they are so much faster than anything else) but have had no luck

3. If you elect to do this looping through a recordset (not efficient) , then what prevents you from
keepin a running tally of the stuff you want to sum?

How do you mean keep a running tally? (there would be separate subtotals for
thousands of parts)

4. You can make a query with a subquery, where the subquery generates the whatever sum. That is much faster than just looping over a recordset. Check out this http://allenbrowne.com/subquery-01.html - look at the "Year to date" example.

I "think" if I was smarter i would be able to do the recursion and the rolling subtotals with subqueries? I've worked a lot of access out but I'm really a bit stuck with this

 

[spikepl]

First - don't use words like "recursion", because it is completely confusing in this context, since it is completely unrelated to what you are doing.

Second - all tasks are normally accomplished by a little bit at a time, not by one grand swallow. Grab the example from Allen Browne, adapt it to one item, and then expand on that.

 

[InstructionWhich7142]

Sorry, Recursion was related to the step before this one which I am also sort of stuck on and which effects the table (and sorting which you commented on) that's why I mentioned it.

Anyway, yea, I'll see if I can get the subquery working! thanks


err, when you questioned the sorting, that effects how this will work, as it needs to work down in date order ?
the make table has this: ORDER BY ssched.Date, ssched.ijn, sbom.irn;
will that be ok?

 

[InstructionWhich7142]

I copied Allen's example and I'm having the same problem as when I messed about with subqueries before and when I've tried a totals query etc, my SQL for the main update is:

UPDATE sndbasis SET sndbasis.Demand = [sndbasis].[requirement]+(SELECT Max(sndbasis.Demand) FROM sndbasis WHERE (((sndbasis.pstk)=[pstk])););

and I get operation must use an updateable query? it seems like the subquery locks the table?

 

[spikepl]

A TABLE has no inherent order. It does not matter how you made the table.

As to your query, I see little trace of Allen Browne's example there. Take his example and make it work first, adopted to your needs, just as a SELECT query. Then you can play with UPDATE. You will not get to where you want to be by one giant stride - small steps will take you there.

 

[InstructionWhich7142]

sorry, I feel like I'm being difficult but I'm just really confused

A TABLE has no inherent order. It does not matter how you made the table.

So how should I proceed? (access does not support a recursive select query, a MakeTable followed by an Update with the table joined to itself work around this, but you say I shouldn't be using a table at all?)

As to your query, I see little trace of Allen Browne's example there. Take his example and make it work first, adopted to your needs, just as a SELECT query. Then you can play with UPDATE. You will not get to where you want to be by one giant stride - small steps will take you there.

Allen's example is for two joined tables, I took his and renamed it for my single table? I don't see how I can not simplify it as my structure looks nothing like his?
oh and i'd tried once with aliasing the temp table's names but that didn't seem to help, so I tried a seperate totals query, that also gave the same error so I went back to a simple subquery iI had used before, I really am trying everything I can think of, it seems like I'm asking the impossible?

 

[spikepl]

I have no clue where you are at right now. As to the order, I was referring to your first post, in which you cannot base a recordset on a table and assume some given order. The recordset would need to be based on a query.

Allen Browne's example illustrates the concept of using a subquery to get a total. Whether the subquery runs on the a different table or an aliased self-joined table is immaterial for the illustration, but I can see that is too much to swallow in one go. I would suggest you google a bit for some examples of a self-joined table, to get the concept and the syntax.

 

[InstructionWhich7142]

I have no clue where you are at right now. As to the order, I was referring to your first post, in which you cannot base a recordset on a table and assume some given order. The recordset would need to be based on a query.

ok, no worries, i'd rather use a query based on that table (as its quicker than a recordset), and roll down it in order with the query

Allen Browne's example illustrates the concept of using a subquery to get a total.

Yea, i see that bit, i need a Max not a Sum so that should be fine to swap?

Whether the subquery runs on the a different table or an aliased self-joined table is immaterial for the illustration,

His uses some joins as thats how his data is, or are you saying the table HAS to be joined in some way or other and thats why i'm getting the error?

 

[InstructionWhich7142]

right, i've made a basic subquery do a sum as part of a select query (i think i also had it doing Max ok, but as it wasn't updating the fields it was always finding 0, lol)


i've just tried to turn the sum back into an update query and i get the same error?

 

[InstructionWhich7142]

I have just been doing a bit of speed testing with the select subqueries (using SUM, MAX and TOP) and they are all disappointingly slow

the problem is the table (or query) is 200k records, so anything that looks at the whole table to find a top value (and then has to do it for the other 199,999 records) just takes forever

that's why I was trying the "FindPrevious" approach, to bypass any full table sums and just increment current value based on the last total (which should be reasonably fast to find just going back up the table from the current record, or so I thought! lol

 

[InstructionWhich7142]

I've got it!

I went back to the recordset way, but avoided FindPrevious and made another table to store the subtotals (by adding an index and using .Seek i've got the runtime down to a few minutes from many hours)

I also cleaned my code up a ton (I hope)

Function Test1()


Dim SubTotalSum As Long

Dim db As DAO.Database
Set db = CurrentDb
Dim Rst As DAO.Recordset
Dim rst1 As DAO.Recordset


Set Rst = db.OpenRecordset("sndbasis") 'Still a table - bit stuck with this
Set rst1 = db.OpenRecordset("sndbasispstk")

rst1.Index = "primarykey"
Rst.Index = "primarykey"

Do Until Rst.EOF
   
'Find the last total in the temp table
 rst1.Seek "=", Rst.Fields("pstk").Value

'Add to the current value to the total to get the new total
 SubTotalSum = rst1.Fields("clqty").Value + Rst.Fields("Demand").Value
 
 'Write the value back to both tables
 Rst.Edit
 Rst.Fields("Demand").Value = SubTotalSum
 Rst.Update
 
 rst1.Edit
 rst1.Fields("clqty").Value = SubTotalSum
 rst1.Update
 
   Rst.MoveNext
Loop


Rst.Close
Set Rst = Nothing

rst1.Close
Set rst1 = Nothing

End Function

Please let me know if I'm still doing it wrong?

Oh when you said the "Previous" wouldn't be right because the table doesn't have an order, would that also effect the MoveNext (if i'm expecting the subtotals in the correct order?) I've added an index to the main table to speed things up, will that have sorted the order issue? or do I still need to get an order by in there somehow?

Updated:

 

[spikepl]

You do. Once more : a TABLE has no particular order. If you are relying on things being in a given order, then you WILL get a surprise sooner or later, UNLESS you use a recordset based on a query in which you specify an ORDER BY. Replace "sndbasis" with "SELECT ListOfNecessaryFields FROM sndbasis ORDER BY WhatereverItIs"

 

[InstructionWhich7142]

Oh! I didn't realise I could just do that and still do the .Edit and .Update, I thought a query would be read only,

I've also just had to disable the .Index I had used, but it doesn't seem to have effected performance, awesome

Thanks again