"Humungous excel spreadsheet as database" problem

[foggy]

I am a constant bettor of soccer and have been using Excel as a database for the last 4 years. I have results and stats for over 71,000 games. This is not a problem but I have another spreadsheet that assesses certain selections and then calculates all sorts of stats across the entire width of the spreadsheet ( to column "IV") and down 10,000 lines, which is only enough to get me through to the end of this year.

Originally these two spreadsheets were in the same file (size 77mb) but I have had to split them up so that this assessment sheet is by itself (size 52mb).

There are a few other files that this one is linked to and this large one takes the best part of 2 minutes to open even though I have a 3.62 GHz processor.

With a file this large it takes a while for it to re-calc after each piece of data entry (approx 20 - 30 secs) in each cell, unless I turn "auto-calc" off and re-do at the end.

I am currently 3 and half weeks into betting the best selections that the stats that this "database" has revealed for upcoming games and have obtained a strike rate of 76.92% at $1.6372 avg div (26 sel/20 wins) in this time.

From these stats, I am keen to keep it going, and going well and fast, and for longer than one year.

The question I need to ask is, am I better off using Access or this SQL I have heard about?

I do not know anything about SQL but every attempt that I have made in the past to use Access leaves me dumbfounded. I find it very hard to learn and, unfortunately, do not have it on this PC.

Anyone's experience and suggestions in this matter would be greatly appreciated.

[foggy]

I guess this one would have been better off posted on the Microsoft Community website for Access and Excel. Probably no one on this site uses SQL to be able to make an informed suggestion to my query.

Thanks to anyone who may have given it thought.

[lomaca]

I guess this one would have been better off posted on the Microsoft Community website for Access and Excel. Probably no one on this site uses SQL to be able to make an informed suggestion to my query.

Thanks to anyone who may have given it thought.SQL is basically a query language that you can use on databases.

Excel is inherently slow once you get over a certain data size and while it's attractive because it's relatively easy to get going, one soon finds out the limitations.

Access is an alternative as are half dozen other databases, but you don't have to use the Access programming language to manipulate the data.
If you can use Excel (VBA) language you are half way there, you can use Visual Basic or VB.NET or any other programming language in conjunction with SQL, while Access is acting as a mere repository of your data.

I think there is even a free database available with Open Office.

True, the learning curve may be steep, but your other options are limited, the best you could do is to break up your sheets further into smaller data sheets, based on dates or locations and link them.
Whichever way you go it will take some learning.

Good luck

[Crackone]

Hi Foggy if I have a problem with excel I look at this site,

http://www.mrexcel.com/forum/index.php

[foggy]

Thanks guys, both suggestions were great.

The splitting of the file down to just that one sheet has improved it quite a bit but I am hesitant to have dates split up as I would then have to open numerous files, say by the month, if I wanted to add in some new parameter to assess. It currently calculates assorted "form selection methods" for the last 4000 contenders back to Jan 1 this year. If this was the case of breaking it up I would then have to open every month separated and input each new assessment criteria formula by column "copy and paste". This would make assessment slow and cumbersome when I have a new idea.

I think the Open Office way to go might be the thing, if it has an Access style database program. Will have a look.

The MrExcel website, looking like a copy of this website, may give rise to a few solutions, as well.

Many thanks for the help.