INSERT a new row with a subquery help

• Mario Garcia-415929

  SSC-Addicted

  Points: 464

  More actions

  August 31, 2009 at 12:29 pm

  #100667

  My MS Access is a bit rusty. I am trying to add a new row into an existing table but unable to.

  This is done using MS Access SQL. I cannot use a module or a macro to perform this task.

  Here is what the table looks like:

  ID coa desc quant

  2 300000 One 1

  3 311111 Two 1

  4 322222 Three 1

  5 333333 Four 1

  I want it to look like:

  ID coa desc quant

  2 300000 One 1

  3 311111 Two 1

  4 322222 Three 1

  5 333333 Four 1

  6 399999 Total 4

  In SQL 2005, I can perform the following with no issues.

  insert into qsum (coa, coadesc, qty)

  select '399999', 'Total', (SELECT sum(qty) from qsum where coa like ('3%'))

  In MS Access 2005, I have

  INSERT INTO qsum ( coa, quant )

  SELECT 399999 AS coa, (SELECT Sum(qsum.quant) AS quant FROM qsum HAVING (((qsum.coa) Like ('3*')))) AS quant;

  and I get the error:

  Reserved error (-3025); there is no message for this error.

  How can I add the total row to an existing table in Access while only using SQL?

  Thanks

• JTSash

  SSCrazy

  Points: 2341

  More actions

  September 2, 2009 at 7:59 am

  #1048154

  Try this:

  INSERT INTO qsum ( coa, desc, quant )

  SELECT '399999' AS coa, 'Total' AS desc, Sum(qsum.quant) AS quant

  FROM qsum

  WHERE (qsum.coa Like '3*');