Excel as a database, VBA, MySQL

**gdubf** · 08-28-2009, 10:04 AM

I'm in the process of setting up a database for jobs with these example fields and more:

Job Number | Project Name | Job Type | Area | Construction Cost | Consultant Fees, etc

I'm working on getting the username form setup: add entries to multiple worksheets, search for and modify entries from the userform, etc.

Next will come the query & report part, much more difficult I think. I want to be able to query and create reports based on variables, criteria & ranges such as:

1. Worksheet location & job type
2. Cost greater than / less than
3. Area greater than / less than
4. # of _____ greater than / less than
5. etc, based on other fields

MY QUESTION: Is Excel the best option for this? It was suggested to me that MySql might be better, at least as far as the query part goes. What is your opinion & why?

Thanks,
gdubf

**gdubf** · 08-28-2009, 10:11 AM

The "why" was my question ... for setting up the database I mentioned, in the long-run, what would be the simplest and best option.

Why should I use _______ [your recommendation here] and why? I'm only slightly familiar with Excel VBA, and not at all with MySQL.

I don't know the best for either storing the data, or for querying. For each job though, there's really only a single entry in each field, not multiple entries, if that helps in the relation of the data to each other. I don't think that multiple tables would be necessary per job type with a lot of rows & columns. Each job type won't be analyzed against the other.

gdubf

**DonkeyOte** · 08-28-2009, 10:16 AM

MySQL is a purpose built database... if you're not familiar with it at all and have only slight familiarity with VBA then I would say this is an ill-advised route ... at least initially (you can always migrate to a MySQL db at a later date).

I agree with Roy that there should be no need for separate sheets, the key to db design is to work out those fields you need to report on and differentiate upon - if using XL as the repository you should definitely be looking to avoid the need for cross-tab queries (no pun intended!) - ie store all data on one sheet in a logical / tabular manner.

Be sure to check out Pivot Tables etc as these may well offer you many of the reports you require.

**gdubf** · 08-28-2009, 10:54 AM

Oh, I now understand the question of "different sheets" ... I was thinking "different applications" in my head when I read that.

For some simplicity now, I was planning to have separate sheets for job type, since the jobs will never be compared together. For example, I'd only want to report on just "restaurant" type jobs or "retail" or "office" individually, never comparing them together for cost analysis.

I could set them all up in the same sheet and differentiate with a "job type" field, but then it would all be in the same sheet and if I just wanted to print that whole sheet I couldn't. I've also been having an issue with auto-sorting the newly entered data, so that's another reason. If I could sort by job type first, and then job number, that would then make it more possible.

I'm sure that was all utterly confusing. Please let me know if you'd disagree or do it differently based on what I've written. I welcome your opinions.

Thanks,
gdubf

**excel_guy49** · 08-28-2009, 11:50 AM

This is a job for access database.
You'll already have access with your office suit.
It uses VBA so if your familure with any VBA programming in Excel it wont take too much time to get use to programming in Access.
There are many wizards and guides to help create tables (used to store data) make reports, construct queries (used to organize or filter data).

Many people try to use Excel because they are use to using it. "If all you have is a hammer.. every problem looks like a nail"

This is not a task that should be undertaken using excel. You need a database. MySQL is more powerful than Access database, but unless your going to be having multiple (more than 5) users entering data at the same time. or having millions upon millions of records, access will be a much easier solution to work with. There is also an access forum structured almost exactly like this forum which you can use to help you along. just Google Access Forum.
Good luck.

**gdubf** · 08-30-2009, 10:43 PM

Unfortunately, neither my computer at work or my own personal laptop came with Access. So, I'm trying to work with what I have if possible because that is indeed what I have. So, my question ... is it possible? What can Access do that Excel can't? When you say, "more powerful", what are you saying?

Thanks,
gdubf

**DonkeyOte** · 08-31-2009, 01:39 AM

Not all companies will have unlimited licences for Access so you would need to check with your IT dept to find out if they do but simply opt not to install on default or they have to pay for additional licences - if the latter it's a given they won't want you to use MS Access - on personal PCs it is only included with the Pro Licences and above ie it is not included in Basic / Student / Small Business editions of Office.

Access is as excel_guy49 infers a "jack of all trades and master of none" (my opinion), that is to say it combines the functionality of a database with it's own UI for building reports whereas something like MySQL is only really a data repository (no reporting UI), Excel on the other hand is not really a database it's more a reporting UI (this is the age-long argument) ... I would say really that if your requirements are pretty basic then XL should suffice - I would certainly agree with excel_guy49 that XL is not designed to be a database and is often mis-used but in this instance and given expertise etc it may be the most appropriate route initially. You can certainly scope your db in XL as the same rules will apply in Access/MySQL in terms of how you create your tables etc... fields etc... as you complete the scope you may in turn come to your own decision as to whether or not XL is fit for purpose with the task at hand or if in fact you will need to use another more appropriate app.