1 / 14

Microsoft Access – Tutorial 3

Microsoft Access – Tutorial 3. Performing Calculations copy the HighWageAmounts query and paste it name it HighWageAmountsWithBonus and open it in Design View select and drag so that the fields HoursPerWeek, Experience, StartDate, EndDate and Openings are selected

oriana
Download Presentation

Microsoft Access – Tutorial 3

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Microsoft Access – Tutorial 3 Performing Calculations • copy the HighWageAmounts query and paste it • name it HighWageAmountsWithBonus and open it in Design View • select and drag so that the fields HoursPerWeek, Experience, StartDate, EndDate and Openings are selected • delete them by clicking the delete key • delete the remaining fields except EmployerName, PositionTitle and Wage • move these fields into the order above • select the blank field to the right of Wage • click the Build button (magic wand) in the toolbar to open the Expression Builder

  2. Microsoft Access – Tutorial 3 Performing Calculations • HighWageswithBonus query should already be selected • we will add a 2% bonus to the Wage • click Wage in the field list and the Paste button above • type the multiply operator (*) and paste HoursPerWeek after it • now multiply that by .02 • click OK to finish the expression • go to the left of the expression (click Home) and give it the name WeeklyBonus: (you must include the :) • click the Show checkbox, if necessary and run the query

  3. Microsoft Access – Tutorial 3 Performing Calculations • notice that some of the fields have three decimal places • switch back to Design View • right-click the WeeklyBonus field and select Properties • format this field as fixed with 2 decimal places • give it the Caption Weekly Bonus and run it again

  4. Microsoft Access – Tutorial 3 Aggregate Functions (totals, averages…) • create a new query in design view • open the AvailablePositions table and add the Wage field three times • click the totals (sum) button on the toolbar • a new row called Totals appears • click the arrow beside it to see what aggregate functions are available • select Min to display the Minimum Wage • name it by adding Minimum Wage: before Wage in the Field row • create AverageWage and Maximum Wage statistics using the remaining fields • save the Query as WageStatistics

  5. Microsoft Access – Tutorial 3 Record Group Calculations • we want to get the statistics for each position • copy and paste the query WageStatistics and name it WageStatisticsbyPosition and open it in Design View • add PositionTitle to the fields • leave the Total row in the default position of Group By • run the query and you will see statistics for each position • save and close

  6. Microsoft Access – Tutorial 4 Forms and Reports • copy the database NortheastFormsReports onto your disk or Documents and open it • select Forms in the Object Bar • click createform by using wizard • select the AvailablePositions Table • add fields in the following order: • PositionID, PositionTitle, EmployerID, Wage, HoursPerWeek, StartDate, EndDate, Openings, Experience • click Next and look at the various layout options in the Preview Window • choose the Columnar table and click Next • look at the various Styles available • choose Expedition and click Next • give it the name Positions Data and Finish

  7. Microsoft Access – Tutorial 4 Changing the Form’s Autoformat • as with Excel, you can Autoformat the look of items • switch to Design View • click the Autoformat button in the toolbar • click the Options button • you can turn different attributes on and off • select Standard to make the form easier to read • click OK • click the View button to see the form properly • click Save • as usual, you can Navigate through the records with the buttons on the bottom

  8. Microsoft Access – Tutorial 4 Finding Data with a Form • you can use forms to find records with specific data • click in the EmployerID field • click the Find (binoculars) button on the toolbar • the Find and Replace dialog box will appear • type 10145 to find the Positions for the Employer The Clipper Ship Inn • you can also use wildcard (*) characters to search(a list of wildcards can be found on page AC137) • type 1018* in the search • note that it finds data for two employers

  9. Microsoft Access – Tutorial 4 Updating, Adding, Deleting and Printing Records • you can update records by going to the correct record and changing the information • when on a record, you can also delete it by clicking the Delete Record button on the toolbar • you can add new records by clicking the Add Record button • you can print form records • select record 42 • click on the PositionID (Primary Key) filed to select this record • File>Print and check Selected Record(s) to print only this record

  10. Microsoft Access – Tutorial 4 Forms based on Two Tables (Main and SubForms) • go to the Form Wizard • to create a form based on two related tables, you first select the Primary table (Employer) • add EmployerID, EmployerName, ContactFirstName, ContactLastName and Phone • next select the related table (AvailablePositions) and add all the fields • because EmployerID was already included you can remove the field AvailablePositions:EmployerID • click Next

  11. Microsoft Access – Tutorial 4 Forms based on Two Tables (Main and SubForms) • look at the different layouts available • view the data by Employer in a Form with Subforms and click Next • we will use the Datasheet layout; click Next • and the Standard style; click Next • call the main form Employer Positions • click Finish • navigate through some records to explore this view • to widen the SubForm, switch to Design View • drag the Subform window to the right to widen it • switch back to Form View to check it • close and save it

  12. Microsoft Access – Tutorial 4 Creating Reports • we will create a report similar to the form we just made • create a Report using the Wizard • from the Employer table add: • EmployerID, EmployerName, City, StateProv, ContactFirstName, ContactLastName and Phone • then add all the fields from the AvailablePositions table • remove the redundant EmployerID field and click Next • explore some of the Grouping options availabel qand see how the preview changes • remove any grouping options you tried and click Next • note that you can sort information; click Next

  13. Microsoft Access – Tutorial 4 Creating Reports • explore the Layout options • select Outline 2 in the Landscape orientation; click Next • explore the Style options • select Corporate and click Next • name the Report Employers and Positions and Finish • to view the Report click the View button • Zoom in and out as necessary

  14. Microsoft Access – Tutorial 4 Creating Reports • return to Design View • experiment with the Design and Layout of the labels and data • note that you can drag and drop items onto the Report to make Custom Designs • use Insert the Globe illustration into the header before you save

More Related