I have 2 tables

A0505 - 1 record per customer
T0505 - many records per A0505 entry

I need to update the A0505.WIPBal with sum(T0505.Cost) for all A0505 records

I have tried various methids, but none seam to work.

Please advise, what I wish to do is basically

"UPDATE A0505 SET WIPAsAt = SUM(T0505.Cost) WHERE A0505.MattID = T0505.TSAY"

The old way of doing it was create a recordset, then loop through and update each A0505 entry, but this is not fast enough. Code below for reference...

Set RS = PBDbase.OpenRecordset("SELECT TSAY, SUM(COST) FROM T0505 WHERE DATE<= aDate AND (BillNo=0 OR BillDate> aDate) GROUP BY TSAY")
A0505.Index = "MattID"
RS.MoveFirst
RC = 0
While Not RS.EOF
A0505.Seek "=", RS("TSAY")
If Not A0505.NoMatch Then
A0505.Edit
A0505("WIPAsAt") = RS.Fields(1)
A0505.Update
End If
RS.MoveNext
Wend

Do you really have to store that calculated value? It's not advisable to store such values.

If you really want to do this then you can use an UPDATE query.

I need to store this calculated value, so that it can be displayed in a Crystal report.

Why is it not advisable to store this value?

I am using VB6 as he front end, and these results get updated as a "balance as at" for complex accounting reports everytime they are run

If it can be calculated or generated then there's no need storing the value. However, in your case it seems you are actually doing more with the result.

Have you had a look at how to create an UPDATE query, or is that what you are enquiring about?

I have tried the update query, but cannot get my head around it, I tried, then found errors, so would appreciate any advice from you guys.

I can add the "where" clause later, as that is related to dates.

Do I need a join of some sort or am I missing the point?

"UPDATE A0505 SET WIPAsAt = SUM(T0505.Cost) WHERE A0505.MattID = T0505.TSAY"


You will need to get that value and put it in there. You can't look it up that way.

So how would I do it?

Either create an Update query and run it or in VBA :

Currentdb.Execute "UPDATE A0505....." , dbFailOnError

JR

It is the update statement which I do not know how to do, that is my question.

I know what I want it to do, but cannot work out how it is written.

"UPDATE A0505 SET WIPAsAt = SUM(T0505.Cost) WHERE A0505.MattID = T0505.TSAY"

The SUM(T0505.Cost) part has to come from a variabel or a control as vbaInet stated.

so

"UPDATE A0505 SET WIPAsAt = SomeVariabel WHERE A0505.MattID = T0505.TSAY"

I am using VB6 as he front end,

I'm not sure if VB6 and VBA uses similar methode.

JR

I could use a variable, but this would only be for one at a time. I am already doing this in a loop, but it is inefficient.

I want to be able to update 10,000 A0505.WIPAsAt entries with the total cost of records (possibly 300 for each A0505 record) in one go.

I then need to add dates onto the query.

What I am aiming for is a total cost at a given date of T0505.Cost, where they may have been billed or written off or still as WIP at any date.

(WIP = Work In Progress)

This is what I was thinking on paper...

SQL$ = "UPDATE A0505 SET WIPAsAt = SUM(T0505.Cost) FROM A0505 INNER JOIN T0505 ON A0505.MattID = T0505.TSAY WHERE T0505.DATE<=" & funDtoSQL(aDate) & " AND (T0505.BillNo=0 OR T0505.BillDate>" & funDtoSQL(aDate) & ")"

Right then PSL, let's get you to use the DSum() function. Look it up and see what you can come up with. Syntax afterwards, is:

"UPDATE A0505 SET WIPAsAt = DSum() WHERE A0505.MattID = T0505.TSAY"

Thanks,

So far I have managed to update ALL A0505 records with the grand total of ALL T0505 records with the followig


UPDATE A0505 SET WIPAsAt = DSum("Cost", "T0505")


So i then try to get it to calc on matching ID's using


UPDATE A0505 SET WIPAsAt = DSum("Cost", "T0505", " WHERE A0505.MattID = T0505.TSAY")


But I get an unknown error

Please advise

Thanks,

UPDATE A0505 SET WIPAsAt = DSum("[Cost]", "T0505", " WHERE A0505.MattID = T0505.TSAY")
But I get an unknown error

Please advise

Goodie. Do you notice anything missing where I've highlighted red? A closing brace maybe:) Post back if you're still struggling.

So you are using VB6.

Create a function in an existing or new module

Public Function UpdateWIP()

Dim MyDB As DAO.Database

Dim RsParent As DAO.Recordset
Dim RsChild As DAO.Recordset

Dim SqlP As String
Dim SqlC As String


Set MyDB = OpenDatabase(YourPathAndMdbNameHere)


SqlP = "SELECT Customer, Sum(Nz(Cost,0)) AS SumOfCosts
FROM A0505
GROUP BY Customer
ORDER BY Customer;"

Set SqlP = MyDb.OpenRecordset(SqlP)
Set RsChild = MyDB.OpenRecordset("T0505")

Do Until SqlP.EOF

RsChild.AddNew
RsChild("Customer") = RsParent("Customer")
RsChild("Cost") = RsParent("SumOfCosts")
RsChild.Update
RsChild.Close
RsParent.MoveNext
Loop
RsParent.Close

Set RsParent = Nothing
Set RsChild = Nothing

End Function

This will populate your child table with the sum values of the parent table.

Code is untested and filed names are for brevity only.


David

OK, now I have

UPDATE A0505 SET WIPAsAt = DSum("Cost", "T0505") WHERE A0505.MattID = T0505.TSAY

But, I get a inpubox asking for T0505.TSAY, Do I need to link these tables using a join, as the database has no linking information

I have similar code at present (posted previously), but it is inefficient, I must create a single SQL update statement to perform the same process

Thanks anyway



So you are using VB6.

Create a function in an existing or new module

Public Function UpdateWIP()

Dim MyDB As DAO.Database

Dim RsParent As DAO.Recordset
Dim RsChild As DAO.Recordset

Dim SqlP As String
Dim SqlC As String


Set MyDB = OpenDatabase(YourPathAndMdbNameHere)


SqlP = "SELECT Customer, Sum(Nz(Cost,0)) AS SumOfCosts
FROM A0505
GROUP BY Customer
ORDER BY Customer;"

Set SqlP = MyDb.OpenRecordset(SqlP)
Set RsChild = MyDB.OpenRecordset("T0505")

Do Until SqlP.EOF

RsChild.AddNew
RsChild("Customer") = RsParent("Customer")
RsChild("Cost") = RsParent("SumOfCosts")
RsChild.Update
RsChild.Close
RsParent.MoveNext
Loop
RsParent.Close

Set RsParent = Nothing
Set RsChild = Nothing

End Function

This will populate your child table with the sum values of the parent table.

Code is untested and filed names are for brevity only.


David

Save the value of DSum into a variable then perform your update, just to break it down for you.


Dim sumCost as Currency

sumCost = DSum("[Cost]","T0505")

CurrentDb.Execute "UPDATE A0505 SET WIPAsAt = " & sumCost & " WHERE A0505.MattID = T0505.TSAY"


Try that.

DSum is not defined ...?

Save the value of DSum into a variable then perform your update, just to break it down for you.


Dim sumCost as Currency

sumCost = DSum("[Cost]","T0505")

CurrentDb.Execute "UPDATE A0505 SET WIPAsAt = " & sumCost & " WHERE A0505.MattID = T0505.TSAY"


Try that.

Where are you calling this from? Can you post your db?

Visual Basic 6, references to DAO 3.51

database contains client data so unable to provide sorry

Where are you calling this from? Can you post your db?

I meant, are you calling it from a module or on a form? Show me the whole function (if it is one) as you have it (including the signature).

This is a function



UPDATE A0505 SET WIPAsAt = DSum("Cost", "T0505") - works for grand total on each line but not individual

Dim SumCost As Currency
SumCost = DSum("[Cost]", "T0505")

PBDbase.Execute "UPDATE A0505 SET WIPAsAt = " & SumCost & " WHERE A0505.MattID = T0505.TSAY"

Not quite the same line as what I gave:

PBDbase.Execute "UPDATE A0505 SET WIPAsAt = " & SumCost & " WHERE A0505.MattID = T0505.TSAY"
CurrentDb.Execute .....


What do you mean by "but not on idividual"? I thought you wanted the grand total? If you wanted a sum of a specific group, then you include the third paramter in the DSum() function. That acts as a filter.

PBDBase is the database name we have opened.

I need to calc the total T0505.Cost for each A0505 record, not the grand total of ALL T0505 on every A0505 record.

Each A0505 record has many T0505 records, which I need to update on the database

You don't need that PBDBase line. Just use CurrentDb. Execute is a method of the CurrentDb object.

How is T0505 linked to A0505? Can you provide the (relevant) field names with a record as an example from these tables? E.g.

T0505
-------------------
ID - 7
Cost - 124.95

OK

A0505 is an account header - 1 per client
T0505 are timesheet records - many per client (A0505 record)

A0505.WIPAsAt should = total of T0505.Cost

There will need to be a date selection on this also, but that can be added in after this initial statement is working.


T0505
-------------------
TSAY - 7 Cost - 124.95
TSAY - 7 Cost - 4.95
TSAY - 7 Cost - 0.95
TSAY - 8 Cost - 50.00
TSAY - 8 Cost - 52.00
TSAY - 9 Cost - 107.54

A0505 (MattID is unique)
-------------------
MattID - 7 WIPAsAt should = £130.85
MattID - 8 WIPAsAt should = £102.00
MattID - 9 WIPAsAt should = £107.54

Many thanks for your help so far

That's not a problem PSL. So at the moment there's no relationship between the two tables? Maybe ClientID? It's either you loop through each client on T0505 adn run the SQL a For statement (for example) or we could look at amending your code so it's more efficient. I noticed you are using the seek method, that can be abit slow. If you want to use sql just let me know and I will write the code that includes the loop and sql string.

There is no set relationship, all T0505 entries have the same TSAY, which links to the unique A0505.MattID field.

The problem with looping, is we are running the loop 13,000 times (for each A0505 record), but there is 215,000 T0505 records in total, so any SQL statements run slow, this will also be run across users slow networks hence the hoping for a single UPDATE statement

The loop I'm referring to will be run in each record in T0505, not A0505. Sql will handle the updating process in A0505. So in theory, it will run 215,000 times.

OK, how will that run? What is the alternative to "seek"?

In that case, I've used a combination of DCrake's suggestion with mine. I've amended his code to suit your needs. Double-check before using (and obviously test on your backup copy :))


Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT TSAY, Sum(Nz(Cost,0)) AS SumOfCosts FROM T0505 GROUP BY TSAY ORDER BY TSAY;"

Set rs = CurrentDb.OpenRecordset(strSQL)

Do Until rs.EOF
CurrentDb.Execute "UPDATE A0505 SET WIPAsAt = " & !SumOfCosts & " WHERE A0505.MattID = " & !TSAY & ";"
rs.MoveNext
Loop

rs.Close
Set rs = Nothing


Have a go with that.