Wiley 978-0-7645-9978-1 Datasheet

3When working with Access for the first time, it is tempting to start filling tablesright away and querying data to get fast results, but it is import

Field propertiesAfter entering field names, data types, and descriptions, you can set individ-ual field properties for each column, which will affect

Input MaskThe Input Mask property can be useful in data entry situations. Whereas For-mat controls how data is displayed, Input Mask controls how data

Default ValueAn important database concept, Default Value can help save time in the dataentry process. The default value is automatically placed in a

Tricks of the Trade: Sorting and Filtering for On-the-fly Analysis There is inherent functionality within Access that can assist you in performingquic

To demonstrate this functionality in action, open the CustomerMaster tableand right-click in the State column. The popup menu shown in Figure 1-9activ

ImportingWith importing, you are making a copy of the data and filling a newly createdtable with the copied data. After importing, the data is disconn

WARNING It is important to remember that Access does not let go of diskspace on its own. This means that as time goes on, all the file space taken upb

TIP The Import Spreadsheet Wizard attempts to make an educated guess asto what data type to set for each column. Still, it is a good idea to check the

Figure 1-11 Data is usually stored in an Excel spreadsheet using the flat-file format.In order to get the customer information for each invoice, sever

Figure 1-12 The last record of CORRUL Corp. was not correctly updated to the newaddress.If the City data is not properly updated everywhere, when you

Figure 1-1 The Database window has seven main sections you can work with: Tables, Queries, Forms, Reports, Pages, Macros, and Modules.What is a table?

of that data. The problem is that the data has not really been related; it has simply been shown how it could relate to each other on a particular spr

Figure 1-13 A one-to-many relationship between tables can be identified by the infinitysymbol on the line connecting the tables. Creating and editing

Referential integrityIn addition to establishing relationships between tables, you are able toenforce certain rules that guide these relationships. Fo

What is a query?By definition, a query is a question. For our purposes, it is a question aboutdata, which is stored in tables. Queries can be exceedin

The Query By Design interfaceGo to your sample database and select the Queries tab. At the top, double-click“Create query in Design view.” The Show Ta

Figure 1-16 The Query By Design interface.For this example, select the following three fields: Branch_Num, Cus-tomer_Name, and State. To select fields

Figure 1-18 The Datasheet view of query shows the results of the query.Sorting query resultsHere, you examine how you can sort the results of this que

Figure 1-19 The sort order options for a column are provided by the Query By Design interface.Figure 1-20 The results of the query are nowsorted in as

Filtering query resultsNext, you examine how you can filter the query output so that you retrieveonly specific records to analyze. In Access, this fil

Figure 1-22 The results of the query will be all records that match the criteria.Querying multiple tablesThis section explores how you can perform a q

Figure 1-2 Opening the table in Datasheet view allows you to view and edit the data stored in the table.TIP The number of records in a table is visibl

Once the TransactionMaster has been added to the QBD, you will notice thatthe previously established relationship is represented, as shown in Figure 1

Figure 1-24 Run this query to examine the results.Refining the query furtherYou can narrow down your results even further by filtering the query resul

Click the Criteria cell in the Invoice_Date column and type “4/20/2004”.When you click out of that cell, you will notice that the date is now surround

Figure 1-27 Each line of criteria will be evaluated separately.Using operators in queriesYou can filter for multiple criteria on any given field by us

InSimilar to Or. Tests for all records that have values that are contained in paren-theses. For example, you can filter for both California and Colora

Figure 1-29 Here are your query results.Exporting query resultsNow that you have learned the basics of creating queries, you need to be ableto export

Go up to the application menu and select Tools → Office Links → Analyze Itwith Microsoft Office Excel. In just a few seconds, Excel will open up and o

Figure 1-3 Opening the table in Design view allows you to add field names or change existing ones.Exploring data typesThe field’s data type ensures th

MemoIf you need to store text data that exceeds the 255-character limit of the Textfield, you should use the Memo field. Long descriptions or notes ab

OLE ObjectThis data type is not encountered very often in data analysis. It is used whenthe field must store a binary file, such as a picture or sound

If you are neither importing nor linking data, the ideal way to create a tablein Access is with the Design view. You looked at the Design view previou

Figure 1-4 Open the New Table dialog box and double-click Design View.Now you can save and name your table by selecting File → Save. Give thetable an

Tricks of the Trade: Working with the Field BuilderA great tool for beginning Access users is the Field Builder. It works as a wizardto help you build