Selecting DISTINCT records from 2 Tables into another

[Guest]

I need to set up a table of (Distinct) countries that exist in a master file,
for use in a list box, and add an extra "(select Country)" record up front,
so that it is the default visible choice in the list box.
I thought of creating a replica of the master file (dummy_country_file) with
only one record containing "(select Country)" in the Country field and
appending this value to the output of the SQL statement...
SELECT DISTINCT country INTO countries FROM masterfile
Yet the SELECT DISTINCT country INTO countries FROM dummy_country_file
statement actually replaces the output of the first statement.
Is there a clause I need to specify so that the second SQL appends its
output to the Countries file instead of replacing or should I run an SQL
statement SELECTing DISTINCT from both files at once (which I don't know how
to do)?

Thanks in advance

 

[Guest]

I managed to join the output of the 2 SELECT's using UNION but I cannot make
ACCESS accept the INTO clause in this statement. So far I can only see the
output I want presented on the screen in some temporary file. Does anyone
know how to store the on-screen output to a file?

 

[Van T. Dinh]

Save the Union Query then use the Union as the Source for your SELECT ...
INTO ... Query.

 

[Guest]

Van, thanks for the reply but you have to help a little more.

All this has to be done in code. I need to do this at the push of a button.

I went through lots of documentation pages last night, set up a UNION query
which produces the results I need but only on the screen. I cannot get it to
write to a table. If I could save the result of the UNION query
programmatically, I might as well save it under the table name I desire and
that's it.

Somehow ACCESS doesn't allow me to put an INTO clause in a UNION statement,
although from the SELECT...UNION statement syntaxes I have come across it
should be allowed. Please show me how to save the UNION results to a named
table.

Thanks again

 

[Van T. Dinh]

Do you mean you constructed the SQL by code?

Try constructing the SELECT INTO ... using the UNION SQL as the Source like:

SELECT F1, F2, ...
INTO NewTable
FROM
(
SELECT .... FROM ....
UNION
SELECT .... FROM ...
)

 

[Guest]

Great! Nested SQL statements.. I didn't know that. Thanks a lot.

SELECT country INTO countries
FROM [SELECT country FROM master UNION SELECT country from master_dummy]. AS
[%$##@_Alias]
ORDER BY country;

My code is working as I had imagined. The user presses on a button and the
underlying code executes the SQL statement, which in turns constructs the
unique country table COUNTRIES, plus the extra "(Select Country)" on top. The
master_dummy file holds the "(Select Country)" record. Access placed the AS
clause itself, replacing your parentheses with brackets.

By the way, do you know how I can stop the annoying questions, like "You are
about to delete table so-so", "You are about to paste 7 records" to which the
user has to reply "Yes" 3 times?? Remember, it has to be a statement which
will be placed in the macro being executed when the user presses the "Create
Unique Country Table" button, just before the OpenQuery action.

Thanks for everything!!!

 

[Van T. Dinh]

The QBE (Query grid) interface converts to square brackets with a period in
some older version of Access.

To remove the confirmation dialog, check Access Help on Macro action
SetWarnings if you use Macros. If you use VBA, check VBA Help on the
SetWarnings Method.