multiple statements updating a table with multiple values

Im trying to do this

UPDATE [ICT & Electrical Audit Table] INNER JOIN Software ON [ICT &
Electrical Audit Table].[Internal Reference] = Software.[PC LICENCED FOR]
SET [ICT & Electrical Audit Table].Software = " "
UPDATE [ICT & Electrical Audit Table] INNER JOIN Software ON [ICT &
Electrical Audit Table].[Internal Reference] = Software.[PC LICENCED FOR]
SET [ICT & Electrical Audit Table].Software = [ICT & Electrical Audit
Table].Software & software.[internal reference] & " " & software.[software
title] & " format=" & software.[format] & " " & software.[serial number] &
chr$(13);

ie clear the content of [ICT & Electrical Audit Table].Software before
setting multiple query returns to it, but it dont like it..can anyone help?

Hi,


You issue it as two statements, do you? and have you tried to add
DISTINCTROW (after the keyword UPDATE) ? If you use MS SQL SERVER, instead
of Jet, try to use a WHERE clause, rather than the inner join (since
DISTINCTROW is Jet's proprietary syntax).


UPDATE table1 SET field1=something WHERE table1.OtherField IN (SELECT
table2.fieldName FROM table2)


Hoping it may help,
Vanderghast, Access MVP


"Millbrook School" wrote in
message ...
Im trying to do this

UPDATE [ICT & Electrical Audit Table] INNER JOIN Software ON [ICT &
Electrical Audit Table].[Internal Reference] = Software.[PC LICENCED FOR]
SET [ICT & Electrical Audit Table].Software = " "
UPDATE [ICT & Electrical Audit Table] INNER JOIN Software ON [ICT &
Electrical Audit Table].[Internal Reference] = Software.[PC LICENCED FOR]
SET [ICT & Electrical Audit Table].Software = [ICT & Electrical Audit
Table].Software & software.[internal reference] & " " &
software.[software
title] & " format=" & software.[format] & " " & software.[serial number] &
chr$(13);

ie clear the content of [ICT & Electrical Audit Table].Software before
setting multiple query returns to it, but it dont like it..can anyone
help?

You would need to run TWO separate queries in Access.

In the first one, I would probably be setting the field to = Null instead
of to a single space. Actually, I would probably not even run the first
one, since the second query updates all the fields that you have just
blanked.

UPDATE [ICT & Electrical Audit Table] INNER JOIN Software
ON [ICT & Electrical Audit Table].[Internal Reference] = Software.[PC
LICENCED FOR]
SET [ICT & Electrical Audit Table].Software = " "

I would just use the following query to update the field
UPDATE [ICT & Electrical Audit Table] INNER JOIN Software
ON [ICT & Electrical Audit Table].[Internal Reference] = Software.[PC
LICENCED FOR]
SET [ICT & Electrical Audit Table].Software = software.[internal reference]
& " "
& software.[software title] & " format=" & software.[format] & " " &
software.[serial number] & chr$(13);

What I don't understand is why you are adding anything other than the
software internal reference field. All the other data seems to be in the
related table (Software) and would normally be accessed by linking to that
table.


By the way, adding just Chr(13) to an Access table field will simply display
a small square character in the field. If you are trying to add a line feed
at the end you need two characters a carriage return and a line feed (and in
that order).
& Chr(13) & Chr(10)
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Millbrook School" wrote in
message ...
Im trying to do this

UPDATE [ICT & Electrical Audit Table] INNER JOIN Software ON [ICT &
Electrical Audit Table].[Internal Reference] = Software.[PC LICENCED FOR]
SET [ICT & Electrical Audit Table].Software = " "
UPDATE [ICT & Electrical Audit Table] INNER JOIN Software ON [ICT &
Electrical Audit Table].[Internal Reference] = Software.[PC LICENCED FOR]
SET [ICT & Electrical Audit Table].Software = [ICT & Electrical Audit
Table].Software & software.[internal reference] & " " &
software.[software
title] & " format=" & software.[format] & " " & software.[serial number] &
chr$(13);

ie clear the content of [ICT & Electrical Audit Table].Software before
setting multiple query returns to it, but it dont like it..can anyone
help?