dsum alternatives

[vbaInet]

Ok, I'm a bit confused why you showed me the second query. Is this related to the previous UPDATE statement or were you just curious why it works?

 

[InstructionWhich7142]

yes, its the previous update however i need it to do the update while its doing the rolling totals

I am also curious why it works

I need it all to happen at the same time because the requirement i want to sum for one level can only be derived after the levels above are derived,

I can perform the Dsum's for the availables first, giving rolling totals down G-L, however after that for every level i need to work out how many are actually required and what can be satisfied by availables before i can work out the requirements for the children - if a requirement is satisfied then non of the children are required to satisfy it,


to give some context i'm basically trying to make a complex MRP - "Master Schedule" report - the "Trees" are Bills of Material so if a part isn't available its sub components need to be required so it can be built

 

[InstructionWhich7142]

lots of editing occurred in the previous post, hope it helps clear things up

 

[InstructionWhich7142]

yea, this works as intended on like, 2000 records:


UPDATE sndbasis SET sndbasis.rollingsum = DSum("[req]","sndbasis","[cldate]<=#" & Format([cldate],"mm/dd/yyyy") & "# and [PSTK] = '" & [pstk] & "'");


in about 10/15 seconds, so ovbiously with 200k it fails hard - i think it has issues with running out of memory etc


would i be better off using VBA and having the subtotals as variables or something and keeping a running total in memory and just add to it and write it back instead of doing a dsum every time?

 

[vbaInet]

That was what I suggested in my earlier posts. Try using a recordset. I'll look into another way later

 

[InstructionWhich7142]

do you mean:

Try using a recordset. Create the Sum beforehand (in a query)

?

I thought a recordset was in effect a temporary table in memory? not done anything with them yet but I'm sure I'll work out how to manipulate them. one problem, just don't actually know what i would do with it once i had it in memory? and what form it would take when i create it?

also how do you mean "create the sum beforehand (in a query)?" i dont see how i can do that? isn't that what i have been trying to do?

 

[vbaInet]

I will advise when I've looked into an alternative. Not very likely that there's one but I haven't had a think yet. Just enjoying my cappuccino at the moment

 

[InstructionWhich7142]

its the end of my work day here but i'll be keeping up to date on this over the weekend thanks for all ur time so far

 

[vbaInet]

its the end of my work day here but i'll be keeping up to date on this over the weekend thanks for all ur time so far

That's if I come online over the weekend I may

Have a good weekend!

 

[InstructionWhich7142]

not quite sure what they have done here:
http://bytes.com/topic/access/answers/828972-complex-running-totals-ms-access-2003-query
but its possibly similar to what i was planning to do with VBA?

 

[vbaInet]

Yes, you can get the cummulative sum using a subquery but the problem here is that query becomes read-only. Read-only queries are not allowed in UPDATE statements. I haven't yet brainstormed.

 

[InstructionWhich7142]

i've found a problem with the VBA idea where i just keep a running subtotal in a variable,

there is no way i could meet all the sort criteria simulatniously,

i need to have:

The Part code overall requirements sorted by date:

the part + Job sorted by date:


ie:

P: PJ: D:
A, 1 1st
A, 2 5th
A, 2 7th
A, 1 9th

(PART, PART+JOB, DATE)

so this would be ok to keep a running total of the Part by date, but the Part Job wouldn't be, as its requirements do not fall in neat blocks!

 

[vbaInet]

You've confused me again. How is Part (which is Text) + Job (unknown) = a numeric value?

I've now got a bit of time to rack my brains. Upload a sample db and tell me how you would like the final results (from which table/fields into which fields/table).

 

[InstructionWhich7142]

sorry, its not, just an oversimplification, and my lack of imagination because job numbers are numeric in our system! Job&part would just make a string,


I have found an interesting suggestion and i'm starting to develop some thoughts,

I believe i have found an example of what you meant by "use recordsets" earlier, basically get every unique value i want into a recordset or a temporary table, increment the quantites against those so i can simultaneously track every rolling total without any "Dsum"age?

database to follow:

 

[vbaInet]

I have found an interesting suggestion and i'm starting to develop some thoughts,

I believe i have found an example of what you meant by "use recordsets" earlier, basically get every unique value i want into a recordset or a temporary table, increment the quantites against those so i can simultaneously track every rolling total without any "Dsum"age?

Good man!

Into a recordset - yes
Increment quantities - yes
Update quantity in recordset - yes.

See how it goes before updating the db. Maybe we could work with your idea.

 

[InstructionWhich7142]

there is some end of the month/start of the month pandemonium occurring so it may be a day or two before I get to this, also i'll probably do it with temporary tables as i'm more familiar with them,

I'm not sure how to go about working sequentially through a table, its not something i've needed to do, generally i just have a "processed flag" field and a dlookup based on it as I have to hit some records multiple times with everything else I've done.

 

[vbaInet]

Perhaps the following link will give you some more insight into working with recordsets:

http://allenbrowne.com/ser-29.html

The way I'm thinking of working with recordsets will be different from yours because I'm thinking of linking the recordset directly to a query. This is non-conventional though and hasn't been done before. But I'm waiting to see what you come up with.

The barebones of looping in a recordset is as follows:

dim rs as dao.recordset, intSumQty as long

set rs = currentdb.openrecordset("SELECT ...", dbopendynaset)

do while not rs.eof
    intSumQty = intSumQty + rs!Quantity
    rs.edit
    rs!Quantity = intSumQty
    rs.update
    rs.movenext
loop

rs.close
set rs = nothing