Thread: Data Management

I am hoping to get some advice regarding systems analysis. Since starting my job last summer, I have been working on update automations using Excel. We have a report that is generated for spring, summer, and fall (and sometimes winter but not always). This report uses various sources and Excel files, 13 in total. So far I have automated 4 of them and will continue automating. However, the user wants everything we automate to be in a database.

My question is, is it logical to automate in Excel (this includes data cleaning) and then dump into a db or is this going the long and wrong way about things? Can I do all the automations, cleaning, sorting, and etc using Access VBA or should I continue using Excel for staging? My own thoughts on the matter are to continue using Excel as a staging programme and then dumping into Access afterwards but I don't have enough experience in data management to be certain.

Kindest Regards:
Michael

It depends. Some data manipulation will be much easier in Access as you can use SQL directly on the data via stored queries. Other stuff may be easier in Excel, or it may be six of one, half a dozen of the other.
There is certainly not a lot of point sorting data in Excel if you are going to put it in a database.

So what you are basically saying is that everthing I have been doing in Excel I could do in Access? What about data cleansing (values that should be numerical but are text and etc...), can Access clean before the data is inserted into the tables?

If the data looks like a number then it should be automatically converted into a number if you append it to a number field.
Since I don't know everything you have been doing, I couldn't possibly tell you whether you could do it all in Excel but if you are doing charts then I'd always try and do them in Excel. (you can do them in Access but it's nowhere near as easy, IMO).

Thanks romperstomper. This is not easy to decide on, at all. The user wants a database but only because he wants better data management and querying capabilities. I know I can create forms in Excel that help to manage his data and essentially use Excel as a database. Plus, the user here has no experience with Access as he has always used Excel (not that this may matter when the user is using a form for querying). I guess I'll just have to go with what feels right (its gut check time)

If you're using it as a database and creating forms to manage data, then use Access. It's much better at it and it's a lot simpler to create data forms. Just use Excel for analysis and reports you can't easily do in Access.

Alright romper, thanks for that. I will start doing that today.

For the record, analyzing database requirements for real world business applications is a lot different than learning it in school. If only everything was about students, instructors, courses, and etc...

Many of my work projects also end up being a combination of Excel and Access. Access is much better at analyzing and manipulating large amounts of data. It's faster, easier to set up, easier to maintain, and gives you much better data integrity. I usually receive data in Excel, import it into Access, run my queries, then export the results to Excel where I can format and graph them.

Excel and Access each have unique strengths and weaknesses. The key is understanding both to the point where you're able to choose which one would be best for each project you get.

Originally Posted by davegugg

I usually receive data in Excel, import it into Access, run my queries, then export the results to Excel where I can format and graph them.

This is what I was thinking but it seems like it may be a doubling of the work (not that I mind).

Originally Posted by davegugg

The key is understanding both to the point where you're able to choose which one would be best for each project you get.

I guess you would base this decision off of the requirements? I am too novice to know which better suits what, at least at the moment I am too novice!

It really doesn't double the work, you can write a little five line method in Access to import the Excel sheet into a Access table, and another five liner to get it back into Excel. Compare that to the many lines you may need to try to get Excel to mimic Access' Query functionality, and you're saving lots of time.

Experimentation is the best form of learning. Do a project in Access, then Excel, and see what parts of it work better in each. As you learn more, come back to a project you did a year ago. What may have taken days of setup and programming back then could take less than an hour as you learn more.

I've created a project in Access this afternoon and have my tables set up. I just tried to import data from excel but apparently the autonumber doesn't populate when you do an import/data dump. I wonder if I should start a new thread or, maybe I should venture over to an Access forum. @Dave, do you think there is enough talent on this forum to answer my Access questions when they come up?

Absolutely I do : )

Cool because this is the only forum that I like to frequent.