Newbie question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jimbo - 18 Mar 2007 21:23 GMT

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

Reply to this Message

strive4peace - 18 Mar 2007 21:45 GMT

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
[quoted text clipped - 14 lines]
>
> JM

Reply to this Message

Jimbo - 18 Mar 2007 22:06 GMT

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
[quoted text clipped - 32 lines]
>>
>> JM

Reply to this Message

strive4peace - 18 Mar 2007 22:30 GMT

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
>
[quoted text clipped - 44 lines]
>>>
>>> JM

Reply to this Message

Jimbo - 18 Mar 2007 23:39 GMT

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,
>
[quoted text clipped - 62 lines]
>>>>
>>>> JM

Reply to this Message

John W. Vinson - 19 Mar 2007 01:27 GMT

>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]

Reply to this Message

Jimbo - 19 Mar 2007 02:01 GMT

Many Thanks - that seems to have done it !!

>>I really appreciate your help - it seems to be making multiple runs
>>through
[quoted text clipped - 13 lines]
>
> John W. Vinson [MVP]

Reply to this Message