problem with updating database

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

--

re: problem with updating database

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

re: problem with updating database

"andybriggs" <member38555@dbforums.com> schreef in bericht
news:3506540.1066754627@dbforums.com...[color=blue]
>
> 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[/color]


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

re: problem with updating database

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" <erik.reynaert@skynet.be> wrote in message
news:3f96a2ba$0$306$ba620e4c@reader1.news.skynet.b e...[color=blue]
>
> "andybriggs" <member38555@dbforums.com> schreef in bericht
> news:3506540.1066754627@dbforums.com...[color=green]
> >
> > 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[/color]
>
>
> 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
>
>[/color]

re: problem with updating database

"Larry Linson" <bouncer@localhost.not> schreef in bericht
news:hzGlb.28735$Ee6.11822@nwrddc01.gnilink.net...[color=blue]
> 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[/color]
needed,[color=blue]
> 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" <erik.reynaert@skynet.be> wrote in message
> news:3f96a2ba$0$306$ba620e4c@reader1.news.skynet.b e...[color=green]
> >
> > "andybriggs" <member38555@dbforums.com> schreef in bericht
> > news:3506540.1066754627@dbforums.com...[color=darkred]
> > >
> > > 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[/color][/color][/color]
able[color=blue][color=green][color=darkred]
> > > to either.
> > >
> > >
> > >
> > > The SQL to do the same thing would be "UPDATE QryBegan SET TotalPoint[/color][/color][/color]
=[color=blue][color=green][color=darkred]
> > > SumPointGame".
> > >
> > >
> > >
> > > Hope this helps.
> > >
> > >
> > > --
> > > Andy Briggs
> > > Elmhurst Solutions Limited
> > > http://www.elmhurstsolutions.com
> > >
> > >
> > > Posted via http://dbforums.com[/color]
> >
> >
> > 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
> >
> >[/color][/color]
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

re: problem with updating database

"Foxster" wrote[color=blue]
> 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....[/color]

[color=blue]
> 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....[/color]

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

re: problem with updating database

"Larry Linson" <bouncer@localhost.not> schreef in bericht
news:NL0mb.37839$Ee6.16803@nwrddc01.gnilink.net...[color=blue]
> "Foxster" wrote[color=green]
> > 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....[/color]
>
>[color=green]
> > 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[/color][/color]
not[color=blue][color=green]
> > with fields of other tables but with results of other tablefields....[/color]
>
> 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[/color]
which[color=blue]
> 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[/color]
January[color=blue]
> 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
>[/color]
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

re: problem with updating database

I'm glad it worked for you and glad I could be of help.

Larry

"Foxster" <erik.reynaert@skynet.be> wrote in message
news:3f9982d8$0$3652$ba620e4c@reader2.news.skynet. be...[color=blue]
>
> "Larry Linson" <bouncer@localhost.not> schreef in bericht
> news:NL0mb.37839$Ee6.16803@nwrddc01.gnilink.net...[color=green]
> > "Foxster" wrote[color=darkred]
> > > 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....[/color]
> >
> >[color=darkred]
> > > 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[/color][/color]
> not[color=green][color=darkred]
> > > with fields of other tables but with results of other tablefields....[/color]
> >
> > 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[/color]
> which[color=green]
> > 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[/color][/color]
be[color=blue][color=green]
> > obvious that the Report is actually made up of two Reports. Use a Query[/color][/color]
as[color=blue][color=green]
> > 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[/color]
> January[color=green]
> > 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[/color][/color]
so[color=blue][color=green]
> > in a temporary database and avoid the database bloat and need for[/color][/color]
frequent[color=blue][color=green]
> > compacting that inevitably results when you create temporary tables[/color][/color]
within[color=blue][color=green]
> > your own database. Now, MVP Tony Toews has code illustrating this[/color][/color]
approach[color=blue][color=green]
> > on his website, http://www.granite.ab.ca/accsmstr.htm.
> >
> > Larry Linson
> > Microsoft Access MVP
> >[/color]
> hi there
> you were right
> today i resaw the report i have created and i have create a new query[/color]
within[color=blue]
> 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
>
>[/color]