Make a Table from a Query (SQL View)

[f430]

i have created a query using the sql view, and want to make this query a table. is there any way i can do that. i have tried clicking on query-make table query, but this takes me to the design view and it completely neglects what i had in sql view.

Thanks

[dsatino]

So you want to make a table based on the results of a SELECT query?

[OldBirdman]

Using Access 2003, I created a SELECT query in SQL view. I then clicked Query->Make-Table query... and Access switched to design view, correctly filling in the grid from my SQL view.
I was presented with a dialog asking for a new table name, and whether to create in this database or another.
I gave a name and chose current database. I was left in design view.
Changing to SQL view, my SQL statement was now altered correctly to a make-table query.

What happened when you clicked Query->Make-Table query...?

[f430]

when i click query> make table query access switches to design view, but the grid is empty, and has no fields filled out

[OldBirdman]

Further testing (Access 2003)
If the SQL can be represented in GridView, Access will do so when Make-Table selected.
If the SQL cannot be represented (i.e. UNION QUERY), the GridView selection is greyed-out (Enabled=False).
But if I press Make-Table in any case, I get a dialog asking for the new table name.
I cannot think of any SQL statement, with or without errors, where Access does not somehow respond either with a filled-in grid or a dialog box asking for further information.
Maybe if I had more information, such as the SQL statement as shown in SQL View, I might be able to help you. Otherwise I can offer no further assistance.

[dsatino]

Quick guess: Your source table name is misspelled.

[f430]

here is my sql code, i wasnt able to get it to work

SELECT   [Part Number], [Code 1], [Quantity 1]
FROM     [Master Table]
WHERE  (([Code 1] IS NOT Null)
OR   ([Quantity 1] IS NOT Null))
UNION ALL
SELECT   [Part Number], [Code 2] AS [Code 1], [Quantity 2] AS [Quantity 1]
FROM     [Master Table]
WHERE  (([Code 2] IS NOT Null)
OR   ([Quantity 2] IS NOT Null))
UNION ALL
SELECT   [Part Number], [Code 3] AS [Code 1], [Quantity 3] AS [Quantity 1]
FROM     [Master Table]
WHERE  (([Code 3] IS NOT Null)
OR   ([Quantity 3] IS NOT Null))
ORDER BY [Part Number];
 

[dsatino]

Ah, you can't view a Union query in design view. If you're UNION query is returning results without a problem, then just save it and build a new query from it. This new query will be viewable in design view and you can use the make-table functionality

[dsatino]

Oh, and I think your confusing it by trying to name your fields as preexisting field names. The column names will be pulled from the first query in the union, so you probably don't have to rename the columns in the rest of queries

[f430]

it worked.
Thanks

[dsatino]

No problem. I don't write a lot pure SQL, so this is more for my own info. Were there any issues with column names you gave to the second and third select queries in the UNION?

[NeoPa]

For those of you relatively new to working directly in SQL (I expect the OP is already happy with his solution but you're also welcome to the knowledge of course), UNION queries (or any type of query that makes sense, but which Access doesn't handle in Design View) can also be handled, directly in SQL, by encapsulating it within a Sub Query (Subqueries in SQL).