Exporting Union Query to Excel

M

Michael

I have the following union query:

SELECT ID, CRC, Supervisor, Manager, Date, Group, Hours, "Datamart" AS
[Source]
FROM Datamart
UNION SELECT ID, CRC, Supervisor, Manager, Date, Group, Hours, "eWFM"
FROM eWFM;

The problem is that I am trying to import this data to excel and I get a
message stating that Too few parameters. Any tips are greatly appreciated!!
 
V

Van T. Dinh

* Have you checked and make sure all the Field names & Table names are
correct? An incorrect name is usually treated a a Parameter and you will
get the error message posted.

* Some of the names you used are keywords / reserved words (Date, Group).
Change the Field names if you can. If you can't, make sure you use square
branckets to denote names rather than key/reserved words.
 
D

Douglas J. Steele

Also, I'm not sure that the square brackets around Source are correct.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Van T. Dinh said:
* Have you checked and make sure all the Field names & Table names are
correct? An incorrect name is usually treated a a Parameter and you will
get the error message posted.

* Some of the names you used are keywords / reserved words (Date, Group).
Change the Field names if you can. If you can't, make sure you use square
branckets to denote names rather than key/reserved words.

--
HTH
Van T. Dinh
MVP (Access)




Michael said:
I have the following union query:

SELECT ID, CRC, Supervisor, Manager, Date, Group, Hours, "Datamart" AS
[Source]
FROM Datamart
UNION SELECT ID, CRC, Supervisor, Manager, Date, Group, Hours, "eWFM"
FROM eWFM;

The problem is that I am trying to import this data to excel and I get a
message stating that Too few parameters. Any tips are greatly
appreciated!!
Click to expand...
Click to expand...
 
V

Van T. Dinh

That's one is OK, Doug.

Alias is a name so the square brackets are valid delimiters. I did a quick
test in A2003 also.
 
G

George Nicholson

UNION SELECT ID, CRC, Supervisor, Manager, Date, Group, Hours, "eWFM"
FROM eWFM;
Click to expand...

should probably be

UNION SELECT ID, CRC, Supervisor, Manager, Date, Group, Hours, "eWFM" AS
[Source]
FROM eWFM;

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Michael said:
I have the following union query:

SELECT ID, CRC, Supervisor, Manager, Date, Group, Hours, "Datamart" AS
[Source]
FROM Datamart
UNION SELECT ID, CRC, Supervisor, Manager, Date, Group, Hours, "eWFM"
FROM eWFM;

The problem is that I am trying to import this data to excel and I get a
message stating that Too few parameters. Any tips are greatly
appreciated!!
Click to expand...
 
D

Douglas J. Steele

Actually, that's unlikely to be the problem: the names of the fields do not
have to agree between the subselects as long as the number of fields and
their types correspond.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



George Nicholson said:
UNION SELECT ID, CRC, Supervisor, Manager, Date, Group, Hours, "eWFM"
FROM eWFM;
Click to expand...

should probably be

UNION SELECT ID, CRC, Supervisor, Manager, Date, Group, Hours, "eWFM" AS
[Source]
FROM eWFM;

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Michael said:
I have the following union query:

SELECT ID, CRC, Supervisor, Manager, Date, Group, Hours, "Datamart" AS
[Source]
FROM Datamart
UNION SELECT ID, CRC, Supervisor, Manager, Date, Group, Hours, "eWFM"
FROM eWFM;

The problem is that I am trying to import this data to excel and I get a
message stating that Too few parameters. Any tips are greatly
appreciated!!
Click to expand...
Click to expand...
 
M

MacDermott

What's the point of the aliasing at all in this query?

Michael said:
I have the following union query:

SELECT ID, CRC, Supervisor, Manager, Date, Group, Hours, "Datamart" AS
[Source]
FROM Datamart
UNION SELECT ID, CRC, Supervisor, Manager, Date, Group, Hours, "eWFM"
FROM eWFM;

The problem is that I am trying to import this data to excel and I get a
message stating that Too few parameters. Any tips are greatly
Click to expand...
appreciated!!
 
V

Van T. Dinh

Doug

IIRC, the types of the corresponding Fields don't have to be the same
either. If Access can assign the combined Field / Column to a strong type,
it will nominate the Column type as Variant.

I think the only requirement in a Union Query is that the number of Fields
in each Select clause must be the same.
 
V

Van T. Dinh

.... and, of course, I meant to write:

"... . If Access canNOT assign ... "
 
G

George Nicholson

My bad. I should have tested before I posted.

I know that the names of fields don't need to match, just the number of
fields. What I *didn't* know was that 'eWFM' would evaluate the same as
'eWFM as SomeAlias" in the query output. I was thinking (erroneously) that
Jet wouldn't know what to make of "eWFM" without "As SomeAlias" and generate
an error because it thought the # of fields were unequal.

It's a good way to end the week: learn something new...

--
George Nicholson

Remove 'Junk' from return address.


Douglas J. Steele said:
Actually, that's unlikely to be the problem: the names of the fields do
not have to agree between the subselects as long as the number of fields
and their types correspond.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



George Nicholson said:
UNION SELECT ID, CRC, Supervisor, Manager, Date, Group, Hours, "eWFM"
FROM eWFM;
Click to expand...

should probably be

UNION SELECT ID, CRC, Supervisor, Manager, Date, Group, Hours, "eWFM" AS
[Source]
FROM eWFM;

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Michael said:
I have the following union query:

SELECT ID, CRC, Supervisor, Manager, Date, Group, Hours, "Datamart" AS
[Source]
FROM Datamart
UNION SELECT ID, CRC, Supervisor, Manager, Date, Group, Hours, "eWFM"
FROM eWFM;

The problem is that I am trying to import this data to excel and I get a
message stating that Too few parameters. Any tips are greatly
appreciated!!
Click to expand...
Click to expand...
Click to expand...
 
G

George Nicholson

What's the point of the aliasing at all in this query?

The [Source] field in the output will specify which table the record came
from.

Can be very handy. Example: Unioning "Budget" and "Actual" data and being
able to easily create a cross-tab from the results. Also makes debugging
easier.

--
George Nicholson

Remove 'Junk' from return address.


MacDermott said:
What's the point of the aliasing at all in this query?

Michael said:
I have the following union query:

SELECT ID, CRC, Supervisor, Manager, Date, Group, Hours, "Datamart" AS
[Source]
FROM Datamart
UNION SELECT ID, CRC, Supervisor, Manager, Date, Group, Hours, "eWFM"
FROM eWFM;

The problem is that I am trying to import this data to excel and I get a
message stating that Too few parameters. Any tips are greatly
Click to expand...
appreciated!!
Click to expand...
 
Top