Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Import to Excel 2003 from Access 2003 parameter query

    I have a parameter try that ask for two separate entries when run.

    I can connect to this qry from within Excel 2003 but the it doesn't ask for user import for the two parameters.

    I would like to run the Access qry and place the result directly into am Excel template pag

    Thanks. Rick

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Rick,

    Was the query built and saved in the Access database, or did you build it using something like MS Query?
    How are you connecting to the Access database from Excel: Excel VBA, a query table or a pivot table?

    Please can you show us any code and the query statement?

    Thanks

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Query Structure

    This Access 2003 query is the last in a chain of queries.

    It was developed by r937 (Rudy)

    Code:
    SELECT closing_year, closing_month, county, city, 0+MAX(prev_median_closeprice) AS [Prev Yr median close price], 0+MAX(prev_median_size) AS [Prev Yr median sq footage], 0+MAX(prev_sales) AS [Prev Yr number of sales], 0+MAX(curr_median_closeprice) AS [Curr Yr median close price], 0+MAX(curr_median_size) AS [Curr Yr median sq footage], 0+MAX(curr_sales) AS [Curr Yr number of sales]
    FROM monthly_q5_extract
    WHERE walls=[attached or detached]
    GROUP BY closing_year, closing_month, county, city;
    It ask for "Current Year" and the "Attached or Detached."

    I can run this query from within Excel 2003 by selecting . . .

    Data - "Import External Data" - "New DataBase Query" - select "MS Access Data Base" - Path - then "Database Name" - Then ''query name" then "columns in query."

    For filtering I select columns ="closing year" ="Closing Month" but when I run it from there I get an error of "Too few parameters: Expected 2."

    If I could make this work dumping the query results into my Excel template this would save me LOTS of time.

    Thanks Rick

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Rick Schreiber View Post
    It ask for "Current Year" and the "Attached or Detached."
    actually, just the latter
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    I'd create that query within MS Query and use a couple of cells to provide the arguments.

    Nick Hodge has a step by step guide to do it - look under the section "Using Parameters In External ODBC Data Queries".

  6. #6
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Colin - that example was excellent.

    What I've discovered is that it's easier and more practical to simply run the queries in Access and at the same time export the data in .xls format to a specific folder. Then, through the .xlt template I simply link back to the exported file.

    Does anyone see a danger with this?

    It seems to work without destroying the formatting in the template.

    Thanks again. Rick

Posting Permissions

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