importing a union query from Access (Excel 2000 (SR-1a))

Topic

New Reply

I have a union query in an Access database that runs correctly within Access.
I want to run that query in Excel, where I have a template, that will allow me to use the data easier.
I seem to be able to create the “new database query” like I have before:
I connect to the database, find the union query, and set up the query table in the same way the source data is arranged. All the table fields are the same as the source data.
But when I run this query, it doesn’t produce any results…nothing, as if there is no data in the fields. No error statements either.
What am I setting up wrong?

Help!!!!!!!!!!!!
Rick

Reply | Quote

Replies

Can you post a file with the query (the sql)?

Reply | Quote

I don’t understand what you meant.

Rick

Reply | Quote

Epic-
I’m not sure what you need, but attached is the excel file with the SQL posted on it.
I’ll try to get you anything you need.
Thanks,

Rick

Reply | Quote

Rick:
I expected to see something that looked like an MSQuery query. This does not look like any of the MSQuery queries that I have done. Are you using MSQery? Is so, have you used MSQuery to save the query to a file? If so, can you locate the file and 2x click it to see what it looks like in text version? If this looks different from what you sent, send or attach it. When you do this, you should be able to see the connection string and some other things.
I suspect that you will see something different. For one thing, the sql you sent is not a Union query (that is, I do not see a Union clause in it) or does the term “Union query” mean something different to you?
Trying to help,
Stephen Stollmack
sstoillma@juno.com

Reply | Quote

epicman,
Thanks for trying to help.

Here is what I know. I created a union query in Access that runs successfully. That SQL statement reads:

SELECT releasedcw.*FROM releasedcw UNION SELECT releasedmr.*FROM releasedmr;

When I go to Excel, run new data base query, it connects to my database and finds this new union query. When I double click on this, it opens a table whose SQL is the one I sent last time, not the one above.

I kind of understand that perhaps there should be more than one table and that there should be “strings” linking the tables together, but there isn’t. I think that in order for multiple tables to link successfully, that there must be a common “primary key”. There isn’t. These are two separate tables that have the same headings, but their data source is different. Maybe they could be linked. I don’t know.

Somehow this query through excel is not able to execute the union query the same way, unless I’m way off base somehow.

So I am back to my original question: Why does a union query work in Access, but is not working in Excel?

One idea to fix this problem quickly: can you call me directly to try to work through things that I am probably missing. I am at work and have an 800#, which works if you are calling from the US or Canada. I am in the Eastern time Zone. I will be here until 7:00 tonight.

I appreciate your help on this!!!!

Rick

Reply | Quote

Rick:
Have you tried writing the query over using MSQuery? I know for sure that ACCESS queries do not look like MSQuery queries. If you are trying to run an ACCESS query out of EXCEL, I do not think you will have any luck.
You shoud see 3 choices when you open the DATA/Get External Data Dialog box in EXCEL:
Run Web Query
Run Database Query and
Create New Query
Chose Create New Query, connect to the database, select the tables, and go the SQL to write your code (or use the Design View which is very similar to what you see in ACCESS).

Reply | Quote

epicman,

Hang with me!!! I really want to work this out.

New idea to solve this problem:
This “union query” that works within Access, but doesn’t run in Excel, can be exported to Excel. That gets me the data to Excel. However, It doesn’t link the way a query does. Therefore it just pastes the data. Is there a way to link it so it updates it each time? That way I can put in the cell calculations and it will always give me the latest data, and therefore the latest analysis of the data.

Here is the big picture:
I have two tables with LOTS of data fields. I have created two separate queries for each of these tables. I then created a union query that combines these two queries that works.
In starting from scratch with Excel, I have tried, unsuccessfully, to import the union query…that was my original question to the Lounge.
I then tried to start with the two separate queries as the two tables, but it too was returning no data (like the Union query) (I suspect because there wasn’t some sort of command to force it to run the query).
So I have just tried to start using the original two tables. but since they can’t be linked (at least I don’t know how), I can’t get them to work in tandem. There is some simple criteria I use on a few of the fields, but they don’t mesh.

Any way to make this work?

Rick
The problem with creating a new query is that there are two different tables involved. I first tried to use the two “query table

Reply | Quote

Take a look at my response in the Access forum. I’m not sure what you’re trying to do or what your problem is, but how many rows are being returned by the union query in Access? I don’t have any problem doing what you’re trying to do with a reasonably sized union query, although I don’t export it from Access, I use Excel to create a new database query and just bring the fields in.

Reply | Quote

Rick:
I think that it would help some iff we could talk for a minutes. We may be barking up the wrong tree. What I mean is that I may not have the level of expertise to solve your problem. On the other hand, i might be overestimating your level of knowledge.
For example, you say “The problem with creating a new query is that there are two different tables involved.” I am thinking, maybe you are refering to a query that links 2 or mpore tables as a “union query”.
A ‘union’ , as I understand the term, is a a query that has 2 or more separate queries connected by a Union operator. I do not do a lot of work in ACCESS so maybe ACCESS refers to a query linking 2 tables as a ‘union’ query or maybe that is your terminology? I regularly connect 3, 4 or 5 tables together using MSquery out of EXCEL using the steps that Charlotte refers to in her response. So, what is the problem??
I think it may be communication.

Reply | Quote

I’m not sure if you’ve solved your problem by now, but I had something similar happen. I resolved the no-data situation by removing spaces from the column headings used by the SQL, and also from the headings set up in Excel. HTH.

Reply | Quote

AnneD!
I think you are on to something. You seem to have had the same problem I am having.
I’m not sure if I understand your suggestions completely. I do have three or four headings that had a two word name, with a space in between. I went to the main source tables in Access and eliminated the space. Now all the queries have headings without spaces.
So I went to Excel and created a New Data Base Query. After connecting to the data base and finding the union query, the list now contains the column headings with no spaces, but the “preview” button still shows no data in any of the fields.
What do you think I am missing.

Rick

Reply | Quote

Is the database you’re querying on a network drive? If so, you could be having timeout or connection problems. Are you returning 10s of thousands of rows? Try it with something smaller and see if that works. The only time I’ve seen a query behave like this, a crosstab query was involved, but I believe you said yours was a straight union query based on two simple select statements, right?

Reply | Quote

Hi Charlotte,

Here is the answer to your questions:
There are only 5-10 rows of data that is returned
The data base is on my computer
When I click the plus sign, it shows all the headings that are in the union query. When I click and highlight one of them, then click the “preview” button to show the data contained in that highlighted field, it shows nothing, i.e. no data. Even though the query contains data when its run from Access

Did you see the post by AnneD to me on this string? She has experienced the same problem of a union query in Access returning only the headings without the data- when ran in Excel. Her solution was a spacing issue in the titles. I am not having much luck with her solution, unless I am not executing it the way she suggested (entirely possible).

Thanks for hanging with me,

Rick

Reply | Quote

Charlotte,

I don’t understand what a “crosstab” query is to know if I am doing one or not.

One of my original problems with this query, which you had a part is solving, was to set up the fields so they aligned with each other, from each table. The problem was that the one table didn’t contain all the same data fields as the other, so I set up “expressions” as dummy fields to make the rest of the headings line up. That did the trick in Access, but maybe its messing up Excel. I haven’t created a sample union query without the “expression” fields.

Thanks,
Rick

Reply | Quote

Hi Charlotte….again,

Please see today’s post to AnneD from me

Rick

Reply | Quote

Only one more possible suggestion: Does your query file have any spaces in the file name?

Reply | Quote

It did, but I changed it after reading your suggestion this morning. However, I still have the same problem.

Let me know if you can think of anything else…it has to be some little thing that is not set up right…The query seems to work, but returns no data.

Thanks again,

Rick

Reply | Quote

I just tried something may help find the root of this problem:

When i do a “new data base query” in Excel and pick, not the union query, but rather one of the two queries that compose the union query, it shows no data when I do the preview. So the problem isn’t necessarily the union query.

The new question becomes, why is this regular Access query not showing any data in Excel.

Rick

Reply | Quote