The past few weeks I have received several questions from members regarding their computing and, I think, answered them satisfactorily. Let’s see, I recall questions on not being able to reinstall Win 95, a missing CD ROM drive, getting a DOS program to run properly in a DOS window in Win 95, installing Win 95 C over Win 95, and Microsoft Access. I believe that my answers solved all problems except possibly with Win 95 C, which I had never heard of until a few days before at our regular meeting. I think it is basically Win 95 B with USB support plus Internet Explorer 4.0. I couldn’t give a definite answer on this, only discuss with the member his various options.

I also know that several other members listed in our Help Line have been receiving calls to answer questions from members. Apparently the Help Line is providing a useful service. This is one of the functions of a user group, and, I think, it not only helps those who ask but also those who answer. That may sound surprising, but I find that in solving other’s problems you are forced to better understand and clarify your thinking on the topics in question. The question I received on Microsoft Access is a good case in point.

I am a relatively recent convert to databases having gotten by storing most tabular data in Microsoft Excel until several years back when I bought my first copy of Access. Now, I had owned a database program before. It was called Superbase, and, if I remember correctly, was the first Windows based database. About six years ago we had a demonstration of it at one of our meetings, the members were offered a good price on it, and I bought it. I used it a bit but never really saw much advantage to using it over a spreadsheet to store my limited amount of tabular data—my fault, not the program’s. A few years later, through, I used Superbase (then essentially out of business) to get a competitive upgrade to my first copy of MS Access. Two or three versions later, I have not only quit using Excel to store non-numeric data, I am becoming moderately proficient in Access’ finer points.

A Microsoft Access database consists of a collection of tables, queries, forms, reports, and Visual Basic Code Modules. As I use it, my original data is in a table (consisting of rows called records and columns called fields) or, perhaps, in two or more tables; I then generate queries (other tables) by putting conditions on the records in my original data. The reports and forms are used to display answers to various questions I ask regarding the data. The Visual Basic code, if included, is used to accomplish manipulations of the tables that either are not possible with the other tools available in Access or are not easily possible. Additionally, one can write a separate Visual Basic program which will open up an Access database (totally distinct from using VB code from within Access) and perform the desired manipulations.

Here is where the member’s Access question prompted me to sharpen my skills in Access: How to solve his problem by not resorting to a separate Visual Basic program that opened the database and did what was desired, since he didn’t want to have to deal with a separate program. This forced me to figure out how to put the same code into a VB module within the database, have a form use this code and display the desired results. But, once I had gone this far, why not do away with the code altogether and somehow accomplish the same thing. After several days of off and on again thinking about the problem, I finally produced the solution using one extra query and one report—a far cry from my original solution. I assume that someone who works with Access every day would have immediately adopted this approach.

Now that I have led you this far, you might like to know the original question. A member had a database of recent car sales listing everything (probably from an auction) with fields for the buyer’s name, address, ZIP, VIN, year, auto make, model, and much more we won’t list. So a typical record might look like John Doe, 4254 Main, Saint Augustine FL, 32084, 4T1BG22K4VU071602, 97, Toyota, Camry ce/le/xle sedan 4dr.

The problem was to group the records based on the first word in the model field (not the whole field) and to count the number in each such group. One such grouping would consist of all sales where the model began with Camry, for example. Sounds easy doesn’t it, and I suppose it is for database professionals. But several casual Access users tried it before it got to me with no solution.

My first thought was to write a simple VB program to open the database, find all the different first words in the model field, count the number of records in each such group and display the results. This is indeed a solution as far as I am concerned. However, it requires one to step outside of the Access environment—and was not preferred by the one who presented me with the problem.

The next method was to create a VB module within Access that did basically the same thing. To display the results, I used a form with a list box based on a one column table, where the table’s values were the results found in my VB module. To find the solution, all an individual had to do now was click on a button on the form causing the VB code in the module to run, which when finished updated the one column table whose values were then posted to the list box on the form, where a typical line in the list box might read "Toyota Camry has 20 sales."

This, however, was still unsatisfying in some sense. I just knew that a solution existed using basically no VB code. When gotten, the final method of solution consisted of creating a query with an additional field displaying the first word in the model field gotten using as the definition of the new field the expression left$([model],InStr([model]," ") – 1). So you see that we didn’t entirely get away from VB. Once I had this query, it was then a matter of creating a report that grouped all the records based on this new field (all the Camrys would be together) and counted the number in each of the groups—something that can be accomplished within a report if the right "buttons" are pushed.