Is it possible to import a Union Query from Access into Excel, via
MSQuery?

I keep getting a bogus error that there is no data being returned, when
it works fine in Access.

TIA

I have discovered that this is a limitation of excel - all union
queries must be performed within excel, or the data must be made into
table form before importing.

See the following link

http://66.102.7.104/search?q=cache:D...a&ct=clnk&cd=9

.... it will most likely not work, so for future generation's benefit,
here is the text from the thread:

*************************************************
I have crate a union query in Ms Access XP to combine 2 tables Customer
&
Vendor.

Below is how the query looks like

SELECT strCustomerID as strID, strCompanyName, "Customer" as strType
FROM tblCustomer
UNION SELECT strVendorID as strID, strCompanyName, "Vendor" as strType
FROM tblVendor;

It can run properly in Ms Access but when I try to import the data from
Ms
Access
using Ms Excel's Import External Data | New Database Query, MS Excel
complaints
Too few parameters. Expected 2.

Anyone knows what when wrong?


Thank You

xxxxxx Suggestions that did not work xxxxxxx

I think I found out what's wrong.

The cause of the problem lies in Ms Excel. It cannot support a query
with
calculated/derived field.

However if you do it using Ms Query from within Ms Excel you can do any

union query with any number calculated fields.
************************************************** *********
-S

S Davis wrote:
Is it possible to import a Union Query from Access into Excel, via
MSQuery?

I keep getting a bogus error that there is no data being returned, when
it works fine in Access.

TIA

Can't you create a make table query which accesses that union query in access?
--
Brevity is the soul of wit.


"S Davis" wrote:

I have discovered that this is a limitation of excel - all union
queries must be performed within excel, or the data must be made into
table form before importing.

See the following link

http://66.102.7.104/search?q=cache:D...a&ct=clnk&cd=9

.... it will most likely not work, so for future generation's benefit,
here is the text from the thread:

*************************************************
I have crate a union query in Ms Access XP to combine 2 tables Customer
&
Vendor.

Below is how the query looks like

SELECT strCustomerID as strID, strCompanyName, "Customer" as strType
FROM tblCustomer
UNION SELECT strVendorID as strID, strCompanyName, "Vendor" as strType
FROM tblVendor;

It can run properly in Ms Access but when I try to import the data from
Ms
Access
using Ms Excel's Import External Data | New Database Query, MS Excel
complaints
Too few parameters. Expected 2.

Anyone knows what when wrong?


Thank You

xxxxxx Suggestions that did not work xxxxxxx

I think I found out what's wrong.

The cause of the problem lies in Ms Excel. It cannot support a query
with
calculated/derived field.

However if you do it using Ms Query from within Ms Excel you can do any

union query with any number calculated fields.
************************************************** *********
-S

S Davis wrote:
Is it possible to import a Union Query from Access into Excel, via
MSQuery?

I keep getting a bogus error that there is no data being returned, when
it works fine in Access.

TIA

Does Excel have the ability to make a table into excel?

If so, thats an excellent idea! I currently have it set up to run a
maketable query within access and then bring that in. Id love to get
rid of that extra step.
Dave F wrote:
Can't you create a make table query which accesses that union query in access?
--
Brevity is the soul of wit.


"S Davis" wrote:

I have discovered that this is a limitation of excel - all union
queries must be performed within excel, or the data must be made into
table form before importing.

See the following link

http://66.102.7.104/search?q=cache:D...a&ct=clnk&cd=9

.... it will most likely not work, so for future generation's benefit,
here is the text from the thread:

*************************************************
I have crate a union query in Ms Access XP to combine 2 tables Customer
&
Vendor.

Below is how the query looks like

SELECT strCustomerID as strID, strCompanyName, "Customer" as strType
FROM tblCustomer
UNION SELECT strVendorID as strID, strCompanyName, "Vendor" as strType
FROM tblVendor;

It can run properly in Ms Access but when I try to import the data from
Ms
Access
using Ms Excel's Import External Data | New Database Query, MS Excel
complaints
Too few parameters. Expected 2.

Anyone knows what when wrong?


Thank You

xxxxxx Suggestions that did not work xxxxxxx

I think I found out what's wrong.

The cause of the problem lies in Ms Excel. It cannot support a query
with
calculated/derived field.

However if you do it using Ms Query from within Ms Excel you can do any

union query with any number calculated fields.
************************************************** *********
-S

S Davis wrote:
Is it possible to import a Union Query from Access into Excel, via
MSQuery?

I keep getting a bogus error that there is no data being returned, when
it works fine in Access.

TIA