Search results

I have found update queries against a temp table to be the quickest solution to explode BOMs (I need to split them down against date requirements as well) However I'm still bumping into major performance issues trying to do rolling subtotals after expanding these BOMs, may I ask what solution...

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

ah, I didn't realise .FindPrevious would move the pointer, that would indeed fail hard! 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...

I'm still really stuck on this, anyone got any thoughts?

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 =...

I thought the same and set it up, but I need to do some recursive stuff that I can't seem to get to work on a standard select query :( I asked specifically about the recersive bit here: http://www.access-programmers.co.uk/forums/showthread.php?t=223117

I think this is what I need (it describes my problem): http://msdn.microsoft.com/en-us/library/ms186243.aspx but I'm afraid I dont really understand it, I'm also wondering if I can somehow trick the query into running on itself or something, any ideas?

Updated: Allowing a Circular reference UPDATE: I have made this work by making a second query and linking it to the first, it ran once and as far as I could tell gave the correct results down levels!! :D However once I saved it and tried to run it again it gave a circular reference error, why...

Hi, I have a query which works through a table of data, the data is interdependant, eg the value of a lower level is dependant on the top level and the query calculates these values If I sort by level so the higher levels are calculated first it works, however I am unsure if this is reliable...

Apologies, I know the mantra of no calculated values and I should have explained my purpose a little more to start with: This table is itself only temporary, generated from some linked tables to do some complex calculations before generating a report and being deleted. While we are on the...

I have an update query, it updates a field with a calculated value which is based on the same field (but of the level above) UPDATE sndbasis LEFT JOIN sndbasis AS sndbasis_1 ON (sndbasis.ijn=sndbasis_1.ijn) AND (sndbasis.pa_irn=sndbasis_1.irn) SET sndbasis.requirement =...

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

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

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

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?

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

do you mean: ? 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...

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

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

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