Nibelung The Edges Of MS Access (MS Access 2000)

Member Name: Nibelung

Product:	MS Access 2000
Date:	04/11/01 (361 review reads)
Rating:

Advantages: Very flexible, Very comprehensive, Well integrated with Office

Disadvantages: Not the slightest bit intuitive

What is MS Access 2000? It’s a database, and it is included in the more comprehensive versions of Office 2000.

Therefore, although you may be quite familiar with MS Word, and probably even write your opinions on it, and you may even dabble with MS Excel, MS Access is still somewhat more peripheral to MS Office, and definitely a product only for those that need a database.

The major problem confronting anyone tempted to have look at Access out of curiosity is the lack of clues given by loading it. With Word you get a white piece of “paper” and a flashing cursor, so there’s a bit of a hint!

With Excel, you get what appears to be a grid, and it doesn’t take too much experimentation to see that you can enter “stuff” into the squares.

So what do you get with Access? A blank stare from a menu, that’s what. So – no clues there, and this is why, in my opinion, few people will be tempted into a “dabble” which is a shame because it’s a very powerful package. So what could an amateur use a database for? Well, here’s a couple of suggestions.

1.Surprise, surprise, that old chestnut, the name and address list.

2.Then there’s the CD collection – one tip here. If you have any CD writing software like Adaptec Easy CD Creator, or you have the deluxe version of CD player on your PC, these create a database in MS Access format – look for *.mdb files on your c:\drive.

3.How about a home itinerary. We were burgled once and robbed of 300 CD’s plus cameras etc. I’m sure my computerised catalogue of CD’s and serial numbers of practically everything we owned, which helped when it came to being paid out to our satisfaction.

Before I start walking you through the process of creating a database of your own, I would just like to mention the word “relational”, which gets bandied around in phrases li
ke “ah yes, but this database, is it relational?” without the enquirer being challenged as to whether he even knows what the question is!”

I’ll explain. Previous databases, for example, those for name and address or customer details, used to have “tables” of data assembled as you would expect to write them on paper, with columns for Mr/Mrs/Ms, First Name, Surname, 1st Line of Address, Postcode and so.

Someone noticed that it was very wasteful of typing effort AND disk space to have to enter “Middlesex” or where ever, whenever someone who lived there was entered onto the system.

Now text is a bulky thing to store compared to numbers. So the idea of a separate table for the repetitive data, in this case “Counties” was evolved. This meant that there was only one mention of Middlesex on the whole database, numbered in an adjacent column for the sake of argument, as “1” (– not just because I live there, but historically it was known as First County).

The other table, with the rest of the address data, would be set up to have a column called, for example “Link To County”. Anyone living in Hounslow or Uxbridge would then have a “1” in that column. All that remains is to create a permanent “Many Addresses To One County” link, or “relationship”, and Access is one such database capable of doing this. This would have been useful years ago when Slough was moved from Bucks to Berks. With a relational database, all you would have had to do to update your records was to find all records with a mention of Slough, and change their county number to that for Berks. Heh presto, relational databases demystified in seconds!

So, back to basics. MS Access has done its best to help you make a simple database using our old friends, wizards.

Most databases can be split down into the following components (I’v
e used Microsoft’s terminology here)

1.Tables – lists of detail that you want to store

2.Forms – An on-screen input area for laying down new data, or editing existing data within a table.

3.Queries – There’s no point having all this lovely detail stored if you can’t extract it to suit your own parameters.

Here’s an example of what I mean. From a table of all your e-pinions, with details like Title, Subject, Members Reads, Rating, Crowned? *, you could assemble a list of how many ops were nice little earners by creating a query that sought to count up all surveys where the “Crowned?” column had a Yes in it.

* Wishful thinking!

There are other aspects such as Reports, Macros, Pages and Modules, but these don’t really rate a mention in a “cat’s lick” of a description like this.

The Access wizard can be used to get you through stages 1,2, and 3 without too much pain using templates of some of the most common types of database needs.

TABLES - Basically, to construct a table, you need to sketch out what your data input needs are in terms of column formats. Don’t be afraid to use the back of a fag packet. No one said it’s all got to be done on-screen. Data types can range from pure text, to dates, currency, Y/N? etc. Having decided this, you can create a table in “Design View” with your Column Headings and Data Types completed, which might look something like this:-

FIELD NAME - DATA TYPE
First Name = Text
Surname = Text
Member Since = Date (dd/mmm/yyyy)
Paid Up? = Y/N?
Subscription Rate p.a. = Currency (£)

Don’t forget to save the table with a memorable name. You may end up with a lot more within the same *.mdb file before you are finished.

FORMS - Now to create in input form. The easy way is to follow the wizard. It will ask you to identify whic
h table it relates to – now do you see what the meaningful table name was for? You get the opportunity to include or exclude columns from the table before saving the form (with a good name, of course). A completed form will enable you to input new records to the table, or by scrolling through them, editing an existing one.

QUERIES - You can only really get to grips with queries once you have data to manipulate, otherwise you won’t know if they are really finding nothing or just not working, so I would suggest that you input a variety of entries via the form to the table.

Using the dummy table layout above, you could, for instance list all members whose “subs” were overdue, who were also over 80 years old!

To my mind, queries are the engine of a good database. Without them, it’s just a bloody great list!

Being a Microsoft product, it’s very easy to integrate Access with Excel and even Word. Lists within Excel can be either imported or remotely linked as tables. Data listed in queries can be linked back to fill in the blanks in Word documents.

Let me give you an example that I’m quite proud of. Where I previously worked as a performance measurement manager, we were responsible for polling customers at the end of a lengthy project as to what they thought of our Project Manager. The Project manager would give us all the usual customer details, which would be input by my staff on a database form. At the bottom of the form was a “button” programmed to load MS Word, open a specific document and, using the data in a query designed to pull out the latest record’s details, fill in all the blanks in the document so that a covering letter could be sent to the customer asking if he would be kind enough to complete our survey. It also printed the survey with all its respective blanks filled in.

On its return, the survey scores and remarks were input, and the collated
scores were then assembled in queries, which formed the basis of management reports linked into Excel so that they could be easily e-mailed to senior managers. Phew, not bad for a lad with language A-levels, eh?

Of course, you don’t have to get this complex, but just because it’s not obvious what you do with it, don’t be put off. Give Access a try. Who knows, if you use it as a repository of “good phrases”, you could get it to write opinions for you!

My major criticism of Access comes from having had to delve deeper, pushing past the limits of what is possible with the basic process, only to find that I also had to learn to write Visual Basic just to make Modules work, which were only there to overcome the shortcomings of Access in the first place. Well, I know what I mean anyway. At most users level, Access will give them plenty of growing room, so don’t be put off by this.

Summary: