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

    returning query to Excel-formating problem (Access/Excel 2000 SR-1a)

    I have an access data base that I want to run two queries on and return the data to an Excel Template I created.
    Each query comes from a different table. The Template is set up to do calculations based upon the data that is returned to the specific cell. That is where the problem is. One of the tables does not have the same amount of data cells, so the data from the second query doesn't align itself with the first one.
    See the attached Excel worksheet for a better explanation.

    Second question:
    Does the headers have to appear when returning data to Excel?
    On the attached sample worksheet, the second query I would run in cell A5 wants to add the headers that it has, even though I already have them from the first one.
    Attached Files Attached Files

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: returning query to Excel-formating problem (Access/Excel 2000 SR-1a)

    All you have to do is go to your access database and, in the query's design view, insert another field just after your name field, something like this:

    Blank:

    This will add a blank field to your query so it will line up with your first query.

    On your second question, I don't know of any way to avoid importing the titles.

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

    Re: returning query to Excel-formating problem (Access/Excel 2000 SR-1a)

    I haven't looked at your sample, but if you're exporting the queries to the same worksheet, why not simply create a single union query from your other two queries and export that? That will give you one set of headers for the union query instead of one for each of the individual queries. You do have to make sure the structures are equivalent, i.e, the same field types in the same order, but the fields don't have to have the same names. So you will need to use CPOD's suggestion and insert an expression into your query as a placeholder.
    Charlotte

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

    Problem with answer (Access/Excel 2000 SR-1a)

    Thanks for the response, but I can't seem to make this work. I suspect its because I created this query in Excel, not Access. I assumed they worked the same way, but this problem tells me they don't.
    I would like to have the data returned to the Excel template I created. If I create this query in Access, can I have the Excel template run it automatically like it does now?
    OR
    Is there another way to answer my original question using the Excel query.
    NOTE: I did edit this query using the Microsoft Query, and not the wizard.

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with answer (Access/Excel 2000 SR-1a)

    Go into your Access database and alter your query2 by adding three new fields as expressions:

    Site:
    Color:
    Size:

    Make sure the fields in query2 are in the same order as they are in query1. Then you can take Charlotte's suggestion of creating a union query:

    SELECT query1.*FROM query1 union SELECT query2.*FROM query2;

    You can then run this third query from Excel.

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

    Re: Problem with answer (Access/Excel 2000 SR-1a)

    i have done what was suggested, but the union query is new to me.
    I found the place to type in the code, but nothing happened.
    I don't know what I did wrong.

    Rick

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

    Re: Problem with answer (Access/Excel 2000 SR-1a)

    I think I got the problem worked out since I last posted my latest question earlier today. The union query does seem to work, but I had to change my expressions. I was receiving an error that said "Duplicate output alias 'exp'. So I changed the expressions to read: exp99: "Site" exp98: "Color" exp97: "Size"
    By doing it this way the query worked, but it returned a value of "Site" in each of the cells under the Title of "exp99", "Color" under Title "exp98" and so on. I don't know too much about expressions, so I'm not sure if I am doing anything else wrong. The union query did work.
    Problem 2: you mentioned that I could run this new query from Excel. When I am in Excel and go to the Run Saved query, I can't get to the Access data base. I tried to copy the new query over to a folder, but it wouldn't do it. It stays in the database. What am I doing wrong?

    Thanks again,

    Rick

  8. #8
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with answer (Access/Excel 2000 SR-1a)

    1. Try:

    exp99: Null

    This should give you a blank field.

    2. You will have to create a new query in Excel. Instead of going to Run saved query, go to New Database query, select your database and the wizard will take you throught it.

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

    Re: Problem with answer (Access/Excel 2000 SR-1a)

    Thanks, the null field idea worked.

    Creating a new query in Excel has posed a problem. When it gives me the list of tables/queries to choose from, only the "true" queries are listed, not the Union one. my other two queries are there, but I would be back to the same problem of running two queries into one Excel sheet.

    Thanks again........We're getting closer!!!

    Rick

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

    Re: Problem with answer (Access/Excel 2000 SR-1a)

    Ok, I found the table using the Excel wizard, however, it is showing that there is no data, when there is. I don't know why its doing this. When I run this union query in Access, the data it returns is correct.

    Rick

  11. #11
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with answer (Access/Excel 2000 SR-1a)

    I don't know what to tell you. I couldn't reproduce the the situation. I created a union query and was able to bring it into Excel without any problem.

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

    Re: Problem with answer (Access/Excel 2000 SR-1a)

    I was able to run the query in Excel, except it just returned the Titles, no data. I put no other filters or criteria on this query, since the original two did that. Can you think of anything that I am doing wrong?
    When I run this union query from Access, it runs it correctly.

    Any thoughts are appreciated!

    Thanks again,
    Rick

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

    Re: returning query to Excel-formating problem (Access/Excel 2000 SR-1a)

    Hi,
    This is a late reply because I have been trying to make this work. I have created the union query, and can export it to Excel, however it doesn't let me create a link to keep it updated. More importantly, I want to create some formulas in excel that analyze this data, which changes daily. If I could open up that Excel workbook and have it automatically link and update the information, it would automatically reannalyze the data.

    I have re-posted this new question a week or so ago, and have been getting help from "epic60sman", but I am not getting closer to an answer. The subject is called: "importing a union query from Access" and it started on July 20.

    Would you look at this please?

    Thanks,

    Rick

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

    Re: returning query to Excel-formating problem (Access/Excel 2000 SR-1a)

    clarification:
    My post that I was referring to was posted on the Excel board, not the Access board

    Rick

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

    Re: returning query to Excel-formating problem (Access/Excel 2000 SR-1a)

    I'm not sure why you're having a problem. If I create a union query in Access, I can open an Excel workbook, use Data-->Get External Data-->New Database Query to navigate to the database in question and bring in the union query I created in Access.

    The query wizard in Excel lets me select the columns from the union query and then pastes the result into the spreadsheet. You don't need a link because the query is dynamic. I can refresh the data from Data menu but I have it set up to refresh automatically when the spreadsheet opens as well. Are you trying to do something else?
    Charlotte

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
  •