Newbie question

[Jimbo]

I am creating my first Access DB - and have succeeded ti creating an update
query which works - except for the part where I try to update a field :"in
place" by 1

The way I have the query set up is

Field - NumParts
Table - Members
Update to: [Members]![NumParts] : «Expr» ([Members]![NumParts] + 1)

I have tried moving/adding/removing Parentheses but I always get a syntax
error

Can you help ?

Many Thanks

JM

 

[strive4peace]

you already have the table source up there and unless there is a
fieldname conflict, you do not need to specify it again

use a dot (.) not a bang (!)

also, in the UpdateTo cell, simply put the new value -->
[Members].[NumParts] + 1



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Jimbo]

Thanks, Crystal

I did as you suggested - members.numparts+1

I did not get a syntax error but I did get a message about updating 291330
rows and I only have 538 records so far

When I checked the results, the field contained 1494, instead on 1

Any ideas ?

Many Thanks again

you already have the table source up there and unless there is a fieldname
conflict, you do not need to specify it again

use a dot (.) not a bang (!)

also, in the UpdateTo cell, simply put the new value -->
[Members].[NumParts] + 1



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I am creating my first Access DB - and have succeeded ti creating an
update query which works - except for the part where I try to update a
field :"in place" by 1

The way I have the query set up is

Field - NumParts
Table - Members
Update to: [Members]![NumParts] : «Expr» ([Members]![NumParts] + 1)

I have tried moving/adding/removing Parentheses but I always get a syntax
error

Can you help ?

Many Thanks

JM

 

[strive4peace]

Hi Jimbo,

what is the data type for NumParts?

what is the SQL for your query?

from the menu --> View, SQL

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks, Crystal

I did as you suggested - members.numparts+1

I did not get a syntax error but I did get a message about updating 291330
rows and I only have 538 records so far

When I checked the results, the field contained 1494, instead on 1

Any ideas ?

Many Thanks again

you already have the table source up there and unless there is a fieldname
conflict, you do not need to specify it again

use a dot (.) not a bang (!)

also, in the UpdateTo cell, simply put the new value -->
[Members].[NumParts] + 1



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I am creating my first Access DB - and have succeeded ti creating an
update query which works - except for the part where I try to update a
field :"in place" by 1

The way I have the query set up is

Field - NumParts
Table - Members
Update to: [Members]![NumParts] : «Expr» ([Members]![NumParts] + 1)

I have tried moving/adding/removing Parentheses but I always get a syntax
error

Can you help ?

Many Thanks

JM

 

[Jimbo]

The datatype is Number - no decimal places

The SQL is:

UPDATE Members, Golfers1 SET Members.Handicap = Golfers1![Hcp Index],
Members.LastPart = Golfers1!Date, Members.NumParts = Members.NumParts+1
WHERE (((Golfers1.Number)=[Members].[GHIN]));

For clarity - the Members table contains a lost of all my members; the
Golfers1 table is a spreadsheet of activity by members

I really appreciate your help - it seems to be making multiple runs through
the "golfers1" file (??)

I am saying - if the GHIN/Golfers numbers are equal, replace the date,
handicap and add 1 to the number of rounds played

Hi Jimbo,

what is the data type for NumParts?

what is the SQL for your query?

from the menu --> View, SQL

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks, Crystal

I did as you suggested - members.numparts+1

I did not get a syntax error but I did get a message about updating
291330 rows and I only have 538 records so far

When I checked the results, the field contained 1494, instead on 1

Any ideas ?

Many Thanks again

you already have the table source up there and unless there is a
fieldname conflict, you do not need to specify it again

use a dot (.) not a bang (!)

also, in the UpdateTo cell, simply put the new value -->
[Members].[NumParts] + 1



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Jimbo wrote:
I am creating my first Access DB - and have succeeded ti creating an
update query which works - except for the part where I try to update a
field :"in place" by 1

The way I have the query set up is

Field - NumParts
Table - Members
Update to: [Members]![NumParts] : «Expr» ([Members]![NumParts] + 1)

I have tried moving/adding/removing Parentheses but I always get a
syntax error

Can you help ?

Many Thanks

JM

 

[John W. Vinson]

I really appreciate your help - it seems to be making multiple runs through
the "golfers1" file (??)

I am saying - if the GHIN/Golfers numbers are equal, replace the date,
handicap and add 1 to the number of rounds played

Use a JOIN rather than a WHERE clause:

UPDATE Members
INNER JOIN Golfers1
ON Golfers1.Number=[Members].[GHIN]
SET Members.Handicap = Golfers1.[Hcp Index],
Members.LastPart = Golfers1.Date,
Members.NumParts = Members.NumParts+1;


John W. Vinson [MVP]

 

[Jimbo]

Many Thanks - that seems to have done it !!

I really appreciate your help - it seems to be making multiple runs
through
the "golfers1" file (??)

I am saying - if the GHIN/Golfers numbers are equal, replace the date,
handicap and add 1 to the number of rounds played

Use a JOIN rather than a WHERE clause:

UPDATE Members
INNER JOIN Golfers1
ON Golfers1.Number=[Members].[GHIN]
SET Members.Handicap = Golfers1.[Hcp Index],
Members.LastPart = Golfers1.Date,
Members.NumParts = Members.NumParts+1;


John W. Vinson [MVP]