Update Problem

[LJG]

Hey Guys,

Anyone tell me why this update query will not work?

UPDATE [Software Costs] INNER JOIN tempSoftCost ON [Software Costs].softID =
tempSoftCost.softID SET [Software Costs].Description =
tempSoftCost.Description, [Software Costs].[1 off] = tempSoftCost.[1 off],
[Software Costs].priority = tempSoftCost.priority, [Software Costs].type =
tempSoftCost.type, [Software Costs].code = tempSoftCost.code, [Software
Costs].modified = tempSoftCost.modified;

TIA

Les

 

[George Nicholson]

In your SET clause, try bracketing *ALL* occurances of tmpSoftCost and *ALL*
tmpSoftCost fieldnames, regardless of whether they contain a space or not:
"... = [tmpSoftCost].[Priority]..."

This is just a guess. I know it seems awfully verbose but if you were
constructing this in the query designer, it would insist on those brackets.
It would either insert them itself or prompt you for parameters where they
were "missing". It never seems to work without them, so try giving it what
it wants.

Other than that, I don't see anything wrong with your syntax (I assume all
table & field names are correct & are of the correct type, etc.)

HTH,

 

[John Vinson]

Hey Guys,

Anyone tell me why this update query will not work?

UPDATE [Software Costs] INNER JOIN tempSoftCost ON [Software Costs].softID =
tempSoftCost.softID SET [Software Costs].Description =
tempSoftCost.Description, [Software Costs].[1 off] = tempSoftCost.[1 off],
[Software Costs].priority = tempSoftCost.priority, [Software Costs].type =
tempSoftCost.type, [Software Costs].code = tempSoftCost.code, [Software
Costs].modified = tempSoftCost.modified;

TIA

Les

Possibly because you do not have a unique Index (such as a Primary
Key) on SoftID.


John W. Vinson[MVP]

 

[LJG]

Hi George,

Thanks for that, I add the brackets and it runs the update OK, however, I
have added one more record to table tmpSoftCost but that record is not being
added.

John suggest I add the softID (primary ID) but get the message that this is
not updateable.

Thanks
Les

In your SET clause, try bracketing *ALL* occurances of tmpSoftCost and
*ALL* tmpSoftCost fieldnames, regardless of whether they contain a space
or not:
"... = [tmpSoftCost].[Priority]..."

This is just a guess. I know it seems awfully verbose but if you were
constructing this in the query designer, it would insist on those
brackets. It would either insert them itself or prompt you for parameters
where they were "missing". It never seems to work without them, so try
giving it what it wants.

Other than that, I don't see anything wrong with your syntax (I assume all
table & field names are correct & are of the correct type, etc.)

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Hey Guys,

Anyone tell me why this update query will not work?

UPDATE [Software Costs] INNER JOIN tempSoftCost ON [Software
Costs].softID = tempSoftCost.softID SET [Software Costs].Description =
tempSoftCost.Description, [Software Costs].[1 off] = tempSoftCost.[1
off], [Software Costs].priority = tempSoftCost.priority, [Software
Costs].type = tempSoftCost.type, [Software Costs].code =
tempSoftCost.code, [Software Costs].modified = tempSoftCost.modified;

TIA

Les

 

[George Nicholson]

but that record is not being added.

Update queries update existing records within a table. (which we have done,
right?)
Append queries add new records to a table. (which is an *entirely* different
animal)

You can try using the query wizard to create a "Find Unmatched" query that
will pinpoint the new (i.e., unmatched) record in tmpSoftCost and then
create an append query that will add the results of the unmatched query to
[Software Costs]. If you have an autonumber field in [Software Costs] that
will complicate things.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Hi George,

Thanks for that, I add the brackets and it runs the update OK, however, I
have added one more record to table tmpSoftCost but that record is not
being added.

John suggest I add the softID (primary ID) but get the message that this
is not updateable.

Thanks
Les

In your SET clause, try bracketing *ALL* occurances of tmpSoftCost and
*ALL* tmpSoftCost fieldnames, regardless of whether they contain a space
or not:
"... = [tmpSoftCost].[Priority]..."

This is just a guess. I know it seems awfully verbose but if you were
constructing this in the query designer, it would insist on those
brackets. It would either insert them itself or prompt you for parameters
where they were "missing". It never seems to work without them, so try
giving it what it wants.

Other than that, I don't see anything wrong with your syntax (I assume
all table & field names are correct & are of the correct type, etc.)

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Hey Guys,

Anyone tell me why this update query will not work?

UPDATE [Software Costs] INNER JOIN tempSoftCost ON [Software
Costs].softID = tempSoftCost.softID SET [Software Costs].Description =
tempSoftCost.Description, [Software Costs].[1 off] = tempSoftCost.[1
off], [Software Costs].priority = tempSoftCost.priority, [Software
Costs].type = tempSoftCost.type, [Software Costs].code =
tempSoftCost.code, [Software Costs].modified = tempSoftCost.modified;

TIA

Les

 

[LJG]

Hi George,

Thanks for that, I have 'Manually' altered the table now for the sake of
completing a task.

However, As have a number of tables (12), that at sometime will need to be
updated via an import routine, using tmp tables between the import and the
update.

Are you suggesting that the autonumber is wrong? to create id fields....and
if so why?....more importantly, will I have to re-create these tables again?

TIA
Les

but that record is not being added.

Update queries update existing records within a table. (which we have
done, right?)
Append queries add new records to a table. (which is an *entirely*
different animal)

You can try using the query wizard to create a "Find Unmatched" query that
will pinpoint the new (i.e., unmatched) record in tmpSoftCost and then
create an append query that will add the results of the unmatched query to
[Software Costs]. If you have an autonumber field in [Software Costs] that
will complicate things.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Hi George,

Thanks for that, I add the brackets and it runs the update OK, however, I
have added one more record to table tmpSoftCost but that record is not
being added.

John suggest I add the softID (primary ID) but get the message that this
is not updateable.

Thanks
Les

In your SET clause, try bracketing *ALL* occurances of tmpSoftCost and
*ALL* tmpSoftCost fieldnames, regardless of whether they contain a space
or not:
"... = [tmpSoftCost].[Priority]..."

This is just a guess. I know it seems awfully verbose but if you were
constructing this in the query designer, it would insist on those
brackets. It would either insert them itself or prompt you for
parameters where they were "missing". It never seems to work without
them, so try giving it what it wants.

Other than that, I don't see anything wrong with your syntax (I assume
all table & field names are correct & are of the correct type, etc.)

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Hey Guys,

Anyone tell me why this update query will not work?

UPDATE [Software Costs] INNER JOIN tempSoftCost ON [Software
Costs].softID = tempSoftCost.softID SET [Software Costs].Description =
tempSoftCost.Description, [Software Costs].[1 off] = tempSoftCost.[1
off], [Software Costs].priority = tempSoftCost.priority, [Software
Costs].type = tempSoftCost.type, [Software Costs].code =
tempSoftCost.code, [Software Costs].modified = tempSoftCost.modified;

TIA

Les