Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

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

    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!!!!!!!!!!!! <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Rick

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

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

    I don't understand what you meant.

    Rick

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

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

    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
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

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

    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

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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).

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

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

    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

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    Take a look at <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=acc&Number=58059&page=0 &view=&sb=&o=&part=&vc=>my response</A> 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.
    Charlotte

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  11. #11
    Lounger
    Join Date
    Jan 2001
    Location
    Newfoundland
    Posts
    41
    Thanks
    4
    Thanked 0 Times in 0 Posts

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

    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.

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

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

    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

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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?
    Charlotte

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

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

    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

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

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

    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •