Using Access to collect data for excel dashboard

[Kamarlon]

I would like to express a big thank you to Dan who guided me in setting up an Access database. The database is stored on a shared drive. I created a query that collects the data from various tables and I then export the data to the input worksheet in the file containing the dashboard. Access allows 2 big bemefits:

- you can create a log in form where users select their names and enter a password. Based on who the user is they will get access to only the forms which they are supposed to see

- data integrity. In the database i created after persons update the monthly information, a manager goes through to ensure the data is correct. He makes any necessary correcions and and then selects "closed" from a dropdown box. When he selects "closed", users are not able to make changes to the past data entered.


I highly recommend using Access to have multiple users enter their data and then exporting this data to excel to update the dashboard.


Thanks again Dan.

 

[Montrey]

Kamarlon


I want to do this exact same thing! One question how do you export the data into the input worksheet in the dashboard? is this a manual copy paste? or do you have some vba code? U HAVE TO TELL ME!!!! This is my last key to da puzzle. please o please help.

 

[bobhc]

Good day Montery

You can export from access or import from within excel/data/get external data. Just three to four mouse clicks to find and load the access data base file then choose the table to import and that's it excel opens up with a new data table...but may not be as much fun and girlfriend upsetting as diablo 3 but a lot easier and far less aggro

 

[Montrey]

yea GF aggro everyday.


Riddle me this. How do i get started building databases? Would you start by building an excel file like a database then use access to turn the file into a database? What is the easiet way to enter data into a database?


I like the idea of querying on a database because it means that I can access the information anywhere at anytime. It also cuts down on file size to I would imagine because there would be less formulas.


I'm just not sure the steps to go about this. Or where to start/what to do.

Ima sad. : (

 

[dan_l]

No problem. I am happy to help.


2 notes for you Montrey:


-You can get the data out of access a few different ways. Data>External data will let you use the query tool. You can also use VBA. The query tool and some supporting excel side features are really nice. the VBA/ado method seems to refresh quicker - but the functionality to refresh it has to be coded by you.


-to get started: however you want. If your data is already in an excel file, just import it into access. Make sure it defines data kinda correctly: dates should be dates, text should be strings. If not, just build a table in the database. Whatever is practical is fine.


Two more words: skyrim dlc

 

[Kamarlon]

Montrey:

I did it manually.

1. Write click on the name of the query/table from the navigation pane

2. Select Export ---> Excel

3. Choose a file name

4. Select the box for "Export data with formatting and layout

5. Select teh box for "Open destination file after the export operation is complete"

6. Select ok

7. The excel file will open and I then select what I want copy and paste it in the dashboard file. This is ok for me as I only have to do this once every month.


You can hoever set it up so that it is automatically updated. I don't know much about that however.


Probably the microsoft website can also give some more ideas:

http://office.microsoft.com/en-us/access-help/export-data-to-excel-HA001224122.aspx#BM1

 

[Montrey]

Yea ima have to learn more of databases.


Tried Skyrim, was extremely dissapointed. Felt like i was always in the pause menu/item screen thing switching between spells and items and w.e


Just finished Act 2 of diablo 3 last night. It is still EPIC.

 

[dan_l]

-----

Tried Skyrim, was extremely dissapointed.

-----

what is wrong with you?


You don't really need to learn a ton about databases to do this. Here's a great source of dummy data for access: http://www.seanlahman.com/baseball-archive/statistics/


Just play with it.