By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,076 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

problem with updating database

P: n/a
in access i have two tables (tblplayer, tblpoints)
in tblplayer i have field totalpoint (number)
in tlbpoints i have field pointgame (number)
the two files are related via idplayer
now i made a query "qrybegan" and in that query i calculate
a field sumpointgame which is the sum of all the field pointgame (option sum
in the query)
i have put in the query also the field totalpoint from the tblplayer

now i want to put the sumpointgame in the field totalpoint of the tblplayer
(i need this for a certain reason)

i made this

dim rsartikelskl as recordset
set huidigedB= currentdB
set rsartikelskl=huidigedB.openrecordset("qrybegan, dbopendynaset)
rsartikelskl.Movefirst
Do until rsartikelskl.EOF()
rsartikelskl.edit
rsartikelskl!totalpoint=rsartikelskl!sumpointgame
update
rsartikelskl.MoveNext
loop
rsartikelskl.Close

Now i thaught that the calculated field sumpointgame was put in the
totalpoint field of the tblplayer
it does nothing................. the field sumpointgame stays at 0

is there a specialist who can tell me why this dont work???
is there someone who has a routine which has the same result (maybe in
sql??)

ths for reply

greetings

--

Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a

Looking at your code, there are a couple of errors: it won't run as it
is. It should be:

dim rsartikelskl as recordset

set huidigedB= currentdB

set rsartikelskl=huidigedB.openrecordset("qrybegan", dbopendynaset)

rsartikelskl.Movefirst

Do until rsartikelskl.EOF()

rsartikelskl.edit

rsartikelskl!totalpoint=rsartikelskl!sumpointgame

rsartikelskl.update

rsartikelskl.MoveNext

loop

rsartikelskl.Close

You should also add these lines to release the object variables and
reclaim memory space:

Set rsartikelskl = Nothing

Set huidigedB = Nothing

This may not be the cause of your problem, but it's a start. Also, are
you sure that your query is an updateable recordset? Try entering data
manually into your query, because, if you can't, your code won't be able
to either.

The SQL to do the same thing would be "UPDATE QryBegan SET TotalPoint =
SumPointGame".

Hope this helps.
--
Andy Briggs
Elmhurst Solutions Limited
http://www.elmhurstsolutions.com
Posted via http://dbforums.com
Nov 12 '05 #2

P: n/a

"andybriggs" <me*********@dbforums.com> schreef in bericht
news:35****************@dbforums.com...

Looking at your code, there are a couple of errors: it won't run as it
is. It should be:

dim rsartikelskl as recordset

set huidigedB= currentdB

set rsartikelskl=huidigedB.openrecordset("qrybegan", dbopendynaset)

rsartikelskl.Movefirst

Do until rsartikelskl.EOF()

rsartikelskl.edit

rsartikelskl!totalpoint=rsartikelskl!sumpointgame

rsartikelskl.update

rsartikelskl.MoveNext

loop

rsartikelskl.Close

You should also add these lines to release the object variables and
reclaim memory space:

Set rsartikelskl = Nothing

Set huidigedB = Nothing

This may not be the cause of your problem, but it's a start. Also, are
you sure that your query is an updateable recordset? Try entering data
manually into your query, because, if you can't, your code won't be able
to either.

The SQL to do the same thing would be "UPDATE QryBegan SET TotalPoint =
SumPointGame".

Hope this helps.
--
Andy Briggs
Elmhurst Solutions Limited
http://www.elmhurstsolutions.com
Posted via http://dbforums.com

thx for reply
as a matter of fact results of the the query are not updatable.....again
something i've leard

but now....other solutions
the question is now......
a have a tabel and i have to make a sum of a field
the result of that field must be put in a field of another table

who can give me some good advice????

thx for reply
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 1/09/2003
Nov 12 '05 #3

P: n/a
Sounds like good advice would be a session with a good book on relational
database design. Storing something that can be calculated when it is needed,
if that is what you are doing, is redundant, and often leads to problems
later.

But, to perform a sum on a particular field in a table in code, with or
without applying selection criteria, you can use the DSum domain aggregate
function. To do it in a query, create a SELECT query in the Query Builder
design view, then click View | Totals and choose the appropriate items for
each field, such as "Sum", "Average", "WHERE", and "GROUP BY".

Larry Linson
Microsoft Access MVP

"Foxster" <er***********@skynet.be> wrote in message
news:3f*********************@reader1.news.skynet.b e...

"andybriggs" <me*********@dbforums.com> schreef in bericht
news:35****************@dbforums.com...

Looking at your code, there are a couple of errors: it won't run as it
is. It should be:

dim rsartikelskl as recordset

set huidigedB= currentdB

set rsartikelskl=huidigedB.openrecordset("qrybegan", dbopendynaset)

rsartikelskl.Movefirst

Do until rsartikelskl.EOF()

rsartikelskl.edit

rsartikelskl!totalpoint=rsartikelskl!sumpointgame

rsartikelskl.update

rsartikelskl.MoveNext

loop

rsartikelskl.Close

You should also add these lines to release the object variables and
reclaim memory space:

Set rsartikelskl = Nothing

Set huidigedB = Nothing

This may not be the cause of your problem, but it's a start. Also, are
you sure that your query is an updateable recordset? Try entering data
manually into your query, because, if you can't, your code won't be able
to either.

The SQL to do the same thing would be "UPDATE QryBegan SET TotalPoint =
SumPointGame".

Hope this helps.
--
Andy Briggs
Elmhurst Solutions Limited
http://www.elmhurstsolutions.com
Posted via http://dbforums.com

thx for reply
as a matter of fact results of the the query are not updatable.....again
something i've leard

but now....other solutions
the question is now......
a have a tabel and i have to make a sum of a field
the result of that field must be put in a field of another table

who can give me some good advice????

thx for reply
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 1/09/2003

Nov 12 '05 #4

P: n/a

"Larry Linson" <bo*****@localhost.not> schreef in bericht
news:hz*******************@nwrddc01.gnilink.net...
Sounds like good advice would be a session with a good book on relational
database design. Storing something that can be calculated when it is needed, if that is what you are doing, is redundant, and often leads to problems
later.

But, to perform a sum on a particular field in a table in code, with or
without applying selection criteria, you can use the DSum domain aggregate
function. To do it in a query, create a SELECT query in the Query Builder
design view, then click View | Totals and choose the appropriate items for
each field, such as "Sum", "Average", "WHERE", and "GROUP BY".

Larry Linson
Microsoft Access MVP

"Foxster" <er***********@skynet.be> wrote in message
news:3f*********************@reader1.news.skynet.b e...

"andybriggs" <me*********@dbforums.com> schreef in bericht
news:35****************@dbforums.com...

Looking at your code, there are a couple of errors: it won't run as it
is. It should be:

dim rsartikelskl as recordset

set huidigedB= currentdB

set rsartikelskl=huidigedB.openrecordset("qrybegan", dbopendynaset)

rsartikelskl.Movefirst

Do until rsartikelskl.EOF()

rsartikelskl.edit

rsartikelskl!totalpoint=rsartikelskl!sumpointgame

rsartikelskl.update

rsartikelskl.MoveNext

loop

rsartikelskl.Close

You should also add these lines to release the object variables and
reclaim memory space:

Set rsartikelskl = Nothing

Set huidigedB = Nothing

This may not be the cause of your problem, but it's a start. Also, are
you sure that your query is an updateable recordset? Try entering data
manually into your query, because, if you can't, your code won't be able to either.

The SQL to do the same thing would be "UPDATE QryBegan SET TotalPoint = SumPointGame".

Hope this helps.
--
Andy Briggs
Elmhurst Solutions Limited
http://www.elmhurstsolutions.com
Posted via http://dbforums.com

thx for reply
as a matter of fact results of the the query are not updatable.....again
something i've leard

but now....other solutions
the question is now......
a have a tabel and i have to make a sum of a field
the result of that field must be put in a field of another table

who can give me some good advice????

thx for reply
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 1/09/2003

thx for reply
i agree what you are saying about redundant matter but i have to make a
report with the first table and sort them first according to fields in that
table and then those who are not sorted by the fields in that table must be
sorted by the total of points in that other table.......
so i need the totals of those points for a certain amount of players in that
table....

so i try to add some fields in that first table with the results i need and
then i can make a report and sorting them easily like i want them to be
sorted.....

i hope you understand this.....

and i agree, i am a self study user of ms access but

now my question: how can you create a report with condition of sorting not
with fields of other tables but with results of other tablefields....

thx for reply
greetings
Nov 12 '05 #5

P: n/a
"Foxster" wrote
i agree what you are saying about
redundant matter but i have to make
a report with the first table and sort
them first according to fields in that
table and then those who are not
sorted by the fields in that table must
be sorted by the total of points in that
other table.......
so i need the totals of those points for
a certain amount of players in that
table....
i hope you understand this.....

and i agree, i am a self study user of ms access but

now my question: how can you create a report with condition of sorting not
with fields of other tables but with results of other tablefields....


The order of the Records in the Record Source of a Report is ignored and
Report sorting is controlled by the Report's Sorting and Grouping
properties. That means that every single Record must have some fields which
are common, and used for sorting.

I think perhaps you might consider whether you could create separate
Reports, each with the appropriate ordering, and put each of them in a
Subreport Control on the actual Report. If you are careful, it will not be
obvious that the Report is actually made up of two Reports. Use a Query as
the RecordSource to select just the Records that will be sorted in a
particular way for each Report.

There are, I am convinced, occasions when creating temporary tables is
unavoidable. But, I've been using Access just about every day since January
1993, and I have never been forced to do so in the business database
applications I normally create.

I have created temporary tables, but only to demonstrate how you can do so
in a temporary database and avoid the database bloat and need for frequent
compacting that inevitably results when you create temporary tables within
your own database. Now, MVP Tony Toews has code illustrating this approach
on his website, http://www.granite.ab.ca/accsmstr.htm.

Larry Linson
Microsoft Access MVP
Nov 12 '05 #6

P: n/a

"Larry Linson" <bo*****@localhost.not> schreef in bericht
news:NL*******************@nwrddc01.gnilink.net...
"Foxster" wrote
> i agree what you are saying about
> redundant matter but i have to make
> a report with the first table and sort
> them first according to fields in that
> table and then those who are not
> sorted by the fields in that table must
> be sorted by the total of points in that
> other table.......
> so i need the totals of those points for
> a certain amount of players in that
> table....
i hope you understand this.....

and i agree, i am a self study user of ms access but

now my question: how can you create a report with condition of sorting not with fields of other tables but with results of other tablefields....


The order of the Records in the Record Source of a Report is ignored and
Report sorting is controlled by the Report's Sorting and Grouping
properties. That means that every single Record must have some fields

which are common, and used for sorting.

I think perhaps you might consider whether you could create separate
Reports, each with the appropriate ordering, and put each of them in a
Subreport Control on the actual Report. If you are careful, it will not be
obvious that the Report is actually made up of two Reports. Use a Query as
the RecordSource to select just the Records that will be sorted in a
particular way for each Report.

There are, I am convinced, occasions when creating temporary tables is
unavoidable. But, I've been using Access just about every day since January 1993, and I have never been forced to do so in the business database
applications I normally create.

I have created temporary tables, but only to demonstrate how you can do so
in a temporary database and avoid the database bloat and need for frequent
compacting that inevitably results when you create temporary tables within
your own database. Now, MVP Tony Toews has code illustrating this approach
on his website, http://www.granite.ab.ca/accsmstr.htm.

Larry Linson
Microsoft Access MVP

hi there
you were right
today i resaw the report i have created and i have create a new query within
the fields i need and created calculated fields wtih the results from the
other database

with that query and the report related with that query i have managed to
sort in a way i want

so no putting results in other fields as you told

thx for the splendid help you gave
greetings
Nov 12 '05 #7

P: n/a
I'm glad it worked for you and glad I could be of help.

Larry

"Foxster" <er***********@skynet.be> wrote in message
news:3f**********************@reader2.news.skynet. be...

"Larry Linson" <bo*****@localhost.not> schreef in bericht
news:NL*******************@nwrddc01.gnilink.net...
"Foxster" wrote
> i agree what you are saying about
> redundant matter but i have to make
> a report with the first table and sort
> them first according to fields in that
> table and then those who are not
> sorted by the fields in that table must
> be sorted by the total of points in that
> other table.......
> so i need the totals of those points for
> a certain amount of players in that
> table....
i hope you understand this.....

and i agree, i am a self study user of ms access but

now my question: how can you create a report with condition of sorting not with fields of other tables but with results of other tablefields....


The order of the Records in the Record Source of a Report is ignored and
Report sorting is controlled by the Report's Sorting and Grouping
properties. That means that every single Record must have some fields

which
are common, and used for sorting.

I think perhaps you might consider whether you could create separate
Reports, each with the appropriate ordering, and put each of them in a
Subreport Control on the actual Report. If you are careful, it will not be obvious that the Report is actually made up of two Reports. Use a Query as the RecordSource to select just the Records that will be sorted in a
particular way for each Report.

There are, I am convinced, occasions when creating temporary tables is
unavoidable. But, I've been using Access just about every day since

January
1993, and I have never been forced to do so in the business database
applications I normally create.

I have created temporary tables, but only to demonstrate how you can do so in a temporary database and avoid the database bloat and need for frequent compacting that inevitably results when you create temporary tables within your own database. Now, MVP Tony Toews has code illustrating this approach on his website, http://www.granite.ab.ca/accsmstr.htm.

Larry Linson
Microsoft Access MVP

hi there
you were right
today i resaw the report i have created and i have create a new query

within the fields i need and created calculated fields wtih the results from the
other database

with that query and the report related with that query i have managed to
sort in a way i want

so no putting results in other fields as you told

thx for the splendid help you gave
greetings

Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.