INSERT a single row combining data from 2 rows

[Gwyn]

Hi, I'm trying to create a single row in a table using
data extracted from 2 rows of another table... but I don't
seem to be able to do it.

The best way to describe the problem is to write the SQL
that I would like to be able to use!

INSERT INTO Actions
(DateTime,Object1,Object2)
#03/12/2003#,
(SELECT ObjName FROM Objects WHERE ObjID='CI:1'),
(SELECT ObjName FROM Objects WHERE ObjID='CI:13')

but I can't do this... It seems I can only use a single
SELECT for populating the columns of the new row.

Please bear in mind that there may be a million rows in
the Objects table so the solution must take this into
account.

I also want to achieve this in a single SQL statement. I
know that I could run an INSERT and then an UPDATE.

Help!!! Any ideas?

 

[Michel Walsh]

Hi,

.... untested, but should work:

INSERT INTO Actions
(DateTime,Object1,Object2)
SELECT #03/12/2003#,
ObjName,
(SELECT ObjName FROM Objects WHERE ObjID='CI:13')
FROM Objects
WHERE ObjID='CI:1'



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

It does, and it makes absolute sense.... nesting the sub
queries. Very clever, thank you!