SQL query results to a table

[Bobp3114]

I have an SQL query (Query1) that gets its data from two different tables...works fine.

SELECT tblProduct.ProductID, tblProduct.PartNumber, tblProduct.Product, tblProduct.Purchaseprice
FROM tblProduct
UNION SELECT tblProductsMYOB.ProductID, tblProductsMYOB.PartNumber, tblProductsMYOB.Product, tblProductsMYOB.Purchaseprice
FROM tblProductsMYOB
ORDER BY PartNumber;

I now want to get (import?) those query results into a table in the same database. The table can be a new table or an existing table with fields that include those mentioned above.
Help would be appreciated
Bob

 

[plog]

I see 2 red flags, so before I give you an answer, I have to ask---why? What's the big picture? What does saving query results do for you?

1. A UNION is often a hack around a poorly designed database. Why do you have two tables with so many overlapping fields? How does tblProduct and tblProductsMYOB differ? Are they the exact same structure?

2. Storing query results is something done by people who don't grasp the power of a relational database. Why can't you just use this query for your needs? Why does a whole new table (which overlaps greatly with 2 other tables) need to exist?

 

[arnelgp]

On Error Resume Next
'check if table exists
DoCmd.SelectObject acTable, "newTableName", True
If Err Then
    CurrentDb.Execute "SELECT Query1.* INTO newTableName FROM Query1;"
Else
    CurrentDb.Execute "INSERT INTO newTableName SELECT Query1.* FROM Query1;"

End If

 

[Bobp3114]

Hi
To answer plog:
The employees use MYOB for sales etc
The database is used in conjunction for tracking possible sales, as a database of customers newsletters, calculatinng freight etc...
I have downloaded a spreadsheet of products etc from the MYOB unfortunately there are many products mentioned in MYOB that are not in the database. I have managed to get to the stage of having a list of all missing entries (the Query) but am not having any luck appending those results to the existing Products table (tblProducts
I admit to limited expertise...hence looking to you guys
With appreciation
Bob
PS Thanks also arnelgp ....will give your answer a go tomorrow and let you know how I go

 

[CJ_London]

if you just want to append records from MYOB into the products table, you don't need the union query.

Assuming columns from both tables match in terms of order, names and datatype the query would be

INSERT INTO tblProducts
SELECT * FROM tblProductsMYOB

The query builder can do this for you if names/columns don't match

Open a new query, add tblProductsMYOB
on the ribbon select the Append option and select tblProducts
Select the fields you want to import and on the appendto line, select the destination field from the dropdown

 

[Pat Hartman]

not having any luck appending those results to the existing Products table

OK, what does that mean? Are you getting error messages? Might you want to share them?