SQL - Adding a Column to Database Table with the Date of the SQL Script Run

Question

I come from an R programming background but now need to learn SQL for automation of CSV's at a weekly interval. I am currently working in Microsoft Access 2010 in SQL mode.

I wish to take an extract of an existing table and apply a filter (as shown below), then add a new column to it titled "Report_Date" that shows the date the script was run on.

The code I have so far is as follows:

SELECT Activity_Report.* INTO [Weekly Report]
FROM Activity_Report
WHERE ((([Activity_Report].[on_date])<DateAdd('m',3,Now()) 
And ([Activity_Report].[on_date])>DateAdd('m',-1,Now())))
ALTER TABLE Weekly_Report
ADD Report DATE;

This worked fine up until the ALTER TABLE function and the filtering was successful. I now get the following error when I run the SQL script above:

Syntax error (missing operator) in query expression.

I am not familiar with this error so any help on where I went wrong with my code would be much appreciated. Thanks

Answer 1

Assuming you are aware of the differences between an insert into and a select into, you don't need your alter table statement at all.

You can simply add the current date and time as a part of your select:

SELECT Activity_Report.*
      ,Now() AS Report
INTO [Weekly Report]
FROM Activity_Report
WHERE [Activity_Report].[on_date] < DateAdd('m',3,Now())
    AND [Activity_Report].[on_date] > DateAdd('m',-1,Now())

(I also got rid of a lot of unnecessary parentheses that Access is rather fond of...)

Answer 2

You can't run two commands in one query in Access.

First run the SELECT INTO query.

Then run the DDL query.