dsum alternatives

[InstructionWhich7142]

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 criteria which is just "if some text field = some text field" ie dont just total the entire table, only sum parts of the same type!]

 

[vbaInet]

Are you saying you are saving the sum generated from the DSum?

 

[InstructionWhich7142]

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

 

[vbaInet]

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

You knew that was coming. I already had my AK47 ready

Try using a recordset. Create the Sum beforehand (in a query), loop through that and update the values in the table accordingly.

 

[manishgoel]

To sum all the values from records with a date less than or equal to the date of the current record in querry design

 

[InstructionWhich7142]

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 and its not looking right


SELECT sndbasis.pstk, sndbasis.Date, Sum(sndbasis.req) AS SumOfreq
FROM sndbasis
WHERE (((sndbasis.Date)<=[date]))
GROUP BY sndbasis.pstk, sndbasis.Date;

 

[vbaInet]

It's possible but there' has to be an order in place.

Before you proceed, let me see your current UPDATE statement. Also show me some sample records of what you're trying to do.

 

[InstructionWhich7142]

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 > child links to populate the values i then want to sum

 

[vbaInet]

Can you copy the relevant bit from the thread and paste it on here. I'm not a big fan of long threads... limited time you see

All I want to see is some sample records, perhaps in a spreadsheet. And I want to see the UPDATE statement in the UPDATE query.

 

[InstructionWhich7142]

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 things are already avilable to this job {a total by code and job})
stock (howmany of these things are in general stock {a total by code basically})
delta (the difference between demand and supply)


the existing make table and update queries are as follows:

make table

SELECT sbom.pstk, sbom.irn, ssched.ijn, ssched.Date, [ssched].[qty]-[ssched].[delivered] AS outstanding, sbom.level, sbom.pa_irn, sbom.multiple, [ssched].[date] & "-" & [ssched].[ijn] & "-" & [sbom].[irn] AS clmrpkey, cst.text, IIf([sbom].[level]=0,([ssched].[qty]-[ssched].[delivered]),0) AS req, 0 AS rollingsum INTO sndbasis
FROM cst INNER JOIN (ssched INNER JOIN sbom ON ssched.ijn = sbom.ijn) ON cst.pstk = sbom.pstk
WHERE ((([ssched].[qty]-[ssched].[delivered])>0) AND ((cst.text)=False))
ORDER BY sbom.irn, ssched.ijn;



update for requirements

UPDATE sndbasis LEFT JOIN sndbasis AS sndbasis_1 ON (sndbasis.pa_irn = sndbasis_1.irn) AND (sndbasis.ijn = sndbasis_1.ijn) SET sndbasis.req = IIf([sndbasis].[level]<>0,[sndbasis].[multiple]*[sndbasis_1].[req],[sndbasis].[req]);

 

[InstructionWhich7142]

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?

 

[InstructionWhich7142]

please see chunk attached in excel

 

[vbaInet]

Your spreadsheet isn't what I'm after. It doesn't make much sense to me

What would be nice is:

1. Here are the records (per table and joined)
2. Here's what I would like to achieve

all in one spreadsheet... indicating what column was updated and what calculation was made.

5 records or so will suffice.

 

[InstructionWhich7142]

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 which might make things clearer however they are VERY simplified)

edit: now iwht xls attachment!

 

[vbaInet]

I will have another read of your thread and see if I can make head or tail from it

Stay tuned!

 

[InstructionWhich7142]

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 types of available

then the requirement for the child is the requirement for the parent * the child's multiple (this gives the raw requirement) then i need to subtract the availables for this part to give the child's requirement

for the next level the child becomes a parent and another child is worked out in the same way down the cascading tree layout

 

[vbaInet]

So you say that the update query works but is very slow? It actually updates the field with the running sum?

 

[InstructionWhich7142]

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" statement*



i haev another query mentioned in one of my other posts:

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]);

which seems to successfully cascade down all levels and update the requirement field based on the level above
(i'm suprised it does because it seems to use the value it has calculated for a parent to calculate the value for a child, and then to use that value for the child to calculate it for the child's child) and it does all this at once, not in multiple passes - unless the fact that my join makes 1.3 million records means it gets to effectively have multiple passes in one hit?

 

[vbaInet]

Can I see the full SQL statement with the DSum() included.

 

[InstructionWhich7142]

just realised i'd done that and edited my post