Search results

just realised i'd done that and edited my post :)

yes i have made it on a test table with the following sql: UPDATE Table1 SET Table1.subtotal = DSum("[clvalue]","table1","[cldate]<=#" & Format([cldate],"mm/dd/yyyy") & "#"); just does a rolling sum of 10 records i have made in a test table, edit: *posts the SQL not just the "update to"...

Any questions let me know, i'm not sure what else i can do on that spreadsheet to make it clearer, row 3 is the parent and row 4 is the child, they link on the Parent Link - Link field so i need to work out the requirement for the parent; by taking the outstanding and subtracting the different...

i dont really know how to explain that in a spreadsheet, i can only just about explain it with a ton of scribble and a load of sketches :/ right, i've tried to generate it with sensible names and detailed descriptions for each column (also there are some formula's in the "Requirement" field...

please see chunk attached in excel

if an order is required the two "Sorts" on the make table are in the correct order (as far as i know) to make it possible to get the correct cascade effect if the table was processed top to bottom so the make table could just add an autonumber?

current field list: code (the code for the thing) requirement (howmany of this are needed) multiple (the number of these needed if a parent requires it) date_required job (the job this is needed for) irn (its reference number) pa_irn (the reference of its parent) allocation (howmany of these...

http://www.access-programmers.co.uk/forums/showthread.php?t=216165 is the data structure for the overall report data source i'm trying to work with the sql i posted above is basically all i've got so far there's a seperate update query than runs before it to cascade down through the parent >...

haha ^^ i've learnt a fair bit through trial and error but have yet to get at all involved with recordsets also not quite sure how to get a sum query to do a running total - i guess its the Where clause i need to get right? or should i be doing an "Expression"? this is what i've got so far...

Yes - Before you shoot me for storing calculated values i'm only doing it to be efficient while i'm generating a complex report, i've made a temporary table and am now updating some values in it edit: *"failing to" would be more appropriate

I have an update query which i want to use to put running totals on a table with ~200k records its VERY slow - hours, is there any alternative way to sum all the values from records with a date less than or equal to the date of the current record? (without using dsum?) [there's an extra...

for two initial questions i have on this - see end :) I have a table of data which forms trees (ie one record relates to another with a internal reference number) this goes on up to 20 levels up (/down) the tree, the fields are basically: code (the code for the thing) requirement (howmany of...

oh and req is the value to update by multiplying the multiple by the req of the level above

ijn is ----- basically which tree its part of if that makes sense ie there are a few thousand trees all with the same format of IRN the IJN determines which tree you are looking at Multiple is the multiple of which the child is required for each of the parents eg: if you need 10 child for...

UPDATE sndbasis LEFT JOIN sndbasis AS sndbasis_1 ON (sndbasis.ijn = sndbasis_1.ijn) AND (sndbasis.pa_irn = sndbasis_1.irn) SET sndbasis.req = IIf([sndbasis].[level]<>0,[sndbasis].[multiple]*[sndbasis_1].[req],[sndbasis].[req]); basically if its not the very root of a tree update to be the...

I saw a tip suggesting that you make your update query as a select to see what it actually finds to update and I worked out why there were a lot more records than first expected: every time a parent occurs it pulls through all its children again (not unexpected in hindsight) That aside i'm...

I have a table of data which creates a tree structure, with "rererence numbers" and "parent reference numbers" I have an update query which looks for a value against a parent and multiplies it by a "per parent" field on the child and writes this value against the child This goes on for up to 6...

"can you not just use up the oldest schedules etc, until they are all used up" thats what the program does :)

the problem is if: line item 1 of Purchase Order 7 has 5 lines of schedules against it alone, and also 5 deliveries, there is no way to individually determine which schedule a delivery is made against, there is no reference like "Schedule Line A" to give "7.1.A" as a unique reference to...

Pegasus - Operations II its some Foxpro based application, can't play with that end unfortunately, i'm getting the data out in ODBC erm, not sure what a relationship diagram should look like but: Purchase Order Header Table: Purchase order number (key field) Supplier name Address, Who...