(PDF) Microsoft Access ™ An Essential Guide (Level 1) | djoko chris - .Microsoft Access Complete Book, MS Access Tutorial PDF Book | Free PDF Books
Looking for:
Microsoft access 2013 pdf book free - |
Microsoft access 2013 pdf book free. Microsoft Access 2013 Step by Step ebook - Home
Microsoft access 2013 pdf book free.Microsoft Access 2013™ An Essential Guide (Level 1)
It also covers proper use of event procedures, testing and debugging, and programming advanced Excel features such as PivotTables, PivotCharts, and SmartTags. The chapters are loaded with illustrated hands-on projects and exercises that tell you exactly where to enter code, how to debug it, and then run it.
Features: Explores in great detail the latest version of Excel and all of its features. Covers Office Web Apps. Features: Covers in great detail the latest version of Access and all of its features. Includes more than applied examples and 10 projects.
If you are looking to automate Excel routine tasks, this book will progressively introduce you to programming concepts via numerous illustrated hands-on exercises. More advanced topics are demonstrated via custom projects. From recording and editing a macro and writing VBA code to working with XML documents and using Classic ASP pages to access and display data on the Web, this book takes you on a programming journey that will change the way you work with Excel.
The book provides information on performing automatic operations on files, folders, and other Microsoft Office applications.
It also covers proper use of event procedures, testing and debugging, and guides you through programming advanced Excel features such as PivotTables, PivotCharts, and the Ribbon interface. Features: Contains 28 chapters loaded with illustrated "Hands-On" exercises and projects that guide you through the VBA programming language. Each example tells you exactly where to enter code, how to test it, and then run it.
If you are looking to automate Excel routine tasks, this book will progressively introduce you to programming concepts via numerous, illustrated, hands-on exercises. Includes a comprehensive disc with source code, supplemental files, and color screen captures Also available from the publisher for download by writing to info merclearning. Each example tells you exactly where to enter code, how to test it and then run it. The focus of this book is on basic programming instructions for both Access and previous versions.
If you are looking to automate Access routine tasks, this book will progressively introduce you to programming concepts via numerous illustrated hands-on exercises. With concise and straightforward explanations, you learn how to write and test your programming code with the built-in Visual Basic Editor; understand and use common VBA programming structures such as conditions, loops, arrays, and collections; code a "message box"; reprogram characteristics of a database; and use various techniques to query and manipulate your Access.
The book shows you how you can build database solutions with Data Access Objects DAO and ActiveX Data Objects ADO ; define database objects and manage database security with SQL; enhance and alter the way users interact with database applications with Ribbon customizations and event programming in forms and reports.
Each example tells you exactly where to enter code and how to test it and then run it. You can try this next: 5. Double click on the Hall — it should be added to the next empty column in the query 6. Move down to Criteria: in column 3 and type the name of the required hall - e. Wessex 7. To carry out the query, click on the [Run] button on the far left of the Ribbon or use [View] to move from Design View to Datasheet View or right click on the Query design and choose Datasheet View One difference between a query and a filter is that you can save it directly for future use.
Click on the query's [Close] button 9. Now try re-running the query: With a query, however, you can change the criteria each time you run it by making it a parameter query. The design is very similar to what you have already seen except that instead of setting a fixed criteria, Access asks for the information at run time. Modify the Hall query to do this: 1. With the Hall query still open, click on the [View] button to change to the Design View 2.
Type in a new criteria saying: [Which Hall? Click on the [Run] button or on [View] to switch to Datasheet View 5. When asked the question Which Hall? Wantage 6. Here, however, to run the query again: 7. Click on the [View] button to change to the Design View 8. Click on the [Run] or [View] button again 9. Type in the name of a different hall - e.
This mechanism is used when you look up a book in the Library, for example. More Complex Queries Next, try some more complicated queries. What if you want to have an alternative criteria in a parameter query?
For example, you might want a list of students living in either one hall or another. To do this, you have to set up criteria on two different lines. Click on the [View] button to change to Query Design 2. In the second line of the Criteria: in column 3, type: [or? Click on the [Run] button or on [View] to switch to Datasheet View 4. When asked Which Hall? When asked or? You now have the students from both halls - [Close] the query, saving the new design You have seen how to match values in a query but you can also use criteria such as greater than, less than, not equal to, between one value and another, or matching part of a field.
For example, how do you set up a query to pick out just the female students? The answer is that you can use a special notation called Like. For the female students: 1. Set the Field: in the first column to SortedStudents. Click on the [Run] button to run the query - or switch to Datasheet View To set up a second condition on this subset of data e.
Whereas alternative conditions are set up on different lines, simultaneous conditions must be set up on the same criteria line: 8. Click on the [View] button to move back to Design View 9. Set the Field: in the third column to Tutor Turn off Show: by unticking the box In Criteria: in the third column, top line, type: [Which Tutor?
Click on the [Run] button to run the query - or switch to Datasheet View When asked Which Tutor? One fault with the original example was that the students' names first name and surname were printed in separate columns. In a query you can calculate a new field, joining these together: 1.
Though both appear to work, plus signs can occasionally cause problems. Set the Field: in the second column to Hall and in the third column to StudNo 5. In the fifth column, repeat step 5 but set the Field: to FirstName 7. Click on the [Run] button or on [View] for Datasheet View to run the query 8.
Double click on the dividing lines between the column headings to widen the FullName column 9. To see how it was calculated Open the Students table and click on the [View] button to move to Design View This means it has not created an active hyperlink i. This appears if you right click in the Field: row or, indeed in Criteria: and choose Build You then have access not just to the field names but also to built-in functions.
Even more importantly, a Criteria: can be set to pick up values held on forms. Forms offer a friendly way of viewing the data in that they show a single record at a time. Forms can also be used to display results from queries. They are also used to facilitate data input. A form has already been set up for the students table: 1. The buttons work as they did before, allowing you to move around, add new records, delete records, filter, sort and search etc. Use this form to type in your own information again: 5.
Fill in the fields with your own information, as you did before 7. In the Title field, select the required title using the list arrow 8. In the Hall field, start typing the name of the hall and watch Access select from the list of values 9. The Tutor field also has a list arrow attached Tip: As was mentioned earlier, when you insert a picture file in Access it often appears as an icon.
Fortunately, Access has wizards to do most of the work for you. To modify a form, you view the form design: 1. Click on the [View] button or right click and choose Design View Note that Access provides you with three new tabs on the Ribbon to help you with the design.
You are not going to modify the design of this form - you will see how to later, in Part 2 of these notes.
For the moment: 2. Click on the [View] button to move back to Form View Filter by Form If you want to select a subset of the records using a filter, you can still use the [Selection] button as before. This isn't very convenient, however, if you want to base the filter on information not displayed on the current form. Here, you might want to search for students living in Wessex Hall which isn't on the current record.
You can do this using the [Filter] button as before, but Access also provides a special filter for use with forms.
Click on the [Advanced] button and choose Filter by Form 2. Click on the list arrow attached to the Hall field and select Wessex 3.
Again, click on [Advanced] and choose Filter by Form — your previous filter is shown 6. Click on the list arrow attached to the Title field and select Mr 7. Now click on the Or tab at the bottom of the filter pane for a second filter 8.
Select a Title of Miss and a Hall of Windsor 9. Close the form by clicking on its [Close] button Note that you can also use [Filter by Form] on a table a blank record appears for you to type in the criteria.
This allows you to create and store reports which can then be printed. The mouse button acts as a zoom facility - position the magnifying glass over a particular piece of text and click on the mouse button to magnify it or use Zoom in the bottom right corner of the screen Note: if you use Zoom, this will alter the magnification level setting for the mouse button.
Right click on the blank area of the report and choose Design View Note how similar Report Design and Form Design are - you have matching Design tabs on the Ribbon to draw the various components. Again, you will see how this is done later. Reports can also be viewed in Report View: 5. Click on the [View] button to see the report, right click on the report and choose Report View 6.
You will be typing in a couple of records and then retrieving some more from a file. Designing the Table As much as possible of a table design should be done in advance on paper. Here, however, you will be creating the table on the screen so that you can see the stages as they are implemented. The table you are going to create contains information about the Halls of Residence at the University. In Part 3 of these notes, you will see how to link this information to the data in the students table: 1.
As you enter data into this column, another Add New Field appears and the first column is relabelled Field1.
Access recognises the type of data entered and automatically allocates a Data Type to it e. Click on the [Table Design] button 3. Other reserved words include Date and Year. Note: it's important that you name the fields exactly as specified in these notes for this exercise to work. The Description is optional - type in Name of Hall of Residence if you want 7. However, if you ever choose to decrease it then you could lose some data. This is called truncation. Move to the second row - use the mouse this time Set the Field Name to Warden and the Data Type to Short text You could now fill in the Description and set some Properties but, to speed things up, just leave the settings for this and subsequent fields as they are.
Move to the eighth field Primary Keys help Access uniquely identify each individual record in a table and hence work more efficiently. If a table doesn't contain a unique identifier then Access will ask to set up an ID field for you.
Here, the Halls of Residence table already has a unique field - the name of the hall: 1. Click on the Name field row 1 2. Click on the [Primary Key] button - a key symbol appears in the field indicator column 3. Click on the [View] button to move to Datasheet View 4. Using a datasheet isn't very friendly, however, so try setting up a special data-entry form. A form gives you more control over what data is entered and can be designed to cut down on typing mistakes, as you saw with the students form.
AutoForm is a very quick and easy way to produce a form - it does so at the click of a button: 1. Click on its [Close] button to Close the form - don't save it this time click on [No] as you will be creating a better form using a Wizard next The Form Wizard is equally easy to use and offers you various additional options. If you start up the Wizard without a table or query open or if you wish to base the form on a different set of data then you would select it here.
You are now asked which fields you want to appear on your form here you have the choice - AutoForm gave you them all. As it happens, for a data entry form, you need all the fields: 4. As it stands it is neat and simple, but a little boring. It's similar to the one produced using AutoForm, but here the boxes on the right differ in size. To improve it: 7. Right click on the form or use the arrow attached to the base of the [View] button and choose Design View to move to Design View Forms have three sometimes more sections - a header, footer and the detail.
The data itself is entered into the detail section; the header and footer can be used for titles etc. To add a footer: 8. Position the mouse over the bottom border of the Form Footer - the cursor should change shape to a double-headed arrow.
Hold the mouse button down and drag the border down one unit there's a scale on the left-hand side Label: 9. Click once on the [Label] tool in the Controls group - the mouse pointer now has an A attached Move the pointer into the top left corner of the form footer and click once A small label box is drawn - type your form footer: University of Reading Right click on the label box and choose Size then To Fit You can also amend the design of a form just the position of objects and their colour in Layout View: Double click on the button if you want to paint the format to more than one object then click on it again to turn it off when you have finished.
Finally, click on the Meals combo box and resize it by dragging the right border to the left Note how large the boxes are for the Warden, Phone etc. This is because you left the Field Size at whereas for Name you set it at Entering Data Using the Form You are now going to use the form to enter a couple of data records: 1. Close the Form window by clicking on the [Close] button save any changes to the design 4.
To see the data: 5. Double click on the HoR table in the Objects list then [Close] it again once you have seen the data Importing Data Access allows you to bring in information from other sources - this is called importing. Databases vary on how they bring in the data and on which sort of files they can import. If you have a really large dataset, it is a good idea to try importing a small section to a new table first and only if that works successfully to try to import it all.
Access can import data in various formats, including Dbase another widely-used database and HTML from web pages. Microsoft Excel spreadsheet files can be imported directly. Here, the data has been saved as tab separated values, which is a standard format which any spreadsheet or indeed word processor should be able to produce.
Other basic formats include comma separated values and just plain text. One thing to note when importing a file is that the first line may contain headings - Access has an option to cope with this and can use them for field names. To add the data to an existing table or create a new table from a file: 1. Click on [Browse] to search for the required file 3. Check that the current directory is set to Training on the D: drive if using a lab PC; if not, the file can be downloaded from the WWW via the hyperlink at step 4 4.
Select the file called halls The Import Text Wizard now starts up. This has several stages, as follows: a. Choose whether the data is Delimited e. Double click on the HoR table in the Objects list to see the new records 8. Click in the top left corner of the table then double click on a column heading divider to autofit it 9. Part 3: Relating Tables Together A relational database management system lets you store information in many tables which can then be linked together.
This is particularly useful when you have information which is either heavily duplicated or sparse many records having empty fields. This improves both performance and scalability. Then, in your inventory, you need only record the name of the supplier to find out the other information.
As each supplier will be supplying several pieces of equipment, this avoids massive data duplication. It's the same situation here with the students.
There is no need to store information about Halls of Residence for each student - that can be picked up from the HoR table. You'll see next how this is done. The aim of the exercise is to create a list of students, living in hall, such that you can send a letter to them to their University address. Note: Before doing the next exercise, you will probably need to recreate the FullNames query again. Follow steps on page 13 under the section, Adding New Calculated Fields. Joins can be created between tables when you design the database in a special Relationships window , or made in a query in which case they only apply to that particular query.
Here, the StudNo field in the Names query has been joined to the equivalent field in the students table. The other common field the Hall of Residence is called Hall in the students table but Name in the HoR table and has not been linked. Move the cursor over the Hall field in the students table 4. Hold down the mouse button and drag the field over the Name field in the Halls table 5. Now you need to set up your query: 6. In column 2, set the Field: to Hall from the students table 8.
In column 3, set the Field: to Address1 from the HoR table 9. In column 4, set the Field: to Address2 from the HoR table In column 5, set the Field to Postcode from the HoR table Click on [Run] to run the query - you should find records are displayed if you spelt Wessex and Windsor correctly - the students living in private accommodation are excluded Click on the query window's [Close] button, saving it as Addresses Relationships To save creating relationships each time you create a query, the tables and some existing queries can be linked and held as a hidden object in the database.
This is done through the Relationships dialog box: 1. Drag the Hall field in the students table over the Name field in the Halls table — an Edit Relationships dialog box appears: Various additional options can be set but some of these are only available if Referential Integrity exists.
Referential Integrity can also be used to protect data. His related records e. Click on [Create] to create the link 6. Repeat steps 4 and 5 to join the StudNo in the students table to that in the FullNames query 7. Drag on the bottom of a table to show all the fields, and on its heading to move it around 9. Reports are saved within the database - you can then modify them at some later date if you need to tidy up the layout, for example.
Access gives you the opportunity of designing your own reports from scratch using Design View , however, unless you are an expert, don't even attempt this.
It's much easier to use AutoReport or a Report Wizard and then modify the design if you need to. It should be possible to display all the information on one sideways sheet. To do this: 5. Click in the Name of Hall column to select it then position the mouse cursor on the border between the Name of Hall and Warden columns it becomes a two-headed arrow 8. Hold down the mouse button and drag the border to the left - release the mouse button when the column is properly sized 9.
Repeat steps 7 and 8 on the other columns until all columns fit on the one page To rectify this: Right click on the HoR tab and choose Layout View again Click on the Date placeholder in the Report Header then drag the right border to move it to the far right of the page — the Time placeholder will also move Right click on the HoR tab again and choose Print Preview to check your changes Finally, close the report click on the [Close] button and, when asked, [Save] it as HoR With AutoReport, you have no say in the way the report is produced.
By using the Report Wizard instead, however, you can set various other options as you found with the Form Wizard. You'll look at this next. Using Report Wizards To demonstrate the Report Wizard, you are going to produce a report listing the students by their hall of residence, with the hall address only appearing once for each group of students. Click on the Addresses query in the Objects list 2. The Report Wizard now goes through six steps: a.
Move across the fields you want on your report. Step two allows you to set grouping levels.
Comments
Post a Comment