Home Blog

Proper MS Access Customer Contacts Database Design Tips

Planning an MS Access database exclusive of assistance of professional expert MS Access consultants can be done, permitting that you simply understand the fundamentals of correct database design methodologies. After you’ve determined the purpose of your MS Access database, you will need to find and organize needed info that will be stored in your Access database.

To seek out and organize the information needed, begin with your existing information. For example, you might record client information on paper forms in a very ancient file cabinet or an Excel spreadsheet. Gather those documents and list every kind of data element shown (as an example, each box that you just fill in on a paper form). Break the fields down to their basic type, such as initial name and last name, instead of merely “name.” If you don’t have any existing forms, pretend you’ve got to design a form to record the consumer information. What information would you put on the new form? What information elements would you wish to store or maybe run reports on in the future? Maybe you want to know how several fishing lodge guests that stayed with you in the past 2 years live in Alaska? Determine and list each of those items. Another example, suppose you currently keep the fishing lodge guests list in a paper notebook. By closely examining these entries, you might discover every entry contains a customers name, address, city, state, postal code and telephone number. Each of these items represents a potential column in an exceedingly precise table.

As you prepare this list, don?t worry regarding getting it excellent or complete on your 1st attempt. Instead, list each item that comes readily to mind and remember that an MS Access database is an exceptionally simple and modifiable tool. If somebody else in your business can be using the database, solicit for their input. You’ll invariably modify the table as long as you do not have dependent programs relying on the information fields. This is often not rocket science and you usually will not need to use the services of an skilled affordable Access consulting companies to extend the table.

Next, think about the varieties of reports, bulk emails, or brochure mailings you might wish to supply from the new database. For example, you would possibly wish sales reports to point out fishing lodge guests by country, or a list outline report that displays room or fishing charter boat inventory levels. You would possibly additionally want to come up with kind letters to send to purchasers that proclaims the special sales. Design these probable reports in your mind, and picture what it might seem like if it were sitting in front of you. What data would you place on the report? List each item. Do the identical for the shape letter and for any different report you anticipate wanting to conduct your daily operations.

Giving careful thought to reports, bulk email and brochure or flyer mailings helps identify knowledge items you may need in your business database. For instance, suppose you offer Alaska hunting lodge guests the opportunity to opt in for newsletter, and you want to print an inventory of those that have opted in. To store that information, you add a “SendEmail” column to the hunting lodge’s consumer table. For every hunting lodge guest, you can either set the field to Yes or No, True or False, or 1 or 0.

Having to send Email newsletters to Alaska hunting lodge guests suggests another information purpose to store within the table. Once knowing that hunting lodge guests want to receive Email newsletters, you will additionally want to know the Email address to that to send them. Therefore you would like to record an Email address for each hunting lodge guest if available.

To design your table, think about these items carefully. Even knowledgeable professional Access consulting businesses would like to come back and add data elements. This can occur moments when saving the table, or years when the requirement arises to increase the functionality of the database.

Tags: database consulting, database consulting company, ms access consulting, ms access database consultants, ms access database consulting company
Posted in Databases · February 13th, 2010 · Comments (0)

Optimizing Optimal Primary Keys For MS Office Access Tables

When designing new tables in MS Access or redesigning current tables, MS Access database consultants want to ensure that every table should include a column, or two or three columns, that uniquely identifies each record stored in the MS Access database table. Some Access database designers use identification numbers, such as social security numbers, employee Id numbers or product serial numbers. MS Access database designers, along with different database designers for other sorts of databases call this the table’s primary key. MS Access makes use of primary key fields to index, i.e., immediately relate data from several tables and marshal the info together.

If your database table already has a unique identifier, like social security number for a dental patient, or product SKU that uniquely identifies every product in your hardware list, you’ll be able to use that identifier as the table?s primary key. But, you need to be fully bound these values for this primary key column will never be the same for an additional record record. You cannot have duplicate values for database primary keys. For example, do not use city names as a primary key, because names are seldom distinctive in a database’s record set. You could very easily have two cities with the identical name in the same table, like Sandpoint, Idaho and Sandpoint, Alaska.

Primary keys should never be empty, null or repeat. Briefly, primary keys must continuously have distinctive values. If a column’s value will ever be unknown (a missing value) or possibly changed at some future purpose, this column should never be used as a primary key, or part of a composite primary key.

Always choose primary keys whose values will never change. When you have an MS Access database that contains additional than one table, it’s doable and often sensible that the table?s primary key will be used as a reference for other tables. If the primary key changes, the change should also be applied everywhere the key is referenced in the other tables. Coming up with database tables where primary keys don’t change reduces the prospect that the primary key might not coincide with alternative dependent tables referencing it.

One common method used by most MS Access database consultants is to use an arbitrary value, such as a guid, or sequential number. These arbitrary unique numbers are very useful when used as primary keys. For example, you might assign each fishing lodge invoice a unique invoice number. The invoice number’s sole purpose is to identify a fishing lodge’s invoice. Once assigned, it never changes and has no reason to change.

Many professional Access consulting services instantaneously take into account employing a column that has the AutoNumber knowledge sort, in spite of whether there could be a social security number, a unique product SKU number or another distinctive identifier.

When your MS Access database tables use the AutoNumber data type, Access automatically assigns a value for you. Such identifiers have no value and are irrelevant other than they’re the primary key. The AutoNumber contains no relevant info about the record. Since there is no immediate relation to the primary key and this knowledge outside of the record set, AutoNumber identifiers are ideal for primary keys as a result of they do not change. Primary keys containing relevant info or facts regarding a row, like postal codes, telephone numbers or a customer addresses, are more likely to change as a result of the factual info may change throughout the life of the information in your database.

There are times when two or more fields logically makeup an MS Access database table’s primary key. For instance, for an Alaska hunting and fishing lodge, a BookingDetails table stores details for Bookings would use two columns as a primary key: BookingId and LodgeServiceId. When primary keys accommodates a lot of than one column, they are often known as composite keys by database gurus.

Getting your MS Access database style “right” is typically a matter of preference and no two individuals can design the database excactly the same. There are rules that make data access and maintainability less demanding, and these rules are learned in formal settings. One ought to strive to continuously make the database scalable and maintainable.

Tags: database consulting, database consulting company, ms access consulting, ms access database consultants, ms access database consulting company
Posted in Databases · February 13th, 2010 · Comments (0)