Importing Information in Table Through SQL

K

Kgwill85

Okay I'm trying to get a table to load into another one through a
series of queries. I'm am using SQL statements to do this. The name
of the table that I'm trying to get information into is called
Detailed_Forecast. The name of the queries that I wrote are
xlsCalendar and xlsCalendar2 (these came from a table named 1 -
RESOURCE FORECAST).

These queries are year based. The Detailed_Forecast displays a 3 year
period horizontally in it's table. The RESOURCE FORECAST table
displays the year veritically. Making the RESOURCE FORECAST table
roll into the Detailed_Forecast table is the goal here. Here's what
I've tried through SQL so far, I'll appreciate any help.

INSERT INTO Detailed_Forecast ( Chiaramonte_DR, [Chiaramonte_DR_+_1],
Res_GID, Name, Service_ID, Serv_Rate, Proj_ID, Common_Project_Name,
Subproject_ID, Sub_Project_Name, Year1, Dec1, Jan1, Feb1, Mar1, Apr1,
May1, Jun1, Jul1, Aug1, Sep1, Oct1, Nov1, Man_Months_year1,
Annualized_FTE_year1 )
SELECT xlsCalendar.[Chiaramonte DR], xlsCalendar.[Chiaramonte DR +1],
xlsCalendar.[Res GID], xlsCalendar.Name, xlsCalendar.id_serv,
xlsCalendar.[Serv Rate], xlsCalendar.[Proj ID], xlsCalendar.[Common
Project Name], xlsCalendar.[Subproject ID], xlsCalendar.Subproject,
[xlsCalendar].Year1 AS Expr1, xlsCalendar.Dec, xlsCalendar.Jan,
xlsCalendar.Feb, xlsCalendar.Mar, xlsCalendar.Apr, xlsCalendar.May,
xlsCalendar.Jun, xlsCalendar.Jul, xlsCalendar.Aug, xlsCalendar.Sep,
xlsCalendar.Oct, xlsCalendar.Nov, xlsCalendar.[Man Months],
xlsCalendar.[Annualized FTE]
FROM xlsCalendar
INSERT INTO Detailed_Forecast ( Chiaramonte_DR, [Chiaramonte_DR_+_1],
Res_GID, Name, Service_ID, Serv_Rate, Proj_ID, Common_Project_Name,
Subproject_ID, Sub_Project_Name, Year2, Dec2, Jan2, Feb2, Mar2, Apr2,
May2, Jun2, Jul2, Aug2, Sep1, Oct2, Nov2, Man_Months_year2,
Annualized_FTE_year2 )
SELECT [xlsCalendar2].[Chiaramonte DR], [xlsCalendar2].[Chiaramonte DR
+1], [xlsCalendar2].[Res GID], [xlsCalendar2].Name,
[xlsCalendar2].id_serv, [xlsCalendar2].[Serv Rate], [xlsCalendar2].
[Proj ID], [xlsCalendar2].[Common Project Name], [xlsCalendar2].
[Subproject ID], [xlsCalendar2].Subproject, [xlsCalendar2].Year2 AS
Expr1, [xlsCalendar2].Dec, [xlsCalendar2].Jan, [xlsCalendar2].Feb,
[xlsCalendar2].Mar, [xlsCalendar2].Apr, [xlsCalendar2].May,
[xlsCalendar2].Jun, [xlsCalendar2].Jul, [xlsCalendar2].Aug,
[xlsCalendar2].Sep, [xlsCalendar2].Oct, [xlsCalendar2].Nov,
[xlsCalendar2].[Man Months], [xlsCalendar2].[Annualized FTE]

The first INSERT statement works. However when I tried to add the
second INSERT statement I get a syntax error. Is there another way I
can do this?
 
J

John Spencer

Are you trying to put both Insert queries into one query? Access doesn't
allow that.

You can run one query
and then run a second query



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Okay I'm trying to get a table to load into another one through a
series of queries. I'm am using SQL statements to do this. The name
of the table that I'm trying to get information into is called
Detailed_Forecast. The name of the queries that I wrote are
xlsCalendar and xlsCalendar2 (these came from a table named 1 -
RESOURCE FORECAST).

These queries are year based. The Detailed_Forecast displays a 3 year
period horizontally in it's table. The RESOURCE FORECAST table
displays the year veritically. Making the RESOURCE FORECAST table
roll into the Detailed_Forecast table is the goal here. Here's what
I've tried through SQL so far, I'll appreciate any help.

INSERT INTO Detailed_Forecast ( Chiaramonte_DR, [Chiaramonte_DR_+_1],
Res_GID, Name, Service_ID, Serv_Rate, Proj_ID, Common_Project_Name,
Subproject_ID, Sub_Project_Name, Year1, Dec1, Jan1, Feb1, Mar1, Apr1,
May1, Jun1, Jul1, Aug1, Sep1, Oct1, Nov1, Man_Months_year1,
Annualized_FTE_year1 )
SELECT xlsCalendar.[Chiaramonte DR], xlsCalendar.[Chiaramonte DR +1],
xlsCalendar.[Res GID], xlsCalendar.Name, xlsCalendar.id_serv,
xlsCalendar.[Serv Rate], xlsCalendar.[Proj ID], xlsCalendar.[Common
Project Name], xlsCalendar.[Subproject ID], xlsCalendar.Subproject,
[xlsCalendar].Year1 AS Expr1, xlsCalendar.Dec, xlsCalendar.Jan,
xlsCalendar.Feb, xlsCalendar.Mar, xlsCalendar.Apr, xlsCalendar.May,
xlsCalendar.Jun, xlsCalendar.Jul, xlsCalendar.Aug, xlsCalendar.Sep,
xlsCalendar.Oct, xlsCalendar.Nov, xlsCalendar.[Man Months],
xlsCalendar.[Annualized FTE]
FROM xlsCalendar
INSERT INTO Detailed_Forecast ( Chiaramonte_DR, [Chiaramonte_DR_+_1],
Res_GID, Name, Service_ID, Serv_Rate, Proj_ID, Common_Project_Name,
Subproject_ID, Sub_Project_Name, Year2, Dec2, Jan2, Feb2, Mar2, Apr2,
May2, Jun2, Jul2, Aug2, Sep1, Oct2, Nov2, Man_Months_year2,
Annualized_FTE_year2 )
SELECT [xlsCalendar2].[Chiaramonte DR], [xlsCalendar2].[Chiaramonte DR
+1], [xlsCalendar2].[Res GID], [xlsCalendar2].Name,
[xlsCalendar2].id_serv, [xlsCalendar2].[Serv Rate], [xlsCalendar2].
[Proj ID], [xlsCalendar2].[Common Project Name], [xlsCalendar2].
[Subproject ID], [xlsCalendar2].Subproject, [xlsCalendar2].Year2 AS
Expr1, [xlsCalendar2].Dec, [xlsCalendar2].Jan, [xlsCalendar2].Feb,
[xlsCalendar2].Mar, [xlsCalendar2].Apr, [xlsCalendar2].May,
[xlsCalendar2].Jun, [xlsCalendar2].Jul, [xlsCalendar2].Aug,
[xlsCalendar2].Sep, [xlsCalendar2].Oct, [xlsCalendar2].Nov,
[xlsCalendar2].[Man Months], [xlsCalendar2].[Annualized FTE]

The first INSERT statement works. However when I tried to add the
second INSERT statement I get a syntax error. Is there another way I
can do this?
Click to expand...
 
G

Guest

Or you could do it something like:

Insert into yourTable(field1, field2, ....)
SELECT T.* FROM
(SELECT field1, Field2, ...
FROM Table1
UNION ALL
SELECT field1, field2, ...
FROM Table2) as T

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


[email protected] said:
Okay I'm trying to get a table to load into another one through a
series of queries. I'm am using SQL statements to do this. The name
of the table that I'm trying to get information into is called
Detailed_Forecast. The name of the queries that I wrote are
xlsCalendar and xlsCalendar2 (these came from a table named 1 -
RESOURCE FORECAST).

These queries are year based. The Detailed_Forecast displays a 3 year
period horizontally in it's table. The RESOURCE FORECAST table
displays the year veritically. Making the RESOURCE FORECAST table
roll into the Detailed_Forecast table is the goal here. Here's what
I've tried through SQL so far, I'll appreciate any help.

INSERT INTO Detailed_Forecast ( Chiaramonte_DR, [Chiaramonte_DR_+_1],
Res_GID, Name, Service_ID, Serv_Rate, Proj_ID, Common_Project_Name,
Subproject_ID, Sub_Project_Name, Year1, Dec1, Jan1, Feb1, Mar1, Apr1,
May1, Jun1, Jul1, Aug1, Sep1, Oct1, Nov1, Man_Months_year1,
Annualized_FTE_year1 )
SELECT xlsCalendar.[Chiaramonte DR], xlsCalendar.[Chiaramonte DR +1],
xlsCalendar.[Res GID], xlsCalendar.Name, xlsCalendar.id_serv,
xlsCalendar.[Serv Rate], xlsCalendar.[Proj ID], xlsCalendar.[Common
Project Name], xlsCalendar.[Subproject ID], xlsCalendar.Subproject,
[xlsCalendar].Year1 AS Expr1, xlsCalendar.Dec, xlsCalendar.Jan,
xlsCalendar.Feb, xlsCalendar.Mar, xlsCalendar.Apr, xlsCalendar.May,
xlsCalendar.Jun, xlsCalendar.Jul, xlsCalendar.Aug, xlsCalendar.Sep,
xlsCalendar.Oct, xlsCalendar.Nov, xlsCalendar.[Man Months],
xlsCalendar.[Annualized FTE]
FROM xlsCalendar
INSERT INTO Detailed_Forecast ( Chiaramonte_DR, [Chiaramonte_DR_+_1],
Res_GID, Name, Service_ID, Serv_Rate, Proj_ID, Common_Project_Name,
Subproject_ID, Sub_Project_Name, Year2, Dec2, Jan2, Feb2, Mar2, Apr2,
May2, Jun2, Jul2, Aug2, Sep1, Oct2, Nov2, Man_Months_year2,
Annualized_FTE_year2 )
SELECT [xlsCalendar2].[Chiaramonte DR], [xlsCalendar2].[Chiaramonte DR
+1], [xlsCalendar2].[Res GID], [xlsCalendar2].Name,
[xlsCalendar2].id_serv, [xlsCalendar2].[Serv Rate], [xlsCalendar2].
[Proj ID], [xlsCalendar2].[Common Project Name], [xlsCalendar2].
[Subproject ID], [xlsCalendar2].Subproject, [xlsCalendar2].Year2 AS
Expr1, [xlsCalendar2].Dec, [xlsCalendar2].Jan, [xlsCalendar2].Feb,
[xlsCalendar2].Mar, [xlsCalendar2].Apr, [xlsCalendar2].May,
[xlsCalendar2].Jun, [xlsCalendar2].Jul, [xlsCalendar2].Aug,
[xlsCalendar2].Sep, [xlsCalendar2].Oct, [xlsCalendar2].Nov,
[xlsCalendar2].[Man Months], [xlsCalendar2].[Annualized FTE]

The first INSERT statement works. However when I tried to add the
second INSERT statement I get a syntax error. Is there another way I
can do this?
Click to expand...
 
K

Kgwill85

Or you could do it something like:

Insert into yourTable(field1, field2, ....)
SELECT T.* FROM
(SELECT field1, Field2, ...
FROM Table1
UNION ALL
SELECT field1, field2, ...
FROM Table2) as T

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.



[email protected] said:
Okay I'm trying to get a table to load into another one through a
series of queries. I'm am using SQL statements to do this. The name
of the table that I'm trying to get information into is called
Detailed_Forecast. The name of the queries that I wrote are
xlsCalendar and xlsCalendar2 (these came from a table named 1 -
RESOURCE FORECAST).
Click to expand...
These queries are year based. The Detailed_Forecast displays a 3 year
period horizontally in it's table. The RESOURCE FORECAST table
displays the year veritically. Making the RESOURCE FORECAST table
roll into the Detailed_Forecast table is the goal here. Here's what
I've tried through SQL so far, I'll appreciate any help.
Click to expand...
INSERT INTO Detailed_Forecast ( Chiaramonte_DR, [Chiaramonte_DR_+_1],
Res_GID, Name, Service_ID, Serv_Rate, Proj_ID, Common_Project_Name,
Subproject_ID, Sub_Project_Name, Year1, Dec1, Jan1, Feb1, Mar1, Apr1,
May1, Jun1, Jul1, Aug1, Sep1, Oct1, Nov1, Man_Months_year1,
Annualized_FTE_year1 )
SELECT xlsCalendar.[Chiaramonte DR], xlsCalendar.[Chiaramonte DR +1],
xlsCalendar.[Res GID], xlsCalendar.Name, xlsCalendar.id_serv,
xlsCalendar.[Serv Rate], xlsCalendar.[Proj ID], xlsCalendar.[Common
Project Name], xlsCalendar.[Subproject ID], xlsCalendar.Subproject,
[xlsCalendar].Year1 AS Expr1, xlsCalendar.Dec, xlsCalendar.Jan,
xlsCalendar.Feb, xlsCalendar.Mar, xlsCalendar.Apr, xlsCalendar.May,
xlsCalendar.Jun, xlsCalendar.Jul, xlsCalendar.Aug, xlsCalendar.Sep,
xlsCalendar.Oct, xlsCalendar.Nov, xlsCalendar.[Man Months],
xlsCalendar.[Annualized FTE]
FROM xlsCalendar
INSERT INTO Detailed_Forecast ( Chiaramonte_DR, [Chiaramonte_DR_+_1],
Res_GID, Name, Service_ID, Serv_Rate, Proj_ID, Common_Project_Name,
Subproject_ID, Sub_Project_Name, Year2, Dec2, Jan2, Feb2, Mar2, Apr2,
May2, Jun2, Jul2, Aug2, Sep1, Oct2, Nov2, Man_Months_year2,
Annualized_FTE_year2 )
SELECT [xlsCalendar2].[Chiaramonte DR], [xlsCalendar2].[Chiaramonte DR
+1], [xlsCalendar2].[Res GID], [xlsCalendar2].Name,
[xlsCalendar2].id_serv, [xlsCalendar2].[Serv Rate], [xlsCalendar2].
[Proj ID], [xlsCalendar2].[Common Project Name], [xlsCalendar2].
[Subproject ID], [xlsCalendar2].Subproject, [xlsCalendar2].Year2 AS
Expr1, [xlsCalendar2].Dec, [xlsCalendar2].Jan, [xlsCalendar2].Feb,
[xlsCalendar2].Mar, [xlsCalendar2].Apr, [xlsCalendar2].May,
[xlsCalendar2].Jun, [xlsCalendar2].Jul, [xlsCalendar2].Aug,
[xlsCalendar2].Sep, [xlsCalendar2].Oct, [xlsCalendar2].Nov,
[xlsCalendar2].[Man Months], [xlsCalendar2].[Annualized FTE]
Click to expand...
The first INSERT statement works. However when I tried to add the
second INSERT statement I get a syntax error. Is there another way I
can do this?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

I'll try this out today.
 
Top