Need to Learn MS Access From Scratch and Fast

[Benedict_Arnold]

Any suggestions?

I have a huge database to build and despite some people suggesting using Excel, Excel won't do what a I want without re-typing about half a million entries!

 

[expat_mike]

Any suggestions?

I have a huge database to build and despite some people suggesting using Excel, Excel won't do what a I want without re-typing about half a million entries!

A very large pot of coffee.

Seriously though, surely there must be some way of avoiding re-typing all the data.

What format is the initial data? Is it already in an Access database?

 

[Q5]

What version of Access are you using and how big is the database and in what format is it currently in.

 

[Benedict_Arnold]

I have absolutely no idea what format the original data is in. All I can do is copy and paste it one page at a time off the web. 100 entries at a time.

There's 8,000 active oil companies in Texas, probably 250,000 wells. on probably 200,000 leases, God knows how many partnerships, currently 8,000 or so well non-compliances (most of which are paperwork, thankfully). I'm trying to do an exercise of matching the well to the owner to the non compliances so we can phone the owners up and pitch our services fixing their paperwork or their wells to avoid the State of Texas filling them with cement.

I'm going to end up with a table of oil companies, a table of wells, a table of leases, a table of contacts, a table of non compliances, etc. etc. and I'm hoping to use Access to put all this together and let me sift through the data to drum up some business. Some sort of Boolean and / if / or / not sorts should do the trick, but the problem is going to be getting all the data that I can download from separate parts of a govenrment website into one huge database of our own. We can't do this type of sorting on line. The web tools made available won't allow it.

Some people have suggested using Excel, but I liken that to the old addage "when all you've got is a hammer, every problem starts to look like a nail".

 

[Vladimir]

IME using video tutorials is the fastest way to learn. You can sit back and watch a whole tutorial suit, or the super fast way is to look up individual excercises on youtube when you get stuck.

 

[expat_mike]

I think that you need to approach this with a 'Big Data' exercise mindset.

There are several steps involved, including identifying where your data is, its format, and preparing the data for analysis. Only then does the analysis start, followed by the generation of results.

It does feel that you are trying to develop the total solution in Access, before you have achieved full understanding of the initial data/formats/locations etc.

I too would suspect that Excel is a good tool to store the initial raw data (maybe you do need to copy/paste the data from the web to Excel - boring but not impossible), and then write macros to transform the data into a format ready for analysis. You can then export your prepared data into Access for the database work.

 

[expat_mike]

There's 8,000 active oil companies in Texas, probably 250,000 wells. on probably 200,000 leases, God knows how many partnerships, currently 8,000 or so well non-compliances (most of which are paperwork, thankfully).

I would also suggest that you don't try and tackle the whole exercise in one go.

Start with a pilot project using a small subset of the data. Then you can learn if your analysis technique/tool works effectively, and then scale up using the rest of the data.

Maybe also do a quick google, to find out if this type of problem has already been used as a student/academic project, and the methodology/data is available for you to experiment/gain inspiration from.

 

[Benedict_Arnold]

I'm using Excel as the "paste" end of the copy'n'paste exercise as it easily tabluates the data for me. I can then more easiliy import the data into Access. Later on, much later on, I expect we'll be able to enter new data as and when it arises, either the same way or by some sort of form filling exercise.

I've already downloaded the names of the 8,000 active oil companies in Texas, 100 at a time. I balked at downloading the other 240,000 inactive / defunct ones!

Same goes for the wells. There's no way I'm downloading the data on all quarter of a million of them. After all, I'm not interested in the ones that have already been plugged or the ones without problems. I'm interested in those with problems. All 8,000 of them (mostly paperwork problems, thankfully).

What I didn't realise, and what people outside of Texas probably don't either, is that most of these oil companies are actually individual people who own one or two oil wells on their land. They may have been drilled yesterday, or a 100 years ago, and most of them only produce 10 to 20 barrels a day using a pump-jack ("nodding donkey") to lift the oil out of the ground. There's a 500 barrel oil tank next to the well, and once a week someone comes round with a truck and hauls the oil away. Whole towns (like Tomball, five miles from where I'm sitting) get free natural gas as a byproduct off the oil wells.

Still. 10 barrels a day x 365 days a year x (say) $10 profit per barrel = $36,500 a year for sitting on your behind watching the sun go down. Not a bad living...

 

[Vladimir]

Why not buy the whole index instead of copy pasting? Would they provide a database in excel or PDF?

 

[Benedict_Arnold]

If only....

This is Texas, and the Texas Railroad Commission (which doesn't control trains, just all the oil) rules Texas.

The database is accessible on line on their terms. It's not for sale. It's horribly tedious and I'm going to circumnavigate that tedium by essentially downloading disparate data, mashing it all up into one big database of our own, then extracting the data we need.