• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

dynamic data transer from access query to excel

V

vpxlquest

Member
Hello ,

I have created sql queries in my access database which provides data based on date fields entered by user. it works fine. I want to display the result in an excel sheet dynamically changing the date every week.


'SELECT JOBAllocations.Date, JOBAllocations.USERJOBNUM, JOBAllocations.PHASE, JOBAllocations.HOURS, JOBAllocations.DESC, JOBAllocations.ETRAKCODE, JOBAllocations.USERDETNUM

'FROM JOBAllocations

'WHERE (((JOBAllocations.Date) = [WEEK ENDING DATE MM/DD/YYYY]) And ((JOBAllocations.USERJOBNUM) = "1883") And ((JOBAllocations.PHASE) = "OFFICE"))

'ORDER BY JOBAllocations.PHASE;


this is the sql query generated in access how can i dynamically display it without opening access. I am hoping to get the data displayed and create a pivot table to display the data in excel

some help will be appreciated.
 
Hmm. Been awhile since I worked with Access...I think there's a way to set the criteria equal to "Parameter", which then causes the program to stop and ask the user for the parameter (ie, the date). As for exporting the results to XL, I think that's something the query wizard gives you the option of doing. If you can't change it with your current query, the fastest way to learn is to probably create a new query that exports to XL, then look at the VB and see how it did it.
 
You can create a PivotTable in Excel based on an external data source, including an Access query.


See http://office.microsoft.com/en-us/excel-help/about-pivottable-and-pivotchart-source-data-HP005199304.aspx


However it may not work since your query requires user intervention. In my experience, MS Query throws an error (although this article suggests that it is still possible: http://support.microsoft.com/kb/200190).


The way I do it is based on this approach: http://www.ozgrid.com/forum/showthread.php?t=18289 -- however you'll need to alter your query so it runs from Excel and takes user input there. You'll also need to introduce macros into the workbook because that is the only way to get the query to accept variable input.
 
thanks for your post

i have used the microsoft.com website and got what i wanted using the pivot table report filter i have filtered the output to show the data i want.i am planning to make a dashboard with this data. thanks again
 
I love addins:


http://www.contextures.com/xlPivotPlayPlus01.html


Pivot Play lets you edit the query on the fly.
 
You must log in or register to reply here.
Back
Top