Error summing in action query...

[PEB]

When I try to add by action query the sum of the previous values in the recordset - the sum isn't correct

I want table like this named Test:

Field1, MySumField

Test, 1
Test, 2
Test, 3
Test, 6
Test, 12

I've other table Rows like this...

Field1
Test, 1

And using those tables i'm using this SQL:

INSERT INTO Test (Field1, MySumField)
SELECT Field1, IIF(Dsum("MySum Field","Test"," ")>0,Dsum("MySu mField","Test", ""),1) FROM Rows;

And the wanted result isn't obtained..

Well staff like begin trans and commit trans are turned off hein..

[PEB]

So why Dsum("MySumFiel d","Test","" ) doesn't function properly?

[PEB]

And also in table "Rows" I have 1000 like this record :
Field1
Test
Test
Test
......
990 times
........

Test

[MMcCarthy]

When I try to add by action query the sum of the previous values in the recordset - the sum isn't correct

I want table like this named Test:

Field1, MySumField

Test, 1
Test, 2
Test, 3
Test, 6
Test, 12

I've other table Rows like this...

Field1
Test, 1

And using those tables i'm using this SQL:

INSERT INTO Test (Field1, MySumField)
SELECT Field1, IIF(Dsum("MySum Field","Test"," ")>0,Dsum("MySu mField","Test", ""),1) FROM Rows;

And the wanted result isn't obtained..

Well staff like begin trans and commit trans are turned off hein..

You don't give the fieldName of the second field so I'm calling it Field2

Field1
Test, 1

You haven't incremented the MySumField by the current Field2 Value.

INSERT INTO Test (Field1, MySumField)
SELECT Field1, IIF(Dsum("MySum Field","Test"," ")>0,Dsum("MySu mField","Test", "") + Field2,1) FROM Rows;

[PEB]

Thanks a lot!

However, Mary test it as you gave it to me!

[PEB]

The query that Im currently using is:

INSERT INTO Results ( Field1, MySumField )
SELECT Rows.Field1, nz(DSum("MySumF ield","Results" ,""))+1+Val([Field1]) AS Expr1
FROM [Rows];

And the result isn't achieved!

[MMcCarthy]

You have to put in an alternative value using nz. Try ...

INSERT INTO Results ( Field1, MySumField )
SELECT Rows.Field1, nz(DSum("MySumF ield","Results" ,""),0)+1+Va l([Field1]) AS Expr1
FROM [Rows];

[PEB]

the result is:

Field1 MySumField
Test 1
Test 1
Test 1
Test 1
Test 1
Test 1
Test 1
Test 1
Test 1
Test 1
Test 1
Test 1
Test 1

;(

In fact i've performed this with my proper function but can't understand why dsum fails???

[MMcCarthy]

You have to put in an alternative value using nz. Try ...

INSERT INTO Results ( Field1, MySumField )
SELECT Rows.Field1, nz(DSum("MySumF ield","Results" ,""),0)+1+Va l([Field1]) AS Expr1
FROM [Rows];

Would this work. I'm not sure what the value in MySumField is currently.

INSERT INTO Results ( Field1, MySumField )
SELECT Rows.Field1, nz(DMax("MySumF ield","Results" ),0)+1+Val([Field1]) AS Expr1
FROM [Rows];

[NeoPa]

DSum() will ALWAYS work on the recordset as it was (still is) before the data is appended.
Even if you don't explicitly say that you want to manage it as a transaction, Access will anyway.
I set up the following code and produced the results shown.

INSERT INTO Test ( Field1, MySumField )
SELECT Rows.Field1, Nz([subTest].[MySumField],1) AS MySumField
FROM (SELECT Sum([Test].[MySumField]) As MySumField
FROM Test) AS subTest, [Rows];

Results (in Test table) :
Field1	MySumField
Test	1
Test	1
Test	2
Test	4
Test	8
Test	16

These records were all created by the append query above.
The data in the Rows table was simply 1 record where Field1 = 'Test'.
No other fields were used from table 'Rows'.

[PEB]

Yeah NeoPa,

I've done nearly the same thing but instaed using SubQuery i've used a function.

But using a subquery seems to be more efficient because when you use a function using the ordinary append method the function doesn't work correctly because there is Begin trans and commit trans that aren't finished when the sum on the field is done.

So if you ever use a function only run the query from

mydb.execute

without begin trans and commit trans...

It's strange that the subquery obviously uses the data from begin trans and commit trans...

I'll try it monday...

It's very interesting how a subquery uses this temporary data...

Thanks Mary, NeoPa