Please help with plan to take Excel to next level

[curious1]

"Excel is not a database." We know the mantra and are living the reality. We have developed great and complex functions in Excel, often times with the help of the generous experts on this board. We use Excel craftily and lovingly. Our data are simple in structure: fixed number of columns, and a growing number of rows. Now we need evolve and stop forcing Excel to be a database, but still avail ourselves of the critical functions we rely on.

We're not sure how to architect this evolution, however. We need to shift to storing our data in mySQL (mandate). None of us has familiarity with db management, nor are we programmers. We will need to learn and/or outsource. But to help me wireframe the process, are the steps: translate our Excel functions into Language X --> apply them to data in the database --> export the results to Excel (for further analysis)? Or other? (No such thing as an Excel function --> mySQL query converter I wager.) It seems the import/export piece is not the mysterious piece in this puzzle, but how we apply our Excel functions to the data in mySQL is. Any helpful guidance would be truly appreciated!

 

[alansidman]

Perhaps, if you explained as explicitly as possible what you are trying to do, there a lot of folks who are part of this forum that participate in the sister Access forum that could answer your question. Ciritical to this is understanding what you are trying to do. Depending on how big your database is would dictate whether Access is the answer. Here is a link to a good explanation on setting up a database.

http://forums.aspfree.com/microsoft-access-help-18/relational-database-principles-208217.html

I hope this gets you started on the right foot.

Alan

 

[Ron Coderre]

I have the same advice for anyone in your situation.

It doesn't matter which database platform you ultimately use.
What DOES matter is that you understand how databases work.
Just because their screens look like spreadsheets, don't be fooled.
Databases must be approached completely different from Excel.

My recommendation:
Get an edition of "The Practical SQL Handbook".
(It comes with a trial version of MySql and working examples)

That book will teach you:
• how databases are structured.
• how data tables must structured
• how tables interact with each other
• the concepts behind queries and joins
• many other topics that apply to ALL databases...not just MySql

You'll discover that all databases have essentially the same functionality.
The differences are usuallly minor: the odd function and syntax (commas
vs semicolons, etc) Then you'll be able to work effectively with any
database (SQL Server, Oracle, etc).

Note: I have no financial interest in recommending The Practical SQL Handbook.
It's simply a good book.