For Dummies Access 2010
Contents
1. Print Breview amp c Page Size AI Access Objects Search E volunteers Forms E vownteers Reports I Yolunteers2 W vounteers2 Show Margins Sre Margins E print Data Ory ik Columns Page Database Database Access 2007 Microsoft Access fie g Setup Page Layout Volunteers2 State City DE New Castle Wilmington Denver Elizabethtown Elizabethtown Lancaster Landisville Millersville Mount Joy Mount Joy Philadelphia E More Fages Pages Retresh Excel All Last Name Lange Simmons Jamieson Anderson Newville Talbot McGowan Kelly Framingham Framingham wilson 5mythe DARTE AR B Zoom One Two om g 20 Tek POF Emai More Close Print File orkPs Prewew Dats Close Preview First Name Karen Fred Chris Edger Morgan Aan Lori Mary Margaret Thomas Thomas Kevin WG iso Fitter 20 Part I Basic Training You can really roll up your sleeves and design a new report or play with an existing one adding all sorts of bells and whistles Figure 1 5 shows this happening in Design view note that the report s title Volunteers Report is selected It has a box around it and tiny handles on the corners and sides of the box which means you can reformat the title change the font size or color of the text or even edit the words themselves if a new title is needed So y2. H Options B ext You must have SharePoint installed on your network or server to make full use of this feature and of course have other users with whom to share your files for this process to be useful Given that we can t delve too far into Microsoft SharePoint here hey this is an Access book right we rec ommend the following resources to expand your knowledge of SharePoint Mhttp sharepoint2010 microsoft com 1 www wiley com Use the Search box to find books and other learning tools that pertain to SharePoint 2010 http sharepointsolutions com Check this site and of course Google for others to find online courses in making the most of SharePoint 2010 25 26 Part I Basic Training How Access Works and How Vou Work with It ar ar When you look at all the applications in Microsoft Office Word Excel PowerPoint Outlook and of course Access you ll see some features that are consistent throughout the suite There are big differences too and that s where books like these come in handy helping you deal with what s different and or not terribly obvious to a new user Access has several features in common with the rest of the applications in the Microsoft Office suite You ll find the same buttons on several of the tabs and the Quick Access toolbar demonstrated in Chapter 2 appears in all the applications as do the default items on the menu itself If you alread
3. 21146 I volunteers 1 HEHEHE HERET Height 0 375 Top 0 0417 W vounteers2 tert oon Back Style Transparent Back Color Background 1 x x n Border Style Transparent Fs fcity Last Name Border Width Hairline T Border Color Test 1 Lighter 5 aa Special Effect Fist font Name Calibri Detas i id fhiiig itindindiniising Font Size 20 Figure 1 5 a A Text Align General Se a fron wei Normal You can z Font Underline No Font talic No avoid Fore Color Tet 4 Lighter 5 2 line Spacing v j H Hyperink Address D esign Hyperiink SubAcdress Hyperlink Target view but Gridline Se Tep Tamara i Gridiine Style Bottom Transparent to really Gridiine style Lett Transparent Gridline Style Right Transparent customize ba Gridline Width Top 1pt 2 Gridline Width Bottom lpt i r Gridiine vadth Lett lpt things you ll Gridline Width Right Lot i Top Margin 0 need it J a p attom meran i E ne Le Chapter 1 Getting to Know Access 2010 2 If you want to place your personal stamp on every aspect of your report you can use Design view to e Add titles instructional or descriptive text boxes and graphics e Set up customized headers and footers to include any information you want to appear on all the report s pages If all this sounds exciting or at least interesting then you re really on the right track with Access The need to create custom reports is a major reason to use Access you can find out about all thes
4. And certainly if you have thousands of records you need a tool like Access to manage them Although you can use Microsoft Excel to store lists of records it limits how many you can store no more than the number of rows in a single worksheet In addition you can t use Excel to set up anything beyond a simple list that can be sorted and filtered So anything with a lot of records and complex data is best done in Access Some reasons why Access handles big databases well Typically a big database has big data entry needs Access offers not only forms but also features that can create a quick form through which someone can enter all those records This can make data entry easier and faster and can reduce the margin of error significantly Check out Chapter 7 for more about building forms When you have lots and lots of records you also have lots of opportuni ties for errors to creep duplicate records records with misspellings records with missing information and that s just for openers So you need an application such as Access to ferret out those errors and fix them Chapter 9 lays out how you can use Access to find and replace errors and search for duplicate entries Big databases mean big needs for accurate insightful reporting Access has powerful reporting tools you can use to create printed and on screen reports and those can include as few or as many pieces of your data as you need drawn from more than one ta
5. Ctrl key You ve got yourself single click access to well Access Don t have a Quick Launch bar on your Taskbar Right click the Taskbar and choose Toolbars from the pop up menu From the resulting submenu choose Quick Launch It won t have a check next to it if it s not currently part of your Taskbar if it s already checked you do have a Quick Launch bar displayed and just didn t realize it If it s not checked choose it and you ll have a place to put the icons you want to keep handy If you re using Windows 7 you can also right click on any icon in the Programs menu and choose Pin to Taskbar from the pop up menu Quick and easy Selecting a starting point So Access is open and assuming you opened it from the Start menu or from the Quick Launch portion of the Taskbar you re staring at the Access interface You may see features whose purposes elude you or that you don t yet know how to use Hey don t worry that s why you re reading this book You can find out more about all the tabs and buttons panels and menus and all that fun stuff in Chapter 2 for now just look at the ways Access offers Chapter 1 Getting to Know Access 2010 29 Figure 1 12 Pick your recently used data base from the Recent list on the right SSS you to get started with your database be it an existing one that needs work or anew one you have all planned out and ready to go Opening an existing d
6. file name represents a number Access assigns consecutive numbers to the default names counting from any previ ously created databases If this is your absolute first database in a fresh installation of Access the file name offered in this panel will be Database1 Note that you don t need to type a file extension here Access will add the correct one for you 3 As needed choose a new location for the database file by following these steps a Click the little file folder with an arrow on it found to the right of the File Name box Doing so opens the File New Database dialog box shown in Figure 1 15 which you can use to navigate to the drive or folder where your database should live 32 Part I Basic Training Figure 1 15 Name and choose a location to store your database file ESE ar b Use the panel on the left side of the File New Database dialog box to navigate to a folder for your database When you re looking at a list of folders click once to select the one in which you want to store your database c As needed click the New Folder button and name your new folder click OK to return to the File New Database dialog box d Click OK the name you gave the file back in Step 2 is applied and the file is saved to the location you chose a a eee _ Al File New Database es I hailey IE Users gt Laurie Documents gt x bt 4 i Search 2 Nam
7. hoses Access oai Drivers a opens At Volunteer Database Date roodified 11 23 2009 241 PM Shared with Everyone 4 d Microsoft Access Database 106 MB right up Date crested 10 31 2009 3 33 PM ee R 28 Part I Basic Training ar ar Good news Access 2010 will open database files you created with previ ous versions of Access and should support whatever features are employed within those database files All your tables should open prop erly and reports forms and queries should all work fine too If some helpful person has added Access to the Quick Launch toolbar on the Windows Taskbar you can click the Access 2010 icon it looks like a pink key and there you go Access opens for you right then and there Does having an Access icon on the Quick Launch portion of the Taskbar sound extremely convenient It is To add the icon follow these steps 1 Choose Start All Programs Microsoft Office 2 When the list of Office applications appears hold down the Ctrl key and click and drag the Access menu command down to the Quick Launch bar that s the portion of the Taskbar immediately to the right of the Start button A black beam cursor along with an icon for the application and a plus sign indicating that the shortcut will be a duplicate of the command on the menu appears where you point with your mouse on the bar indi cating where the new icon will go 3 Release the mouse button and then release the
8. upa database quickly build records into that database and then use that data in several useful ways Later on who knows You may become an Access guru In this chapter you ll discover what Access does best and when you might want to use another tool instead and get a look at what s new and improved in Access 2010 compared to Access 2007 You ll see how it does what it does and hopefully you ll begin to understand and absorb some basic termi nology Now don t panic nobody s expecting you to memorize long lists 12 Part I Basic Training of high tech vocabulary or anything scary like that The goal here and in the next two chapters with regard to terms is to introduce you to some basic words and concepts to help you make better use of Access in general as well as better understand later chapters in this book if you choose to follow us all the way to its stunning conclusion What Is Access Good For Anyway What is Access good for That s a good question Well the list of what you can do with it is a lot longer than the list of what you can t do with it of course only if you leave things like paint your car and do the dishes off the can t do list When it comes to data organization storage and retrieval Access is at the head of the class Building big databases Okay what do I mean by big database Any database with a lot of records and by a lot I mean hundreds At least
9. Chapter 1 Getting to Know Access 2010 In This Chapter Deciding when to use Access Discovering what s new in Access 2010 Unlocking the basics of working with Access Figuring out how to get started MBER we amp A ccess 2010 the most recent version of the Microsoft Office database application is a very robust and powerful program You probably already know that and perhaps that power or your perceptions of all that Access can do is what made you reach for this book We applaud your wise choice For all of its power Access is also very pardon the expression acces sible It s pretty easy to use at the edges where a new user will be you don t have to venture all the way in to its core to get quite a lot out of the software In fact with just the basic functionality that you ll discover in this book you ll be able to put Access through many of its most important paces yet you ll be working with wizards and other on screen tools that keep you at a comfortable arm s distance from the software s inner workings the things that programmers and serious developers play with Feel better now You don t have to use every feature and tool and push the edges of the Access envelope In fact you can use very little of everything Access has to offer and still have quite a significant solution to your needs for storing and accessing data all because Access can really do it all enabling you to set
10. Chapter 1 Getting to Know Access 2010 2 7 Double click any existing Access database file on your Desktop or ina folder as shown in Figure 1 11 Access opens automatically 5 Windows Update Accessories Dell Dell DataSate Dell Support Center Laurie Dell Video Chat Dell Webcam Documents Extras and Upgrades Games Pictures Maintenance Microsoft Office Music A Microsoft Access 2010 N ee Microsoft Excel 2010 Games Figure 1 1 0 Microsoft Office 60 Day Trial Online Fa Microsott Office Access 2007 Recent Rems The x Microsoft Office Excel 2007 s Wi n dows iG Microsoft Office PowerPoint 2007 Computer lw Microsott Office Word 2007 Start menu N Microsoft OneNote 2010 Network offe rs all the o Microsoft Outlook 2010 2 P Microsoft PowerPoint 2010 Microsoft e wW Connect To Microsoft Publisher 2010 Office Microsoft Word 2010 Control Panel A y Microsoft Office 2010 Tools applications Microsoft Office Tools Default Programs In one 4 Back Heip and Support submenu 0 a Saas Ses G gt Laure Documents League of Humane Voters gt 9 Search P Name s Date modified Type Sice Tays E Docur volunteer Database 11 23 2009 AAPM Microsatt Access 1 092 KB E Pict B Mu M Folders x League of Humane Voters a Symantec a T Downloads i 5 T Favorites Figure 1 11 1 Links m D Mus Double click Patines T Saved Games an Access B Searches B videns database Paik ae file and
11. atabase Well this is the easy one If a database already exists you can open it by clicking the Office button at the upper left of the workspace and choosing Recent from the list of commands that appears As shown in Figure 1 12 a panel opens displaying the databases you ve most recently used Just click the database in the list and it opens listing its current tables queries reports and forms on the far left side of the window Recent Databases BF Open r Datebaseacedb ague of Hurn gue of Humane Voters G Database occa A Volunteer Databas I Database scedb databaset acca ae A Datsbeseb accdb a oo AJA DatebeseSaccdb Dee i X Databssehaced New A Docurnerits When the database is open you can open its various parts just by double clicking them in that leftmost panel whatever you open appears in the main central part of the window Figure 1 13 shows an example a table ready for editing After you open a table you can begin entering or editing records and you can read more about how that s done in Chapter 6 which demonstrates the different ways to edit your data and tweak your tables setups If you want to tinker with any existing queries these too open just by clicking them in the list on the left side of the workspace For more information on queries check out Chapters 11 and 12 You can do simple sorting and look for particu lar records with the skills yo
12. ble if need be You can tailor your reports to your audience from what s shown on the reports pages to the colors and fonts used Chapter 1 Getting to Know Access 2010 13 Big databases are hard to wade through when you want to find some thing Access provides several tools for sorting searching and creating your own specialized tools known as queries for finding the elusive single record or group of records you need Access saves time by giving you new uses for existing tools you may have used to import data from other sources such as Excel work sheets if you started in Excel and maxed out its usefulness as a data storage device and Word tables This saves you from re entering all your data and allows you to keep multiple data sources consistent Creating databases with multiple tables Whether your database holds 100 records or 1 000 records or more if you need to keep separate tables and relate them for maximum use of the infor mation you need a relational database and that s Access How do you know whether your data needs to be in separate tables Think about your data is it very compartmentalized Does it go off on tangents Consider the following example and apply the concepts to your data and see if you need multiple tables for your database The Big Organization database A non profit organization one that rescues homeless pets has a data base of volunteers and contacts past present an
13. cess gt wm g Mome Create External Data DatsbaseTools Fields Table H sse E Seve Object As Available Templates Blank database RI Save Database As f Home B Open sz gl a 8 Ml Volunteer Databas BD veturina Dataa Blank Blank web Recent Sample My templates E Databases eccdb database database templates templates B soatiess man Office com Templates i GI Databases acca aia ala Recent Aset Contects issues amp Non profit Projects Print Save amp Publish Help O Options B it F 1 Publishing to the Web is even easier including the reports you ve cre ated your forms and your data itself The whole shooting match can be opened in a browser window once you publish and upload it to the Web Look in Chapter 10 for more information on how this works 1 Navigation forms make it easier to organize your database components using a simple drag and drop method to display desired parts of your database More about this feature in Chapter 21 Table Events also known as triggers which create new data based on the data in your tables are new in 2010 You ll find out more about those in Chapter 6 for now suffice to say you ll find data macros to bea very handy way to add effective automation to your database Between Data Bars and Conditional Formatting reports have become much more dynamic in Access 2010 Discussed in Chapter 17 you ll find out how to apply formatt
14. create it you use a dialog box that asks you about calculations you want to perform where to place the results and whether you re basing a sort and or a subtotal on more than one field The resulting report is not designed for printing and you have to tinker with your spreadsheet pagination through a specialized view of the spreadsheet to control how the report prints out In Access Just fire up the Report Wizard and you can sort your data choose how to group it decide which pieces of data to include in the report and pick a visual layout and color scheme all in one simple stream lined pro cess Without your doing anything the report is ready for printing Access is built for reporting after all it s a database application and reports are one of the most if not the most important ways you ll use and share your data Because reports are such an important part of Access you can not only create them with minimum fuss but also customize them to create powerful documentation of your most important data Build a quick simple report that just spits out whatever s in your table in a tidy easy to read format See Figure 1 3 for a sample Create a customized report that you design step by step with the help of the Report Wizard See Figure 1 4 The report shown in the figure has the volunteers grouped by the State field Mote DE comes before PA and the records for each state are in ascending order by the City fiel
15. d These options were easily put to work with just a few clicks EEE Figure 1 3 Ah simplic ity A quick report is just one click away ay Ce Figure 1 4 The Report Wizard cre ates more elaborate but simple reports like this one PC Ee lt Home Create wew Themes AlFonts Themes All Access Objects Search Views E volunteers Forms E votunteers External Data E Ag mcos uf E Totals i Hide Details amp Sor a Grouping amp Totals Dstsbase Tools Design Arange EOE Chapter 1 Getting to Know Access 2010 19 Format Page Setup Controls Lay aa Databese3 Database Ac B Page a Eh Date and Time 2007 Necros om g 20 y e Ade Pasting Property Fields Sheet Tools Rogo j tte Header Footer y H H Last Name Adar City E DE Lori McGowan 123 Main Street Landisville H H 2 Mary Margaret l Kelly 34 Elm Avenue Millersville 3 Ann Talbot 761 Chestnut Street Lancaster 4 Edgar Anderson 456 Independence Court Elizabethtown i 5 Thomas Framingham 8701 Rosetree Boulevard Mount Joy H g 6 Chris Jamieson 732 Krause Road Denver T 7 Kevin f Wilson Smythe 150 N 4th Street Apt 3B Philadelphia T Thomas l Framingham 8701 Rosetree Boulevard Mount Joy z Morgan Neville 389 Mission Street Elizabethtown
16. d potential and wants to keep track of a lot of information on them For current and past volunteers the people running the organization want to store information about the vol unteering that was done how much time they spent what they did and for whom For potential volunteers they want to keep track of when and how they ve contacted them whether with mailings and phone calls or at meet ings Imagine keeping all of that in a single table with everything from the volunteer s name to what causes they support to where they live and how much time they can donate For a complex database like this one you d need multiple tables as follows One table would house the volunteer contact information names addresses phone numbers and e mail addresses It might make sense to add a Volunteer Number field which would make each record unique and it would be equally sensible to come up with a number format where one or more of the characters could be used to differentiate among different volunteer contact types past current or potential Asecond table would contain the volunteer number again as a way to link or connect the two tables and also the volunteers status informa tion how much time they have available to volunteer when they re available and which areas they can work in geographically 14 Part I Basic Training ar ar A third table again containing the volunteer number would include
17. e based database shown in Figure 1 16 you can choose a template category by clicking any of the icons which changes the displayed icons in the center of the workspace Click a template icon and you see its details displayed on the right along with the File Name box you saw when starting a new Blank Database When you ve found a template that looks like a good fit for your needs one that starts with the tables forms reports and so on that you think you ll find useful give the new template based database a name and choose a location for it just as we explained in the Starting a new database from scratch sec tion earlier in this chapter Ald ga a IE Databaset Database Access 2007 Microsoft Access o g Home Create External Data DatsbaseTools Field Table led Seve Available Templates Contacts Web Database GR Sove Object As es GH Sove Database as f Home Sample templates BF Open Dj Close Database Qh Oatabase sccdb Cc A Database eccdb M databases acca t S5 G volunteer Databas Charitable Contributions Contacts Weh Database Web Database Info Recent EErEE pin i Print _ i z Z File Name Save amp Publish 2 Everits Faculty Issues Web Database Contacts Web Databasel ag Help Cr Users Laurie Decurnents i Options o E it E t Create ad IAEA Marketing Projects Northwind Project Web Datshase Use the Back button at the to
18. e Date modified Type Size League of Humane Vot 11723 2009 3 39PM File Folder ii Symantec 11 22 2009 12 20 File Folder esk 105 11 19 2009 6 14PM Microsoft Access E Rece Database3 11723 2009 3 27 PM_ Microsoft Access j Computer IE Picture I Musi I Recently Ch j IB Search Publi Folders A jq mi Filename DSSS ad Save as type Microsoft Access 2007 Databases gt Hide Folders Tools OK Cancel 4 Click the Create button A blank table opens An ID column is automatically created along with the first field of your database with the instructions Click to Add The cell beneath that instruction is active awaiting data At this point you can begin entering records into your first table or begin naming your fields and setting them up The field names go in the topmost row the ID field is already created as previously stated and the label Click to Add is atop the column with the active cell If you choose to save your table now right click the Tablel tab and choose Save you can name your table something more useful than Tablel Starting with a template Access provides templates prepared files that work sort of like database cookie cutters for your new database needs You ll find a set of template icons in the Available Templates panel as soon as you open Access As Chapter 1 Getting to Know Access 2010 A Figure 1 16 Pick a template any template and then name your new templat
19. e as a PDF or XPS file and there s one more under the Publish heading You can choose to Publish to Access Services Figure 1 9 reveals the mysteries of Access Services show ing that you can use this command to make your database accessible via the Web or store it at a SharePoint location which would then make it acces sible to others who have the needed rights to view and snag files from that same location CEFF EHHE Volunteer Database Database Access 2007 Microsoft Access om g rie Home Create External Data DatsbaseTools Field Table H Seve File Types Access Services Overview EA Sove Object As i P Share your database with your team fiends or organization with Access Services and G Sove Database as gaj Seve Database As SharePoint BF Open Ise this you want to Save Ol Da close Database A ma ORLA Make your database available through a Web browser and Access Store tables in a central SharePoint location Volunteer Databas Publish Round trip queries forrns reports code and linked tables that are not Web compatible A Databases eccdb re Publish to Access Services ere to watch a wideo de B soatiest man zi G Databases sce Check Web Compatibility i inte You can check your datsbase application for Web compatibility Run Compatibility t9 identify items and setings that are nat supported on the Web Recent Checker New Publish to Access Services Bh id Full URL nei Site Name Help
20. e om amp Home Create External Data Dstebase Tool fields Table a SB Dote amp Time Name amp Capton p fp ata Type Tet Required Sed eZ an 12 Ss F 7 fe m ay E vesno Fa Default value 2 mat matting Unique Mew Tet Number Currency Delete Valigstion ZY More Fields P Field Size 255 1 Setting s gt Indeed a views Ad amp Dele Prope Formatting Field validation AJ Access Objects ae Search p 1D FirstName LastName Address City State Zip HomePhom CellPhone 1 Lori McGowan 123 Main Stree Landisville PA 17638 717 555 1234 717 555 2468 lo E Tebei 2 Mary Margar Kelly 24 Elm Avenue Millersville PA 17611 717 222 4321 717 555 4567 m 3 Ann Talbot 761 Chestnut S Lancaster PA 17602 717 555 9876 an 4 Edgar Anderson 456 independe Elizabethtown PA 17432 717 345 3454 ed 5Thomas Framingham 8701Rosetree Mountjoy PA 17543 717 555 0989 717 555 6789 frd 6 Chris Jamieson 732Krause Roa Denver PA 17456 717 555 0987 7 Kevin Wilson Smytt 150 N 4th Stree Philadelphia PA 19103 215 880 9876 215 555 3456 kw BThomas Framingham 8701Rosetree Mountjoy PA 17543 717 555 0989 717 555 6789 frd 9 Morgan Neville 389 Mission Str Elizabethtown PA 17432 717 555 2098 m New SS Figure 1 1 Table view can be an easy envi ronment for data entry Or not Record I4 lt 1007 10 1 Koo Filter Search lt h 17 Chapter 1 Getting to Know Access 2010 D Figure 1 2 Here s a simple form for entering n
21. e reporting options in Chapters 17 through 19 That s right This chapter plus three more that s four whole chapters are devoted to reporting It must be a big feature in Access What s New in Access 2010 For users of Access 2007 the upgrade to 2010 won t seem like a big deal Yes there are significant improvements and some really great new features but you re won t run smack into the learning curve that users of Access 2003 encountered upon upgrading to 2007 and will still encounter if they move up to 2010 without the interim step If you re coming from 2003 the biggest changes are found in the interface gone are the familiar menus and toolbars of 2003 and prior versions replaced by a ribbon bar divided into tabs that take you to different versions of those old standbys It s a big change and it takes some getting used to In this book however we re going to assume you already got your feet wet with 2007 and aren t thrown by the interface anymore We re figuring you upgraded to 2007 or have played with it enough to feel comfortable diving into 2010 New and improved features So what s new in Access 2010 In the order you re most likely to encounter them here goes The File tab and its associated Backstage View panel are new they replace the Office button and resulting menu in Access 2007 Using the panel on the left shown in Figure 1 6 you make your choices for open ing new files acc
22. es to refer to it is now much more customizable Using the Options menu available through Backstage view you can create new tabs and customize existing ones by adding and reorganizing buttons on the associated Ribbon groups More about this feature in this very chap ter Chapter 1 YY Office Themes make it easy to create visual uniformity within your data base its reports and forms Themes affect colors and fonts and apply consistent graphical elements and are available throughout the Office 2010 suite making it easy to give all your Office creations from Access Word Excel and PowerPoint the same look Pre built database templates available through Backstage view make it easy to build a database with components that make sense for the kind of data you re dealing with Use the Office online templates see Figure 1 7 for the categories and pick a template that suits your needs The secrets to this new feature are found right here in Chapter 1 1 Application Parts are in essence different aspects of your existing data bases that you can recycle for use in new ones Already developed a great form or query Reuse it Learn how in Chapter 7 Chapter 1 Getting to Know Access 2010 D Figure 1 7 Pick a template from any category offered with Office online templates to speed up your database building process pe T All od OF oP I amp Is Volunteer Database Database Access 2007 Microsoft Ac
23. es together to get the reports you need Feel free to sketch your planned database on paper drawing a kind of flow chart with boxes for each table and lists of fields that you ll have in each one Draw arrows to show how they might be related it s sort of like drawing a simple family tree and you re well on your way to a well planned useful database Here s a handy procedure to follow if you re new to the process of planning a database 1 On paper or in a word processing document whichever is more com fortable type the following e A tentative name for your database e A list of the pieces of information you plan on getting from that database on a daily or regular basis Chapter 1 Getting to Know Access 2010 2 Now based on that information create a new list of the actual details you could store List every piece of information you can possibly think of about your cus tomers products ideas cases books works of art students what ever your database pertains to Don t be afraid to go overboard you can always skip some of the items in the list if they don t turn out to be things you really need to know or can possibly find out about each item in your database 3 Take the list of fields that s what all those pieces of information are and start breaking them up into logical groups How Think about the fields and how they work together e If the database keeps track of a library o
24. essing recently used files saving files printing and exiting the application among other momentous decisions It s a lot like ye olde File menu from Access 2003 in terms of what s available Backstage view makes the commands and features that used to live on the File menu easily accessible To get to it just click the File tab 22 Part I Basic Training PCs Figure 1 6 Backstage view new but hauntingly familiar to those who remember the File menu ll id ED HI Volunteer Database Database Access 2007 Microsoft Access oF 8 Mome Creste Externai Data DstsbaseTools Fields Table 8 ied Seve z z Bo Information about Volunteer Database settee atacand Save Object As a z CAUsers Laurie Documents League of Humane Voters Wolunteer Database acedb z GI Save Database As BH Open A m Compact amp Repair Di Close Database ON Help prevent and correct database file problems by using Compact n ane Repair A Volunteer Databas Compact amp view and edit database properties Enea Repair Database A Databases sccdbs B soctiest man Encrypt with Password E g Use a password to restrict access to your detebase Files thet use the 2007 Microsoft Access file format ar later are encrypted E Databases acca Password Recent New Print Save amp Publish Help A Options B t 1 Speaking of that ribbon bar that s new to Access 2003 users the Ribbon as Microsoft lik
25. ew records or review ing existing ones E The mind numbing effect and inherent increased margin for error is espe cially likely when you have lots of fields in a database and the user if work ing in Table view has to move horizontally through the fields A form like the one in Figure 1 2 puts the fields in a more digestible vertical format making it easier to enter data into the fields and to see all the fields at once or only those you want data entered into You can put those fields on the form in the order that ll be easiest for the data entry person and that order would not necessarily have to be the order they appear in the table Alla I is Database Database Access 2007 Microsoft Access gt mw g bom Create External Data DstsbaseTools Fields Table s ay cut d Ascending Wg Selectan gt m ew Total Fie Replace Calibri Detail ju BA a ty copy Z oecenang Padana NE aswe P speting color B 7 U Mew Paste Fitter Refresh ked Fi F tom e Sor Y Toggle Filter aie X Delete EF More 5 seet A Y views Clipbosrd Sort amp Filter Records Find All Access Objects vounteets i votuntecss x Search a Vi f Volunteers Tables E volunteers Forms aulp gt E votunteers Gon McGowan Addre 123 Main Street Landisville a PA 17638 Home Phone 717 555 1234 Cell Phone 717 555 2468 lori domain com Status Active xj Record id a 1or9 b Mn G Searc
26. f books for example perhaps the title publication date publisher ISBN nternational Standard Book Number which is unique for each book price and page count can be stored in one group and author informa tion reviews and lists of other titles by the same author or books on the same topic can be stored in another group These groups become individual tables creating your relational database of books e Figure out what s unique about each record As stated in the previ ous point you need a field that s unique for each record Although Access can create this for you if no unique data exists for each record in your database it s often best to have such a field already in place or create it yourself Customer numbers student num bers book ISBNs catalog numbers serial numbers anything that won t be the same for any two records will do With a big list of fields and some tentative groupings of those fields at the ready and with an idea of which field is unique for each record you can begin figuring out how to use the data 4 Make a list of ways you might use the data including e Reports you d like to create including a list of which fields should be included for each report e Other ways you can use the data labels for mailings product labels catalogue data price lists contact lists and so on 5 List all the places your data currently resides on slips of paper in your pocket on cards in a box
27. h Fom Whew jala g You can find out all about forms in Chapter 7 If your database is large enough that you require help doing the data entry or if it s going to grow over time making an ongoing data entry process likely Access is the tool for you The fact that it offers simple forms of data entry editing is reason enough to make it your database application of choice 18 Part I Basic Training Databases that require special reporting Yet another reason to use Access is its ability to create customized reports quickly and easily Some database programs especially those designed for single table databases known as flat file databases have some canned reports built in and that s all you can do just select a report from the list and run the same report that every other user of that software runs If you re an Excel user your reporting capabilities are far from easy or simple and they re not designed for use with large databases they re meant for spreadsheets and small one table lists Further you have to dig much deeper into Excel s tools to get at these reports Access on the other hand is a database application so reporting is a major up front feature An example In Excel to get a report that groups your data by one or more of the fields in your list you have to sort the database first using the field s to sort the data and then you can create what s known as a subtotal report To
28. hat So you ve got a new database started What do you do now You leaf on over to Chapter 2 where you can find out more about all the tools that Access offers which tools are on screen almost all the time and which ones are specific to the way you chose to dig in and start that database In Chapter 3 you actually begin building a database setting up tables and the fields that give them structure And you ll figure out which tables you need to set up putting that great plan you built in this chapter to work 36 Part Basic Training
29. in another program such as Excel or maybe through a company that sells data for marketing purposes With this planning done you re ready to start building your database The particulars of that process come later in this chapter and in subsequent chapters so don t jump in yet Do pat yourself on the back though because if you read this procedure and applied even some of it to your potential data base you re way ahead of the game and I m confident you ll make good use of all that Access has to offer 16 Part I Basic Training Databases with user forms When you re planning your database consider how the data will be entered 1 If you ll be doing the data entry yourself perhaps you re comfortable working in a spreadsheet like environment known in Access as Table view where the table is a big grid you fill it in row by row and each row is arecord Figure 1 1 shows a table in progress in Table view You decide is it easy to use or can you picture yourself forgetting to move down a row and entering the wrong stuff in the wrong columns as you enter each record 1 You may want to use a form shown in Figure 1 2 a specialized inter face for data entry editing and for viewing your database one record at a time if e Someone else will be handling data entry e Typing row after row of data into a big grid seems mind numbing Ala a Database Database Access 2007 Microsoft Acc
30. ing based on the content of your data based on values found in the records themselves in other words making your reports that much more intuitive So that s a lot of new features and powerful tools you ve now got at your dis posal Other than the Backstage view which you get to see by default when 23 24 Part I Basic Training SS Figure 1 8 The Save and Publish command in Backstage view offers choices for you guessed it saving and publish ing your database B O you start the application however you won t encounter most of them until and unless you want to So for the new user and for the early stages of your database development you ve still got the same solid and dependable tools Access always offered for building tables setting up reports and forms and creating the relationships between your tables that make the database every thing you need it to be Reach out with SharePoint What the heck is SharePoint You may be asking that along with lots of other people who ve been seeing the product name and hearing how it provides access to your Access data from anywhere using desktop applications a Web browser or even your phone Well it s a Microsoft software product that does all that and more helping you manage your documents and col laborate with co workers via the company network Simply click the Save and Publish command in Backstage view see Figure 1 8 and you re on you
31. ou can create any kind of custom report in Access using any or all of your database tables and any of the fields from those tables and you can group fields and place them in any order you want With the Report Wizard you can choose from several preset layouts for your report and it can all be customized row by row column by column 1 Office Themes see the Themes button on the Design tab back in Figure 1 5 apply preset designs that affect fonts colors and other graphical elements automatically Ail ed I Mie n ji taal Database3 Database Access 2007 Micros gt W RS Home Create EtemalData DstsbaseTools Desion Arange Forest Page Setup a9 7 a as g aa A F AN Ecos E Sove amp see 2 H TJ A 2A a Colors xyz f g pe sg Sct N an Aa i E E e B g I ge Mew Themes a ronts a sa 1 insert Page Ado Basting Property Tab ma J Hide Details Numbers 3p Date and Time Fields Sheet Order W vi Themes Grouping amp Totals pi Controls Header Footer Tools All Access Objects Sivotuntecrs a volunteers ib vonunteers Lil Vountees x Property Sheet x search ry ia A E 5 r r m Selecion type Label Tables 2 Report Header Label50 l E Volunteers Format Data Event Other A Forms amp Name Label50 a E vounteers Caption Volunteers Repe I s je Header Visible Yes Reports 2 m Width
32. p of the Available Templates panel to go back to the original set of template categories and to the original set of icons including those listed under the Office com Templates Heading Assuming you have an Internet connection going you ll see the templates Microsoft has to offer Choose one by clicking it and then follow the on screen instructions to utilize it for your new database Note that you can also use the Search Office com for Templates box to look for a particular template just click the arrow button after you ve entered your search terms Again you must be online to use this feature 33 34 Part I Basic Training Figure 1 17 The basic fields for each table are already set up for you along with reports based on the table s fields After you ve downloaded the template or chosen a template that was avail able in your installed copy of Access you can start building data into it There s one major difference from the previous procedure that uses this right side of the Available Templates panel to build a database from scratch naming it choosing a place to store it Instead of having a blank Table1 and nothing else the template gives you pre made tables reports queries and forms in various combinations and numbers all based on the tem plate you chose and they re all set up all you have to do is start entering records Figure 1 17 shows the populated list of database components tha
33. r way to publishing your database to SharePoint which means you can access it from pretty much everywhere including that beach in Maui On second thought some places should probably be declared Data Free Zones Volunteer Database Database Access 2007 Microsoft Access o F g Create External Data Database Tools Fields Table H Seve File Types Save Database As E Sove Object As ay Sane Batabaza Ae m Save Database As Database File Types B Access Database B Open Defeult database format ae A Save Object As lose Database i Close Databas A Access 2002 2003 Dstebase Dae ne Compadble M Volunteer Databas Publish P A DatabaseS scedb B soatiest man ez 2000 Datebese Publish to Access Services oy thacdll be connsadble with G Databases seedy Infe Advanced Recent By Package and Sign Package the database and apply a digital New 3 gw D Print will be compiled int ecutab only file Save amp Publish U Back Up Database Rack up important databases regularly to prevent data lass Help Ai SharePoin Share itto H Options docun B sit Chapter 1 Getting to Know Access 2010 Se Figure 1 9 Get to know your SharePoint options by choosing to Publish to Access Services pe As shown in Figure 1 8 the Save and Publish options include regular old Save Database As to save it in some other format other than as an Access database Save Object As to save your databas
34. t comes with the Web Contacts database template Contacts External Data Detebdase Tools i ar 4 3 Yqyaoaned Mew aste t Refresh views Clipbosrd Sort amp Filter Records Find amp cut Ascending Wg Selecton gt mine E Total Zle Replace Descend H af Swe F Spelling GoTo a Find F Format Painter bo Remove Som VF Toggle Filter ane elete lb Seilet All Access Obje ja Main Seoven 2 Tables aa E comments E contat inte aa i contacten Contacts Database Contact Card Datasheet Report Center Getting Started New Contact ID FirstName Last Name Email Address Forms a New Contaccare ContacDeteils Contact e me r 3 e 3 r ai r m we 3 BG r Defaultmage 5 GettingSterted Main z eCard Namelist ReportCenter x ViceoPiayer a gt 5 3 z Comments J ontact dere I Contacthist E Contactmone JobTitle Search Company Business Phone Form view Ectenced Selection E E Just like a table you build from scratch template based tables need to be populated with data You can change field names see Chapter 5 for direc tions and add and remove fields too After you tweak them until they re appropriate for your database you can begin entering records one field at a time Chapter 1 Getting to Know Access 2010 35 Now w
35. t this point you can give your database a name see the File Name box on the right and click the Create button To take this slowly and step by step always a good idea for your first time read on 1 With Access open and the Available Templates panel displayed click the Blank Database button under the Available Templates heading Chapter 1 Getting to Know Access 2010 3 A panel appears on the right side of the Access window asking you for a name for your new database See Figure 1 14 Blank Database button All db ih 9 amp Volunteer Database Database Access 2007 Microse t Acces HZ Home Create Extemal Date Database Tools Fields Table led swe Availalfle Templates Blank database E save Ovjeas ig B save Datsoase As Home OF oven Cop Close Database 5 b LA volunteer Ehi Mi ivoire Daties Blank Blankweb Recent Sample My templates BD DatibaseS aocdb database database template ternalates A Scitesimdb Office com Templates ice J l Q Sstabased accdb ae z E panpata F TA ra iis 235 a Recent Anet Contacts Inua amp Non profit Projects Tasks auc Print File Name Figure 1 14 saves Publish Databases a The far right He CAUserALawie Decuments panel lets 2 ston youname amp and choose a home for your database eras Blank Database options 2 Replace the default DatabasexX with whatever name you want to use The X in the preceding
36. the volunteer s preferences for activities things they re good at enjoy doing and special skills or resources they can offer such as training homeless dogs or providing space in a barn for rescued horses Because you don t have to fill in every field for each volunteer s record in any table in the database if you don t have a phone number or don t know how many hours someone can work it s okay to leave those fields blank until you ve obtained that information With these three tables in place any type of volunteer or useful contact past current or potential can be entered into the database and only the table s that apply to that person need be populated with data When a potential volunteer becomes a current one relevant data can be entered into the appropriate table s If a potential volunteer never becomes available to help out you can delete that person s name when a prescribed length of time has elapsed or perhaps you can set up a fourth table to hold archived vol unteer records The options are limited only by your needs and intended use of the data Failure to plan Plan to fail If you think carefully about your database how you use your data and what you need to know about your employees customers volunteers donors products or projects whatever you re storing information about you can plan How many tables you ll need Which data will go into which table How you ll use the tabl
37. town PA 17432 717 345 3454 eanderson W Volunteers 16 Thomas Framingham 8701 Rosetree Mount Joy PA 17543 717 555 0989 717 555 6789 framester d I vowunteers ty st 17 Chris Jamieson 732 Krause Roa Denver PA 17456 717 555 0987 cj1234 dom I Yoluriteers List 19 Kevin Wilson Smytt 150 N 4th Stree Philadelphia PA 19103 215 880 9876 215 555 3456 kws098 don Copy Of Volunte amp 22 Thomas Framingham 8701 Rosetree Mountjoy PA 17543 717 555 0989 717 555 6789 framester d E copy of votunt 23 Morgan Neville 389 Mission Str Elizabethtown PA 17432 717 555 2098 mneville3 d New Record H iors gt ib K Untiitered Search 4 1 gt Datasheet View Disa A database file holds all your database components Everything associated with the data is part of the database including 1 All the tables that house your data Y Queries that help you search and use the data Reports that show what your data is and what it means Forms that allow people to view enter and edit data After Access is open you can click the Blank Database button shown in Figure 1 14 to get started Actually the assumption is that you ll start this way so the Blank Database options already appear on the right side of the panel also shown in Figure 1 14 If you ve already clicked one of the other options under Available Templates you can click Blank Database to redis play the panel shown in Figure 1 14 A
38. u pick up in Chapters 9 and 11 Starting a new database from scratch So you don t have a database to open eh Well don t let that stop you To start a new one all you have to do is open Access using any of the tech niques listed earlier in this chapter except the one that starts Access by opening an existing database file which you don t have yet but will now 30 Part I Basic Training Figure 1 13 An existing table ready for records ee MBER we amp All id i gt Volunteer Database Database Access 2007 Microsoft Access om amp J fie home Create Extermal Data Dstrbase Tools Field Table o 2 BR Ac ascending Wg Selectan gt mew amp Total jeReplact Calibri Detsil u 3 J ita copy Z dercenaing Fgacanced WE misme F spelling ccloe B Z U z a Mabe age F Format Painter i e Y Toggle Filter pan X Delete EH More Faen B seet A Y 8 i a Sort amp Filter Records Find Text Formatting 5 ja Volunteers x Volunteers A 1D gt First Name LastName Address City State Zip Home Phone CellPhone Email E Volunteers Table a Lori McGowan 123 Main Stree Landisville PA 17638 717 555 1234 717 555 2468 lori domain E Fine duplicstes 12 Mary Margar Kelly 34 Elm Avenue Millersville PA 17611 717 222 4321 717 555 4567 mm domair EE vonnteerenty 13 Ann Talbot 761 Chestnut S Lancaster PA 17602 717 555 9876 ann domain 15 Edgar Anderson 456 independe Elizabeth
39. y know how to open save and print in say Word you re prob ably ready to do the same things in Access without any difficulty To make sure you re totally Access ready here s a look at the basic proce dures to make sure you have a solid foundation on which to build Opening Access Access opens in any one of several ways So like a restaurant with a very comprehensive menu some people will love all the choices and others will say I can t decide There are just too many options to choose from Now you ll run into situations in which one of the ways is the glaringly best choice hands down that one will be the way to go But what if you ve never heard of it You ll be trying to find my phone number I m unlisted ha so you can give me a piece of your mind So to acquaint you with all your choices so you ll be ready for any situation here are all the ways you can open Access Click the Start menu button in the lower left corner of the screen and choose All Programs Microsoft Office gt Microsoft Access 2010 Figure 1 10 shows my Start menu You may notice items on my menu that don t match yours say programs you may not have don t worry about that Just focus on the Microsoft Office submenu and make your choice from that If you ve recently used Access you ll see it in the list on the left side of the Start menu Just choose Start Microsoft Access 2010 and Access opens
Download Pdf Manuals
Related Search