Can two tables be MERGED into one?

[Guest]

I am at the stage of designing a database and have created 3 different
tables, each with a logical block of information (fields). However, I am
finding that perhaps the data would be better off in a single table.

At this time, the database does not yet contain any data and I wonder if
there is a way of merging two tables into one saving me the task of having to
add each field and properties all over again.

Your assistance will be greatly appreciated. Thank you!
Rocio Lopez-Bretzlaff

 

[Craig Alexander Morrison]

Select the fields in design view and Copy and Paste them to the target also
in design view.

Ensure you update any Table Validation Rules.

This assumes ANSI-92 is not set (which it is not by default)

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Small Business Solutions Provider

 

[Guest]

Craig... it worked like a charm!! and it was so logical I don't know how did
I missed that. Thank you so much!!
Rocio Lopez-Bretzlaff

 

[Guest]

Craig:

Do you know how to to this using vb statements or SQL, I'm trying to use the
Insert Into statment and I'm having a little trouble.

I've created this string:

SQLS = "INSERT INTO LASTAVERUNLINES" & _
"SELECT RUNLINE.* From RUNLINE"
as posted by a member and haven't had success

I use the command
DoCmd.RunSQL SQLS

where SQLS is a declared string

I appreciate any help.

Henry G.

 

[John Vinson]

Craig:

Do you know how to to this using vb statements or SQL, I'm trying to use the
Insert Into statment and I'm having a little trouble.

I've created this string:

SQLS = "INSERT INTO LASTAVERUNLINES" & _
"SELECT RUNLINE.* From RUNLINE"
as posted by a member and haven't had success

I use the command
DoCmd.RunSQL SQLS

where SQLS is a declared string

I appreciate any help.

If you look at SQLS in the Debugger you'll see that it contains

INSERT INTO LASTAVERUNLINESSELECT RUNLINE.* From RUNLINE

As noted in another group, you need a blank before the quotemark after
LASTAVERUNLINES. If that's not the problem... please post a) the
actual Debug window value of SQLS and b) the error message you're
getting.

John W. Vinson[MVP]

 

[Guest]

John:
Thanks for the input. I fixed the problem with the sintax error but now I'm
getting an error with "Sintax error in FROM clause"
This is what I've done: Created a string SQLS as

What I'm trying to do is select all the records from the Runline table and
add them to the master table.

SQLS = "INSERT INTO mastpp " & _
"SELECT RUNLINE.* From RUNLINE "

then I use the command to execute the query:

DoCmd.RunSQL SQLS

Then get the sintax error for FROM the clause.

I read the following from member's post:
RunSQL only works with action queries, such as update, append, delete,
and make-table queries; it doesn't work with SELECT queries.

Is this correct? I'm using Access and VB to code my modules.

Any help is well appreciated.

ProfeG

 

[John Vinson]

John:
Thanks for the input. I fixed the problem with the sintax error but now I'm
getting an error with "Sintax error in FROM clause"
This is what I've done: Created a string SQLS as

What I'm trying to do is select all the records from the Runline table and
add them to the master table.

SQLS = "INSERT INTO mastpp " & _
"SELECT RUNLINE.* From RUNLINE "

then I use the command to execute the query:

DoCmd.RunSQL SQLS

Then get the sintax error for FROM the clause.

If there is a table named RUNLINE, and if the table mastpp has all the
same fields as RUNLINE, this should work. There is something else
going on to generate this error. What are the field definitions in the
two tables?

Some suggestions though:

I'd name the fields explicitly in both the INSERT clause and the
SELECT clause:

INSERT INTO mastapp (fieldname, fieldname, fieldname)
SELECT fieldname, fieldname, fieldname FROM RUNLINE;

And I'd use the error-trappable querydef Execute method instead of
RunSQL:

Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim SQLS As String

On Error GoTo Proc_Err

SQLS = <your query SQL>

Set db = CurrentDb
Set qd = db.CreateQuerydef("", SQLS)
qd.Execute, dbFailOnError

Proc_Exit: Exit Sub

Proc_Err:

I read the following from member's post:
RunSQL only works with action queries, such as update, append, delete,
and make-table queries; it doesn't work with SELECT queries.

Is this correct? I'm using Access and VB to code my modules.

It's correct, but the above query IS an Action query (which contains a
select clause).

Perhaps you could post a bit more information about tables and the
context of the code.

John W. Vinson[MVP]