[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 585
  • Last Modified:

Using Excel 2010 to Query Data from an Access 2010 Database


I have a Access 2010 Database broken into a backend stored on a server and mutiple front end clients.

I now want to query the data but access querys are just not living up to the job and i would prefer to use Pivot Tables in Excel to query and modify the data as required.

Any ideas how i can create an excel sheet that i can just refresh as necessary that will pull in the data i need and want??

Any help appreciated,

Thanks,
0
stevid
Asked:
stevid
  • 3
  • 2
  • 2
2 Solutions
 
jppintoCommented:
On Excel go to your File-Open, select an Access database and select the table that you want to import to Excel. Then just work with the data in Excel and everytime you need, just right click on top of the data and select Refresh.

jppinto
0
 
jppintoCommented:
0
 
stevidAuthor Commented:
Thanks Guys, I have tried this and it kind of works,

However my customers are just coming up as a number as opposed to a name, Any ideas how i can link up the Number with the name and Display the Customer name as opposed to Number???

Thanks,
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
stevidAuthor Commented:
So it seems it is pulling in the ID from the Customer Table as opposed to the name, Any thoughts??
0
 
Jeffrey CoachmanMIS LiasonCommented:
Access 2010 has Pivot tables as well.
They are almost identical to what is available in Excel.
Have you looked into this?
I have never had a Pivot on Excel, that I could not replicate in Access.


<So it seems it is pulling in the ID from the Customer Table as opposed to the name, Any thoughts??>
Yes, in Access, typically the ID (the unique value) is referenced, and the "Human Readable" value is displayed.

This is why I don't like complicating my systems with links to Excel, until I fully investigate if what I need can be done in Access alone.

But again, AFAICT, this is ridiculously simple to do in Access alone.

Without having access to your data it is difficult to give you an  exact solution.
You may have a lookup field in the table that displays the text but references the value.

At some point you may need a query that joins the Customer table to whatever data you are linking to.

Again, this is why I keep everything in one application unless absolutely necessary.

Perhaps I am not understanding something though...


JeffCoachman
0
 
stevidAuthor Commented:
In the End i used a V-Lookup in the Excel Table to look up the Names from the Numbers,

Thanks for your help,
0
 
Jeffrey CoachmanMIS LiasonCommented:
...ok
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now