Crystal Reports Solutions: Converting Access Reports to Crystal Reports

Some Access Tips

The database window

1. In Access, you store all objects of a database in a single file that has an MDB extension. You manage these objects through the database window. If Access is confusing, thinking of it in this perspective will help clear that up.

2. The six tabs at the top of the database window give you access (no pun intended) to each of the six main object types: Tables, Queries, Forms, Reports, Macros, and Modules.

3. Once you have selected an object type, click on the New button to start creating an object of that type.

4. Clicking on the Design button takes you to Design view (duh!). This allows you to modify an object's structure and properties.  This is important to remember, because if you aren't altering the structure or properties, you should not be in this view.

5. Clicking on the Tables button takes you to, you guessed it, Table view. It is here where you enter data into your tables. Don't attempt to do this in Design view, as many who are unfamiliar with Access attempt to do.

Let's look at the tabs across the top of the database window and see what they mean:

Tables

1. You store all of your data in tables--nowhere else.

2. Give each table its unique focus. For example, tblEmployees would contain employee names, addresses, and other information. You would also have tblVendors, tblSuppliers, tblInvoices, tblProducts, and so on. Notice the nomenclature, here. Start each table with "tbl," never use spaces in the name, and capitalize the first letter of the unique part of the name. Use mixed case, never all caps.

3. Within each table, you have fields--just as you do in a spreadsheet. To add a field, go into Design view.

4. Every row in a table is a record.

5. The first column in each row should contain a Primary Key, which is a unique identifier. For a table of employees, you would use employee Social Security Numbers. For Vendors, you would use their tax ID numbers. For Customers, you'd need to come up with a different system. For products, use a meaningful number, rather than number them sequentially.  If you sell magazine articles, for example, you might have this number: 1960201. Let's look at this number from left to right. 1 means the millenium prior to the year 2000. If you had  a 2, then you'd be good for another 100 years. After that, this technology will not be in use, so who cares? Anyway, you can add another digit and not have data integrity problems. 96 means 1996, 02 means February, and 01 means this is the article that appeared first in the magazine when counting from front to back. Using numbers that have meaning allows you to check for missing data at a glance, and it has other benefits as well.

Queries

1. "Query" is not techno-speak. This word is very similar to "question." In Access, you write a question in a structured way, so you can get information from your database.

2. In Access, you can query across more than one table--this is where much of the real power in Access comes from. Also, you can use queries to grab all the information from specific fields, and place it into one datasheet--no matter how many tables are involved. What you end up with is what database people call a "recordset"--a set of records compiled from a database.

3. Database queries have never been straightforward. Access tries to correct this with the Query By Example (QBE) feature. Go into the Access help menu if you want to grasp what this is about. If you cut your teeth on other database programs, this is a delight to work with. If you are new to databases, it sucks.

4. You use the Design view and the Datasheet view to build Queries, just as you do to build Tables. Fair enough!

5. Use the Help menu to understand the types of Queries you can make. You may inadvertently create a nonupdatable Query, when an updatable one is what you really wanted.

6. Keep this idea of a Query in mind. It is the question, not the data. You do not store data in a Query. Your Query runs every time you open it, and it will reflect only what is in the current set of data.

Forms

Access Forms have two purposes in life. The first is to present the table or query's data in a format that is easy to view or update. The second is to create the interface portion of any Access application--for example, you can create a switchboard t use as the control center of a database. Many engineers would rather see the raw database, but most people would rather not--so forms are helpful when you have "ordinary" users inputting data.

Reports

You can use Access' Report function to get the standard row and column format--but you need not be confined to that format.. You can also use Report to make charts, graphs, catalogs of products and services, or even  mailing labels.

Macros

1. Think of a macro as a pre-recorded set of actions. For example, if you had a macro for starting your car, you'd record every step of the process--opening the door, getting in, putting the key in the ignition, etc. In Access, you might write a macro for automatically sorting data entries by last name.

2. Access has 49 different actions you can use in a macro.

Modules

1. Think of modules as containers in which you store programming code. You can even think of  them as having a plastic lid you must burp, if that helps!

2. Modules will be global (available everywhere in your application), form (available only in the particular form that uses it), or report (available only to the particular report that uses it). If a module is form or report, then you store it inside a form or inside a report. Global modules don't travel with exported data, but form and report modules travel with their forms and reports if you export those forms and reports.

There's a view that should be part of the tabs, in my opinion. Instead, Microsoft buried it under the tools menu. That view is none other than:

Relationships

This view tells you how all the tables link together, and modify that as you need to.

1.It's common to have a tblMain table that lists, for example, all of your customers. That table will have field names such as customer number (primary key), company name, taxpayer ID, and other information not likely to change.

2. Then you'd have a table called tblContact, and it would have field names such as customer number, title, last name, first name, phone, fax, e-mail, personal data, preferences, hours, comments, etc.

3. Then you'd have a table called tblOrders, with all the appropriate field names.

4. You'd have any other tables pertinent to dealing with your customers.

5. The relationships view would show lines connecting the customer number field headings of each table.

6. You can set up "data integrity" so that you don't lose, confuse, or duplicate entities (make sure to read the Help menu about data integrity--it is a major reason for using a relational database in the first place).