Microsoft Access 3B: Autonumber / Import and Export: Append Records in a Table

Pros and Cons of Different Primary Keys :

Some movie stores may use the customer's telephone number as a primary key, however this may cause a problem if two people that live in the same house want to rent a video/dvd.

When presenting a video/dvd the clerk would ask for the phone number and then record the transaction under the phone number account.

• If the telephone number was a primary key than only one account could be created for a household.

• It is possible that a video store may want to only issue one account per household and enter the other names of individuals who are able to rent, however if a person moves and changes their phone number this can cause confusion.

Alternatively the store could use a person's driver's license as a primary key.

• Everyone's driver's license is unique, so this would not cause a problem, (assuming everyone had a driver's licence).

• Some stores may require all users to provide a driver's license with picture in order to rent, however this would exclude people who did not drive.

• Other picture id may be used and the number for this other id could be used as the primary key however.

Another solution is to provide each customer with their own number which could be printed on a card and a picture taken of the client for proof.

• The only problem with this is the extra time it takes to take pictures, laminate a card and the hassle if a customer loses their card.

• Fortunately in recent years equipment to do this has become very inexpensive and can be bought for a few hundred dollars, so this is the route many stores have gone.

Note: instead of the "text" format, an account number may be set as a "autonumber" format.

• Each time a new customer is entered, a new number is assigned by Access
• The advantage of having Access assign a number automatically is that a clerk won't accidentally try to assign the same number to two individuals by mistake.
• Another advantage is that all numbers will be in sequence (e.g. 10111, 10112) without the user having to look up the last number used.

Specific Format: Microsoft Access allows you to export a single table at a time in a variety of specific formats:

You can save the data in Microsoft Excel format (.xls) which can be opened directly in the program.

You can also save the data in another spreadsheet program format for use in Lotus spreadsheets.

You can also save in a format for use in other spreadsheets like Paradox or ODBC databases.

General Format: You can also export a single table at a time in a general format in either a delimited or fixed width format:

Delimited Format

Saving as a delimited file allows many programs, including Microsoft Word, Excel and other database programs and custom made programs.

Note: If you were to open the file in NotePad it would appear with quotation marks around the data, and seperated by commas.

"John", "Smith", 122288, "Male", "10"

Fixed Width Format

Saving as a fixed width format is less common and doesn't use quotation marks or commas.

Note: If you were to open the file in Notepad it would appear as shown.

John Smith 122288 Male 10 1234

The problem with this format is that you have to specify the number of spaces for the first name, last name etc. if a person has a very long first name it will cut off the remaining portion of their name.

This website is copyright protected see contact page for details.