new table from full outer join?? 2

[slim2]

I have merged two tables using the help given on a previous post. I am trying to create a new table from the merged results and get msg ACTION QUERY CANNOY BE USED AS A ROW SOURCE. Is there a way to create the new table.

Thanks for any help

 

[tlbroadbent]

You can convert a query to a Make Table query. The columns selected wil be created and data inserted into a new table when you run the query. Select Query | Make Table... from the main menu. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[slim2]

Terry,

Thanks for the response. I beleive you helped me on the original full join. The make table function works, but would like to create the table without manual intervention. I am running the outer join from a VB command and need to create the table when the button is clicked as part of the outer join. Can this be done?

It would also be workable if the outer join could create a file which I could then import?

Thanks

 

[tlbroadbent]

Could you post the current code? That would give me an idea about the best way to proceed. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[slim2]

SELECT
a.cid, a.name, nz(a.amt,0) AS New1, nz(b.amt2,0) AS New2
FROM lrt1 AS a LEFT JOIN lrt2 AS b
ON a.CID=b.CID;
UNION SELECT
b.cid, b.name, nz([a].[amt],0) AS New1,
nz(.[amt2],0) AS New2
FROM lrt2 AS b LEFT JOIN lrt1 AS a
ON b.CID = a.CID;

Terry,
This code is in a VB command button, and is basically what you replied to me earlier. When I click it it runs but I get no output or indication that anything happened. It runs just fine in ACCESS and shows me the results just like it should be, so it works correctly. This is using a small test set and the final table will have some more fields in it. I am new to ACCESS and VB so I may be asking a basic questioin and appreciate your help. If you need anythiong else let me know.

Thanks
Les

 

[slim2]

Terry,

This the statement in VB. I tried a INTO after the second select but that is where I got my error. I am new to ACCESS and VB so my questions are probably very basic. I appreciate the help you have given me.

Thanks
Les

Private Sub Command2_Click()
Dim SQL As String
SQL = "SELECT a.ID, a.field2, nz([a].[amt1],0) AS new1," _
& "nz(.[amt2], 0) As new2" _
& "FROM lrt1 AS a LEFT JOIN lrt2 AS b" _
& "ON a.ID = b.ID;" _
& "Union" _
& "SELECT a.ID, a.field2, nz(.[amt1],0) as new1," _
& "nz([a].[amt2], 0) As new2" _
& "FROM lrt2 AS a LEFT JOIN lrt1 AS b" _
& "ON a.ID = b.ID;"
End Sub

 

[tlbroadbent]

This should work.

Private Sub Command2_Click()
Dim SQL As String
SQL = "SELECT qry.* INTO NewTable FROM " _
& "(SELECT a.ID, a.field2, nz([a].[amt1],0) AS new1," _
& "nz(.[amt2], 0) As new2" _
& "FROM lrt1 AS a LEFT JOIN lrt2 AS b" _
& "ON a.ID = b.ID;" _
& "Union" _
& "SELECT a.ID, a.field2, nz(.[amt1],0) as new1," _
& "nz([a].[amt2], 0) As new2" _
& "FROM lrt2 AS a LEFT JOIN lrt1 AS b" _
& "ON a.ID = b.ID) As qry;"
End Sub
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[slim2]

Terry,

I ran the VB command and did not get the new table, and no error. I then ran it in ACCESS (ACCESS 97) and get the following error.

Syntax error in FROM clause. (Error 3131) on the select statement after INTO NewTable FROM. Have I done something wrong. Thanks again.

SELECT qry.* INTO NewTable FROM
(SELECT a.ID, a.field2, nz([a].[amt1],0) AS new1,
nz(.[amt2], 0) As new2
FROM lrt1 AS a LEFT JOIN lrt2 AS b
ON a.ID = b.ID;
Union
SELECT a.ID, a.field2, nz(.[amt1],0) as new1,
nz([a].[amt2], 0) As new2
FROM lrt2 AS a LEFT JOIN lrt1 AS b
ON a.ID = b.ID) As qry;

 

[tlbroadbent]

Remove the ; after a.ID = b.ID. Sorry, I didn't see that in my post.
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[slim2]

Thanks Terry that worked. I thought I had tried that, but I guess not.

 

[BradMiller]

I am glad i searched b4 i asked thanks Terry!
thanks for answering my question before i could form it and ask it.