Excel and Access integration and auto-population

25 pts.
Tags:
Acces
Access 2000
Access Database
excel
Excel 2010
Excel macros
Macros
Overall, I want to create a database that tracks defects for several sites and ouputs the information in a pivot table. 

Each site (7 in total) documents their internal defect data and sends it to me weekly.  I then merge all of this data (have been doing so manually) into one spreadsheet to perform my analysis with a pivot table.  The problem is that not all sites will have the same applicable data, so I made a master spreadsheet with all fields tailoring each sites to the master sheet's columns to simplify merging - I blocked non-applicable columns.



Software/Hardware used:
MS Access 2000 and Excel 2010

Answer Wiki

Thanks. We'll let you know when a new response is added.

In my opinion, the best approach is for your users to enter their data into a single repository where you can access it. This eliminates them sending it to you and you merging it into your database.

For example: Put your Access database on a network share visible to all of your users. (Or download and install MySQL or SQL Server Express – both free – on a shareable server). Provide an Access form for them to enter their data. If they prefer Excel, you can provide a spreadsheet instead that is designed to post their data to your database. Then use a simple Excel spreadsheet to extract the data and create your PivotTable.

If you want to use Excel to post data to a database but don’t know how, please see my blog in this site: <a href=”http://itknowledgeexchange.techtarget.com/beyond-excel/forward/”>Beyond Excel</a>.

If you’d like to collaborate on this as a project that I could demo in the blog, e-mail me: CHatmaker@CarlisleCC.Com

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Sm6hans
    I thought I could use MS Access to create forms, have a query auto-populate a table, and output the data in a pivot table, but it hasn't been working very well. Any suggestions will be much appreciated!
    25 pointsBadges:
    report
  • Randym
    Its hard to tell what is wrong by just saying it isn't working well. But, in theroy, what you want to do will work. I have done applications similar to this. I used Access to import data from spreadsheets, .csv files and other RDBMS tables and pulled them all together in one master table. Then I still used Excel to link to the Access database which then has multiple pivot sheets depending on the type of output desired. Again, your description is too vague to tell you what is not working correctly.
    1,740 pointsBadges:
    report
  • Sm6hans
    Thanks Randym. I have practically no experience with Access. You say it can be done. Whats the best way to import data - on a weekly basis - being sent from 7 different sites? How can I merge spreadsheets with different columns into a master spreadsheet with all the columns?
    25 pointsBadges:
    report
  • Randym
    You could import as often as you want. It depends if any data needs "fixing" for how much you want to fix at one time. If there a never any probems with the data coming in, then you could maybe just do it once a month. It depends how often your pivot tables needs to be updated. For how to merge. To get started, there will be some trial and error on what is the best way to import into Access. You may link the Excel spreadsheet so it looks like a table in Access or you may have the user save the speadsheet as a .csv file and you could import the .csv file into a "work" table. If all seven sites have the exact stucture, you could have one link or one import by saving the source document as the same name. If you need to track each sites data, the spreadsheet should have some sort of identifer in it or you need to attach a site identifer to it when you import the data. If the sites have different structures, you may need to have more than one link or .csv import structure for each type of file coming in. Once you do that, you can use queries to manipulate the data into a master table. Then you could use Excel to link to that master table and do all of your pivot reports. Access can do pivot reports, but I found that Excel may be more end user friendly especially if the end user works with Excel alot and wants to created many different reports.
    1,740 pointsBadges:
    report
  • Randym
    I should add that once you figure out all the steps and get the procedures in place, you can have a form with a button that will do all the work for you. I do want to point out that this may be somewhat complicated if you don't have much experience using Access. One thing to keep in mind is to make sure that you don't load duplicate data and maybe provide a way to "reload" the data. Meaning have an identifier for the imported data that indicates the "batch" of records loaded. I like to use date and time in format yyyymmddhhnnss. This way you can easily remove all records loaded in a batch and reload.
    1,740 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following