CODEWITHSUNDEEP
sqlvbams-access-2010
Marco
Marco

Reputation: 23937

How to create table and insert values in one statement

I have been given the task to create a statement, which creates a table and then inserts values from a join of two different tables.

This is a hideous task, just to feed a small application with data, because it can't deal with views (and SQL server for that part)

Which brings me, to my CREATE statement:

CREATE TABLE resultstable1_DIFF (
[Id] Counter Primary key not null,
[km] number,
[Diff] float)

And the INSERT Statement

Insert Into resultstable1_DIFF(km, Diff)
SELECT Round(A.km, 4), Round(X.y - A.y, 4)
FROM sourcetable_1_HQ100 as A INNER JOIN sourcetable_2 as X
ON Round(A.km, 4)  = Round(X.km, 4)

Unfortunatly UNION does not work here. Can somebody point me into the right direction? It is my goal, to create a loop for some 50-ish source table pairs.

If a VBA macro would result in a more elegant and reusable solution, it is fine by me.

Kind regards.

Upvotes: 1

Views: 6066

Answers (1)

valex
valex

Reputation: 24144

I guess Access supports SELECT... INTO so try to use:

SELECT Round(A.km, 4) as km, Round(X.y - A.y, 4)  as Diff
Into resultstable1_DIFF
FROM sourcetable_1_HQ100 as A INNER JOIN sourcetable_2 as X
ON Round(A.km, 4)  = Round(X.km, 4)

But here is the problem with the ID field. I guess you can't make a PK field this way:

"You may want to define a primary key for the new table. When you create the table, the fields in the new table inherit the data type and field size of each field in the query's underlying tables, but no other field or table properties are transferred."

Upvotes: 2

Related Questions