Reports and Queries in Databases (Grade 12 NSC Matric Computer Application Technology): Revision Notes

Report Grouping

What is report grouping?

Report grouping is a powerful database feature that allows you to organise and present your data in a structured, easy-to-understand format. Instead of displaying all your records in one long list, grouping arranges them according to specific categories or criteria that make sense for your particular needs.

The main benefits of report grouping include:

• Better organisation: Data becomes much easier to read and analyse
• Professional appearance: Reports look more polished and structured
• Quick comparisons: You can easily compare performance between different groups
• Summary information: You can include totals, averages, and other calculations for each group

Creating a report using grouped data

Microsoft Access provides an excellent tool called the Report Wizard that guides you through creating grouped reports step by step. This wizard asks you a series of questions about how you want your data organised and then builds the report automatically based on your answers.

Using the Report Wizard for grouped reports

The Report Wizard process involves several key stages, each building on the previous one to create your final grouped report.

Step 1: Access the Report Wizard Start by opening your database and navigating to the Create tab in the ribbon. Click on "Report Wizard" to launch the tool that will guide you through the entire process.

Step 2: Select your data source The wizard first asks you to choose which table or query contains the data you want to include in your report. Use the dropdown menu to select from available tables and queries in your database.

Step 3: Choose your fields Next, you'll see two columns: "Available Fields" on the left and "Selected Fields" on the right. Move the fields you want in your report from the left column to the right column by double-clicking them or using the arrow buttons. Remember, you can add fields from multiple tables if needed.

Step 4: Set up grouping The wizard asks if you want to add any grouping levels. Select the field you want to group by (such as "Class" for student data or "Department" for employee data). You can also adjust grouping intervals - for example, grouping dates by month rather than individual days.

Step 5: Configure sorting Choose how you want the data sorted within each group. You might want names in alphabetical order, or dates from newest to oldest. The wizard allows you to set up to four different sorting criteria, each with ascending or descending options.

Step 6: Select layout and orientation Choose how you want your report to look on the page. Options typically include:

• Columnar: Fields arranged in columns (good for detailed records)
• Tabular: Data arranged in a table format (good for comparisons)
• Justified: Fields spread across the page width

You can also choose between Portrait (tall) or Landscape (wide) orientation.

Step 7: Name your report and finish Give your report a meaningful name and decide whether you want to preview it immediately or modify the design. Most of the time, you'll want to preview first to see how it looks.

Group headers and footers

Group headers and footers are special sections that appear at the beginning and end of each group in your report. They're incredibly useful for making your reports more professional and informative.

Understanding headers and footers

Group headers appear at the start of each new group. They typically contain:

• The name or value that defines the group (like "Class A" or "Sales Department")
• Descriptive text explaining what the group represents
• Sometimes subtotals or counts for the group

Group footers appear at the end of each group and often include:

• Summary calculations (totals, averages, counts)
• Blank space to separate groups visually
• Sometimes charts or graphs related to that group's data

Adding custom headers and footers

You can personalise your group headers and footers to make your reports more informative and visually appealing. In Design view, you can add text boxes, labels, and formatting to these sections.

To modify headers and footers:

1. Open your report in Design View
2. Click on the Label tool in the Controls group
3. Click and drag to create a text area in the header or footer section
4. Type your custom text
5. Format the text using the ribbon tools for fonts, colours, and alignment

Group calculations

One of the most powerful features of grouped reports is the ability to include automatic calculations for each group. Access can perform basic mathematical operations on your data, providing valuable insights at a glance.

Types of calculations available

Access offers several built-in calculation options:

• SUM: Adds up all values in a numeric field (great for totals)
• AVERAGE: Calculates the mean value (useful for performance metrics)
• COUNT: Counts how many records are in each group
• MIN: Finds the smallest value in the group
• MAX: Finds the largest value in the group
• Standard Deviation: Shows how spread out the values are
• Variance: Another measure of data spread

Three methods for adding calculations

You can include calculations in your grouped reports using three different approaches, each suitable for different situations.

Method 1: Using the Report Wizard When creating a new report, the Report Wizard includes a "Summary Options" step where you can select which calculations to include. This is the easiest method for new reports, as it sets everything up automatically.

Method 2: Using the Totals option For existing reports, you can add calculations using the Totals button in the Design tab ribbon. This method gives you access to all the calculation types and is perfect for reports that already exist but need summary information added.

Method 3: Manual textbox entry For maximum control, you can manually create textboxes and enter formulas yourself. This method requires more technical knowledge but gives you complete flexibility over where calculations appear and how they're formatted.

Manual calculation formulas

When adding calculations manually, you need to understand the proper formula syntax. All formulas in Access start with an equals sign (=) and use square brackets around field names.

Basic formula structure: $\text{=FUNCTION([FieldName])}$

Common examples:

• $\text{=SUM([Mark])}$ - adds up all marks in the group
• $\text{=AVERAGE([Hours])}$ - calculates average hours worked
• $\text{=MAX([Capacity])}$ - finds the highest capacity value
• $\text{=COUNT([StudentID])}$ - counts how many students in the group

Important placement considerations: The location of your calculation textbox affects what data it calculates:

• In group header or footer: Calculates only for that specific group
• In report header or footer: Calculates for all records in the entire report

Summary options in practice

When you access Summary Options during report creation, you'll see a dialogue box where you can select which fields to calculate and which calculation types to apply. You can choose multiple calculation types for the same field - for example, both SUM and AVERAGE for a marks field.

The "Show" section lets you decide whether to display:

• Detail and Summary: Shows all individual records plus group calculations
• Summary Only: Shows only the group calculations, hiding individual records

This flexibility means you can create anything from detailed reports with subtotals to executive summaries showing only the key figures.