Append with Sub query

[Peter]

I have a simple Append query that I use a sub query to determine the value of
one of the fields to be appended. When I attempt to run the query I get an
error, "Reserved Error (-3025); there is no message for this error".

If I remove the sub query and replace it with a literal value the query
works, all fields are text.

INSERT INTO Table1 (Field1, Field2, Field3)
SELECT 'val1' AS [1stfield], 'val2' AS [2ndfield],
(SELECT newvalue FROM Table2 WHERE Table2.testcondition = 'val2') AS
[3rdfield]

The sub query works as a standalone select query without any trouble, note
that Table2.testcondition is the primary key of that table.

If I change it to be 2 queries nested then it works but this is undesirable
since this has to be created and run in code, a sub query is simpler in every
respect, well... if it works that is.

Will Access not allow a sub query in an Append query? I can't say that I've
ever tried to do this before but it never occurred to me that it wouldn't
work and I was surprised when it didn't.

The error message, or lack thereof, doesn't give much away. This is Access
2003 SP3 on Windows XP Pro SP3.

 

[John Spencer MVP]

You may be forced to use the DLookup function, since in theory your subquery
could return multiple records even though using the primary key means YOU (not
the SQL engine) know that it will return at most one record.

You can try several things
-- option 1: use the DLookup function
-- Option 2: change the subquery to an aggregate query so you only return one row
-- Option 3: Write the query to use the alternative syntax for inserting one
record. (Probably the best solution if only one record is being inserted and
no other table is identified in the SELECT Query)

DLookup("NewValue","Table2","TestCondition='val2'") as 3rdField

or

(SELECT Max(newvalue) FROM Table2 WHERE Table2.testcondition = 'val2') AS
[3rdfield]

or

INSERT INTO Table1 (Field1, Field2, Field3)
Values ('val1', 'val2',DLookup("NewValue","Table2","TestCondition='val2'"))

By the way, it is usually best to post the actual SQL that is failing instead
of a generic example.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Peter]

Thanks for the heads up.

The DLookup function does work, I tried your thoughts of making the subquery
an aggregate query, but it still returns the same error, irrespective of
which type of aggregate function I use.

No matter, I will use the DLookup function.

Thanks again for your help.
--
Regards - Peter

You may be forced to use the DLookup function, since in theory your subquery
could return multiple records even though using the primary key means YOU (not
the SQL engine) know that it will return at most one record.

You can try several things
-- option 1: use the DLookup function
-- Option 2: change the subquery to an aggregate query so you only return one row
-- Option 3: Write the query to use the alternative syntax for inserting one
record. (Probably the best solution if only one record is being inserted and
no other table is identified in the SELECT Query)

DLookup("NewValue","Table2","TestCondition='val2'") as 3rdField

or

(SELECT Max(newvalue) FROM Table2 WHERE Table2.testcondition = 'val2') AS
[3rdfield]

or

INSERT INTO Table1 (Field1, Field2, Field3)
Values ('val1', 'val2',DLookup("NewValue","Table2","TestCondition='val2'"))

By the way, it is usually best to post the actual SQL that is failing instead
of a generic example.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a simple Append query that I use a sub query to determine the value of
one of the fields to be appended. When I attempt to run the query I get an
error, "Reserved Error (-3025); there is no message for this error".

If I remove the sub query and replace it with a literal value the query
works, all fields are text.

INSERT INTO Table1 (Field1, Field2, Field3)
SELECT 'val1' AS [1stfield], 'val2' AS [2ndfield],
(SELECT newvalue FROM Table2 WHERE Table2.testcondition = 'val2') AS
[3rdfield]

The sub query works as a standalone select query without any trouble, note
that Table2.testcondition is the primary key of that table.

If I change it to be 2 queries nested then it works but this is undesirable
since this has to be created and run in code, a sub query is simpler in every
respect, well... if it works that is.

Will Access not allow a sub query in an Append query? I can't say that I've
ever tried to do this before but it never occurred to me that it wouldn't
work and I was surprised when it didn't.

The error message, or lack thereof, doesn't give much away. This is Access
2003 SP3 on Windows XP Pro SP3.