1

I have a saved query (MyUnion) for a union, appending monthly files (linked views):

select * from RawTrade1801
union all
select * from RawTrade1802

Trying to write this to a new table is proving problematic:

SELECT MyUnion.* into RawTrade2
FROM MyUnion
WHERE Field8 = 'ZAR';

I get an error: Cannot open database "

My aim is to once a month create a master table by appending every monthly file.

E.D.
  • 319
  • 1
  • 5
  • 15
  • The error message, if taken literally, would seem to imply that Access is looking for a database qualifier but cannot find one. – Tim Biegeleisen Feb 16 '18 at 13:47
  • 1
    I get that. The union results are fine, but writing these results to a new table seems to get it unstuck. How else would one get the union results written into a table? – E.D. Feb 16 '18 at 14:01

3 Answers3

1

The following steps should generate a table from your UNION query:

  1. Create a new query. In the SQL View, now type:
SELECT * FROM (SELECT * FROM RawTrade1801
UNION ALL
SELECT * FROM RawTrade1802);
  1. Save the query. In its Design View click the Make Table button. Type in the table name (e.g. NewTable) that you want the output of this query to be.
  2. Save and close the query.
  3. The query icon will have changed. Double click on it and it should generate your table.

Access actually changes the SQL to 

SELECT * INTO NewTable FROM
(SELECT * FROM RawTrade1801
UNION ALL
SELECT * FROM RawTrade1802) AS [%$##@_Alias];

when you view the SQL after step 4.

rnoodle
  • 534
  • 2
  • 5
  • 21
0

Try using INSERT INTO ... SELECT:

INSERT INTO RawTrade2
SELECT * FROM RawTrade1801 WHERE Field8 = 'ZAR'
UNION ALL
SELECT * FROM RawTrade1802 WHERE Field8 = 'ZAR';

Of course, even if this works it still does not explain why your original query does not work. I expect it is a slight technical problem, though I don't know enough Access to see it immediately.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • I am having the same problem as the OP, but creating a table from the sql directly causes a syntax error. I think the problem is an Access thing, but I am unable to find a solution. – rnoodle Apr 05 '20 at 12:43
  • Great...you should ask a question about that `:-)` – Tim Biegeleisen Apr 05 '20 at 13:12
0

Create the Union query, save it with the name like 'qUnion'. From menu open:

  • Create-->Query--> Make Table.
  • In Add Table Menu chose "Queries" and
  • Add your union query ('qUnion').
  • Select the fields that you need.
  • Open query.
  • Anew table will be created.
ouflak
  • 2,458
  • 10
  • 44
  • 49
Alik
  • 1
  • 1