PEB 1,418
Expert 1GB
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("MySumField","Test","")>0,Dsum("MySumFiel d","Test",""),1) FROM Rows;
And the wanted result isn't obtained..
Well staff like begin trans and commit trans are turned off hein..
10 1705 PEB 1,418
Expert 1GB
So why Dsum("MySumField","Test","") doesn't function properly?
PEB 1,418
Expert 1GB
And also in table "Rows" I have 1000 like this record :
Field1
Test
Test
Test
......
990 times
........
Test
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("MySumField","Test","")>0,Dsum("MySumFiel d","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("MySumField","Test","")>0,Dsum("MySumFiel d","Test","") + Field2,1) FROM Rows;
PEB 1,418
Expert 1GB
Thanks a lot!
However, Mary test it as you gave it to me!
PEB 1,418
Expert 1GB
The query that Im currently using is:
INSERT INTO Results ( Field1, MySumField )
SELECT Rows.Field1, nz(DSum("MySumField","Results",""))+1+Val([Field1]) AS Expr1
FROM [Rows];
And the result isn't achieved!
You have to put in an alternative value using nz. Try ...
INSERT INTO Results ( Field1, MySumField )
SELECT Rows.Field1, nz(DSum("MySumField","Results",""),0)+1+Val([Field1]) AS Expr1
FROM [Rows];
PEB 1,418
Expert 1GB
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???
You have to put in an alternative value using nz. Try ...
INSERT INTO Results ( Field1, MySumField )
SELECT Rows.Field1, nz(DSum("MySumField","Results",""),0)+1+Val([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("MySumField","Results"),0)+1+Val([Field1]) AS Expr1
FROM [Rows];
NeoPa 32,556
Expert Mod 16PB
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 1,418
Expert 1GB
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: josh dismukes |
last post by:
/// here is the code i'm getting a parse error on the last line of the
code which
/// is </html> any help will be much appreciated.
<?php
session_start ();
|
by: Steve |
last post by:
I just spent waaaaaaaaaaaayy too much time trying to track down an error that was incorrectly reported just now, and I would like to see if someone
can explain to me why it was reported that way.
...
|
by: Bryan Tang |
last post by:
I built an ASP to search the content in index server. It is ok if search in
English. Whenever I search in Chinese, I will got the following error
message,
CreateRecordset ?u?~ '80004005'
...
|
by: jj |
last post by:
I've got a form button that fires off 3 queries but if the first query
returns an error, I don't want the other two queries to happen.
Example: first query runs an insert from a linked table but...
|
by: coosa |
last post by:
Hi,
I was installing SQL Server on my machine and during installation my PC
freezed. It happens frequently on my machine. So i tried after
restarting to install it again and since then i always...
|
by: Lori2836 via AccessMonster.com |
last post by:
I have a query where I am grouping by Fiscal Month, Summing # of Quotes,
Summing # of Days.......and created an expression.........Avg Days: /.
And I get the following error...
|
by: dancole42 |
last post by:
So I have an invoicing database based on two main forms: Orders and
OrderLines. Orders has fields like:
OrderID
BillingMethod
OrderDate
CreditCard
CCExp
OrdSubTotal
ShippingCharge
|
by: NewlytoSQL |
last post by:
Hi all, im fairly new to SQL and im stuck half way through a query,
im using DB2 here is what im tryng to do.
i have a query that brings back an item number , shelf req, sum of all orders columns,...
|
by: md9108 |
last post by:
I created, using some borrowed code, an asp search page for our intranet. I'm using frontpage 2003. When I publish I get that complation error on different lines at different times. They all seem...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |