MS ACCESS

Source:

https://support.office.com/en-us/article/Access-2007-training-courses-and-tutorials-a777c7b3-1a06-4afe-9fee-e98fb201f883

MS-Access 2007

Choose between Access and Excel:

Goals:

After completing this course you will be able to:

·         Choose the right program — Microsoft Office Access 2007 or Microsoft Office Excel 2007.

·         Know whether to use a relational or flat data structure.

Before you begin

You'll need a bit of experience with Excel, an open mind, and a willingness to try something new.

Topics in this course

     1. Right questions, right program 

     2. Relational means data in multiple tables

     3. The advantages of relating

     4. Flat keeps it all together

     5. To relate, or not to relate?

     6. More questions, more clarity

     7. Solid reasons to use Access

     8. Solid reasons to use Excel

     9. Quick reference card

1.    Right questions, right program

The big question: How do you want to organize your data?

If you're having trouble choosing between Access and Excel, take a moment to answer an important question: Do you need to organize your data into a relational data structure or a flat data structure? Yes, those are geek terms, but don't let them intimidate you. Deciding on a structure isn't hard, and the next sections walk you through each type of data structure and show you how to choose between the two.

For now, just keep in mind that Access is designed to work with relational data, while Excel works best with flat data structures.

    2.   Relational means data in multiple tables:

Relational structures divide your data into related pieces.

A relational data structure divides your information into logical pieces, and places each piece in a separate table. Now, wasn't that easy?

For example, a sales database typically puts information about customers — their names, addresses, and other key facts — in one table, and information about what those customers buy in another table.

Organizing your data that way can make it quite powerful. Keep going to find out more.

         3.   The advantages of relating:

We're not kidding about powerful.A relational data structure has several advantages: It lets you answer important business questions, such as who bought the most (or least) of your product last week. The figure above shows you how this can work. What's more, using separate tables can make your data easier to manage, because each table holds just part of your information. And finally, a relational structure helps keep your information accurate, because you can prevent users from entering data in the wrong table.

    4.   Flat keeps it all together:

Flat data resides in a single table.

In contrast to a relational data structure, a flat data structure is a simple list that isn't related to other data. For example, a grocery list is a flat file, and so is a list of your friends and relatives. Flat data structures are easy to create, and they're also easy to maintain, as long as you don't have too much information.

Simple lists work nicely in Excel. In fact, Excel is designed to create and maintain flat files.

In case you're wondering, each table in a relational structure is also a flat file. For example, in a typical sales database, one table holds a list of customers, and the other holds a list of orders. The only real difference between the types of structures is that with flat files, you don't need to relate the data in one list to the data in another. Each list is useful by itself.

       5.   To relate, or not to relate ?

Actions and repeated data need a relational structure.

             1.    The customer Hanari Carnes...

             2.    ...places an order on July 8...

             3.    ...and another on July 10. One customer, many actions. That kind of repetition needs a relational structure.

So how can you tell if you need a relational structure? By answering a few more questions:

·         First, do you have a lot of repeated data? For example, do you constantly enter the names of cities or states? If so, you can put that repeated data into another table and create a relational structure. Doing so can save you time and effort because you don't have to reenter the same information each time you create a new record.

·         Second, do you want to track actions or events? For example, do you want to track sales or customer complaints? Any time you want to track an action, a relational data structure usually works best.

If you find yourself answering no to those types of questions, or if you only have a small amount of data and you don't mind looking at repeated information, then feel free to use a flat structure.

      6.   More questions, more clarity

Reasons to use each program.

While data structures are important, they aren't the only reasons to choose Access or Excel. A few more questions can help you decide between the two programs:

·         First, do you need to store and manage your data, or do you need to analyze that data? If storage is your primary goal, then use Access, but for analysis, use Excel.

·         Second, do you have a lot of data? For example, is your Excel worksheet so large that it's hard to use? If so, even if you have flat data, Access can make your information easier to find.

·         Is your data mostly text, or is it numeric? Access can store a large amount of text — up to two gigabytes in a single database — while Excel is designed to store numbers and perform sophisticated calculations on them.

         7.   Solid reasons to use Access:

Access = greater access to data.

So here are a few more reasons to use Access:

·         Do you need to help users enter data? For example, some users find it hard to enter data in a grid of cells. With Access, you can work around that problem by creating data entry forms that make it easier to enter data accurately.

·         Do your users need reports? With Access, you can create reports that users can run at any time.

·         Access is also a better choice when you need to have multiple users working on the data at the same time. Several users can work on the same data, because Access locks a record only while a user changes it. You can share an Excel workbook with several users, but the process works best when users change data in Excel at separate times.

·         If you need to connect to several data sources and edit the data directly in those sources, then Access is your choice. You can view external data with Excel, but you can't change that data.

8.   Solid reasons to use Excel

Analyses and numbers ? Choose Excel.

So where does Excel shine? With numbers! You can run sophisticated what-if models and cost-benefit analyses that you can't in Access.

Do you need PivotTable reports? These are interactive tables that allow you to rotate rows and columns and see different summaries of your data. Excel makes it easier to work with PivotTable reports and PivotChart reports — charts based on those tables.

Do you want to convey information visually, by using charts or data bars? Excel is your tool.

Finally, not everyone has Access, and not everyone knows how to use it. Your coworkers may prefer Excel.

Those are just a few of the reasons for using Access or Excel. The links in the Quick Reference Card at the end of this course take you to more information about using each program.

9.   Decide between a relational and flat data structure

·         A relational structure breaks data into separate, related tables.

·         A flat structure is a self-contained list.

     Ask a few more questions:

These questions help you decide between Microsoft® Office Access™ and Microsoft Office Excel®, based on the strengths of each program:

·         Do you need to store and manage your data, or analyze it? For storage, use Access; for analysis, use Excel.

·         Do you have a lot of data? Even if it's flat data, Access can make it easier to use and manage.

·         Is your data mostly text, or mostly numbers? Access can store a lot of text and make it easier to use. Excel makes it easier to store and use numbers in a flat structure.

 Solid reasons to use each program

Access Is Best For	Excel Is Best For
Data entry.	Storing numbers.
Reports that users can run at any time.	PivotTable® reports — reports in interactive tables that allow you to rotate the rows and columns to see different summaries of your data.
Connecting to external databases and editing data in those databases.	Charts, conditional formatting, color bars, and other visual data displays.
Allowing multiple users to work on the data simultaneously.	Greater familiarity. More people use Excel than Access.