Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Posts
    23

    Unanswered: Subquery inside INSERT INTO Statement

    What I'm trying to do is write an INSERT INTO statement while being able to integrate a subquery in the insert statement. I'm new to using subqueries, but have tried several different ways to do this and haven't been able to find one that worked properly. The purpose of using a subquery in the instance I am is to perform an addition operation on one of the values before it is inserted into the designated table.

    For example, if I have a two tables named:
    Sales_Table with data fields:
    -ProjectID (Primary Key)
    -Sales
    SalesProjects_Table with data fields:
    -ProjectID (Primary Key)
    -ProjectSales

    I may try to perform the following insert:

    INSERT INTO SalesProjects_Table(ProjectID, ProjectSales) VALUES ('10', '15000' + (SELECT Sales FROM Sales_Table WHERE ProjectID = '10')

    Say the result of the SELECT Sales subquery was 20000, the value input into the SalesProjects_Table would ideally be 35000. However, I'm having trouble being able to do this with a subquery. The 15000 ProjectSales figure is coming from a form, but I can't get the subquery integrated into the entire statement to get it to work properly. I'm using Access 97 and I get an MSACCESS.exe error of some sort and Access shuts down. I'm not sure if Access has the capabilities to perform this operation or not, but if there is anyone who would know how to do this, please let me know!

    Thanks much!

    ~LMF

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Subquery inside INSERT INTO Statement

    Originally posted by littlemadfox
    What I'm trying to do is write an INSERT INTO statement while being able to integrate a subquery in the insert statement. I'm new to using subqueries, but have tried several different ways to do this and haven't been able to find one that worked properly. The purpose of using a subquery in the instance I am is to perform an addition operation on one of the values before it is inserted into the designated table.

    For example, if I have a two tables named:
    Sales_Table with data fields:
    -ProjectID (Primary Key)
    -Sales
    SalesProjects_Table with data fields:
    -ProjectID (Primary Key)
    -ProjectSales

    I may try to perform the following insert:

    INSERT INTO SalesProjects_Table(ProjectID, ProjectSales) VALUES ('10', '15000' + (SELECT Sales FROM Sales_Table WHERE ProjectID = '10')

    Say the result of the SELECT Sales subquery was 20000, the value input into the SalesProjects_Table would ideally be 35000. However, I'm having trouble being able to do this with a subquery. The 15000 ProjectSales figure is coming from a form, but I can't get the subquery integrated into the entire statement to get it to work properly. I'm using Access 97 and I get an MSACCESS.exe error of some sort and Access shuts down. I'm not sure if Access has the capabilities to perform this operation or not, but if there is anyone who would know how to do this, please let me know!

    Thanks much!

    ~LMF
    I don't think you can do this ... I tried to myself and no go. THe runtime I get is 3205 ...

  3. #3
    Join Date
    Jul 2002
    Posts
    23
    Thanks for trying at least. Can you think of another way to accomplish a similar goal with this? I do have vba experience.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by littlemadfox
    Thanks for trying at least. Can you think of another way to accomplish a similar goal with this? I do have vba experience.
    Yes. Why not query for the value to be inserted into the other table before performing the insert?

  5. #5
    Join Date
    Jul 2002
    Posts
    23
    Is there a way to save a queried value inside a variable (i.e. as a Double) so that in the INSERT statement I can just insert that variable? All this happens very quickly inside the vba code, so I'd like to do it as efficiently as possible. Let me know your thoughts. Thanks much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •