If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update Problem
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 |
#2
|
|||
|
|||
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. "LJG" wrote in message ... 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 |
#3
|
|||
|
|||
On Wed, 13 Apr 2005 21:05:40 +0100, "LJG"
wrote: 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] |
#4
|
|||
|
|||
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 "George Nicholson" wrote in message ... 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. "LJG" wrote in message ... 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 |
#5
|
|||
|
|||
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. "LJG" wrote in message ... 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 "George Nicholson" wrote in message ... 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. "LJG" wrote in message ... 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 |
#6
|
|||
|
|||
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 "George Nicholson" wrote in message ... 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. "LJG" wrote in message ... 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 "George Nicholson" wrote in message ... 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. "LJG" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Cumulative Update for Outlook Express (KB887797) | Bob | Outlook Express | 4 | January 31st, 2005 10:55 AM |
Office XP Update Problem | Bill | General Discussions | 0 | December 1st, 2004 06:11 PM |
Multi-Table Update Query or Lookup Problem | SowBelly | Running & Setting Up Queries | 2 | September 10th, 2004 11:20 PM |
Table update problem | Dave | Running & Setting Up Queries | 2 | June 29th, 2004 03:00 AM |
Auto - Update of text boxes with page numbers problem | Don | Visio | 0 | April 27th, 2004 05:15 PM |