Problem with Macro RunSql Statement

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • jl2886
    New Member
    • Mar 2007
    • 83

    #1

    Problem with Macro RunSql Statement

    I would like a all records from [Master Log] (a table) where [Master Log].[Disposition]="Rejected" and input those records into the [Rejected] (a table). Both tables are exactly the same as far as structure. I have created a macro with one RunSql Statement.
    [Code]
    INSERT INTO [Rejected] SELECT ALL FROM [Master Log] WHERE [Master Log].[Disposition]="Rejected"
    [Code/]
    It says there is a syntax error, do you have any ideas?
    Also, could I run this from a form, or do I have to create a macro in order to do it.
    Actually, I want to then delete the "rejected" records from the master log. Would I create another runsql statement? How might it look?
  • LacrosseB0ss
    New Member
    • Oct 2006
    • 112

    #2
    With the insert statement, you need a VALUES clause to specify what will go in. And "ALL" will look for a field name. To select every column from a matching row use '*'. From what I'm seeing, everything is ok except those. Your new query will look something like this:

    Code:
    INSERT INTO [Rejected] 
    VALUES (SELECT * FROM [Master Log] WHERE [Master Log].[Disposition]="Rejected")
    Now, your 2nd question (remove records in Rejected), you will need another query. The best query to use for this would look as follows:

    Code:
    DELETE 
    FROM Master Log 
    WHERE "primaryKey" NOT IN (SELECT "primaryKey" FROM Rejected)
    Hope this works/helps
    - LB

    Comment

    • jl2886
      New Member
      • Mar 2007
      • 83

      #3
      Thank you, but I get a syntax error when i place the statement in the macro run sql action?

      Originally posted by LacrosseBoss
      With the insert statement, you need a VALUES clause to specify what will go in. And "ALL" will look for a field name. To select every column from a matching row use '*'. From what I'm seeing, everything is ok except those. Your new query will look something like this:

      Code:
      INSERT INTO [Rejected] 
      VALUES (SELECT * FROM [Master Log] WHERE [Master Log].[Disposition]="Rejected")
      Now, your 2nd question (remove records in Rejected), you will need another query. The best query to use for this would look as follows:

      Code:
      DELETE 
      FROM Master Log 
      WHERE "primaryKey" NOT IN (SELECT "primaryKey" FROM Rejected)
      Hope this works/helps
      - LB

      Comment

      • LacrosseB0ss
        New Member
        • Oct 2006
        • 112

        #4
        The problem is with your table name. Master (space) Log is confusing to the query. It assumes Master is the table name and Log is another command. However, there is no command named Log.

        I popped the query into an access db and changed the table name to Master_Log and everything worked fine.

        Hope this helps
        - LB

        Comment

        Working...