Greetings and a MrExcel.com Forum Newbie Question

[bebeck]

Good Morning All,

Hope everyone had an opportunity to view the solar eclipse today. I'm glad I took the time to do so. Anyway, Excel-wise, I am mostly self-taught and I spend time watching YouTube to find answers to my Excel "how-to" questions as well as learn new techniques and strategies to use MS Excel more effectively in my work. Speaking of work, I exist in a very software restricted environment where I cannot load useful tools like "PowerPivot" etc. [Don't mean to sound like a whiner, just stating facts] MS Excel 2010 and MS Access 2010 are my only tools at work. I am learning Access, S-L-O-W-L-Y but surely, but would like to discuss how I might use MS Excel 2010 to more efficiently handle my data collection/analysis chores as I continue my database development education. For example, I know how to construct tables in MS Excel and how to use a "Data Form" to fill-in the table. I also discovered that I can utilize a maximum of 32 "fields" (columns) in the data form, so I would need to break up current large worksheets in to "bite-sized" tables. Easy enough, I suppose, and with over a million rows available, I wouldn't run of our data-bucket space anytime soon. Also, using the "Rows" function, I was able to create a primary-key-like field in Excel (sort of) that I might be able to like to other Excel tables, if this is even possible without the addition of third party software. All-in-all, I suppose my REAL question is this: Am I wasting my time trying to beat MS Excel in to relational data base submission? Thoughts? Comments? Experience? Any feedback would be greatly appreciated. Cheers!

V/r,

Bruce (aka "Digital Data Minion")

 

[Joe4]

Welcome to the Board!

I am also mostly a "self-taught" user who started in Excel, then added VBA, and then learned Access. Speaking from experience, sure, you could use Excel as a relational database, though that is not really what it was designed for. So it can be a bit cumbersome, clunky, inefficient, and slow (depending on the size of your data and what it is that you are trying to do).

However, Access IS a relational database program. That is precisely what it is designed to do, so it handles them MUCH better than Excel. Here's the caveat through. Access has a much steeper learning curve, and if you don't understand relational database theory and the rules of normalization when designing your tables and database, you can make things miserable for yourself (been there, done that!).

So it really makes sense before you start using Access deeply to do the following:
- Work through a beginner book or class on using Access (don't really need to go into VBA at this point, just the basics, especially queries!)
- Read up on Relational Database Theory if you are not familiar with it
- Learn and understand the Rules of Normalization

There are lots of good articles out there on the net that cover some of these topics too. Here is one to start you off:
Database design basics - Access

 

[bebeck]

Joe4,

I couldn't agree with you more! FWIW, I'm on page 263 of Michael J. Hernandez's "Database Design for Mere Mortals" (3rd Edition). I'll take a look at the article linked in your response because knowledge is good! Cheers!

BBeck