Three different actions

Common sources and destinations

• Microsoft Excel workbooks.
• Text and CSV files.
• Other Access databases.
• SharePoint lists.
• ODBC databases such as SQL Server, when drivers and permissions are available.
• PDF, Word, Excel, text or email output for reports and data.

Before importing Excel or CSV

1. Give every column a clear heading.
2. Remove title rows, merged cells and empty columns.
3. Keep one type of data in each column.
4. Check dates and leading zeros.
5. Remove totals from the raw data area.
6. Decide whether to create a new table or append to an existing table.
7. Import a small test first.
8. Check rejected records and data types after import.

Linked table warning

A linked table depends on its source path, connection and permissions. If the source file moves, use Linked Table Manager to repair the link.

What is a query?

A query asks the database a question. It can show selected fields and records, calculate values, summarise data or change data.

Create a select query in Design View

1. Choose Create → Query Design.
2. Add the tables or queries that contain the required fields.
3. Check that the join lines are correct.
4. Double-click fields to add them to the design grid.
5. Choose a sort order when needed.
6. Type criteria under the correct field.
7. Clear the Show box for helper fields that should not appear.
8. Choose Run to view the results.
9. Save the query with a clear name such as qryActiveStudents.

Common criteria examples

AND and OR

Criteria on the same row use AND. Criteria on different Or rows use OR.

Calculated field

Type a field name, a colon and an expression in a blank query column.

FullName: [FirstName] & " " & [LastName]
TotalCost: [Quantity] * [UnitPrice]
Balance: Nz([AmountDue],0) - Nz([AmountPaid],0)
Age: DateDiff("yyyy",[DateOfBirth],Date()) - IIf(Format(Date(),"mmdd")<Format([DateOfBirth],"mmdd"),1,0)

Totals query

1. Create a select query.
2. Add the required fields.
3. Choose Totals on the Design tab.
4. Use Group By for category fields.
5. Use Sum, Average, Count, Min or Max for numeric fields.
6. Run and check the result.

Parameter query

A parameter query asks the user for a value. Example criteria:

Between [Enter start date:] And [Enter end date:]
[Enter programme name:]

Use Query Parameters to define the expected parameter data type, especially for dates and numbers.

Crosstab query

A crosstab query summarises data in rows and columns. Example: programme names as row headings, academic years as column headings and Count of students as values.

Types of action queries

Safe action-query method

1. Create a backup.
2. Build the query first as a Select query.
3. Run it and confirm exactly which records are selected.
4. Change it to the required action-query type.
5. Check the target table and field mappings.
6. Run the query once.
7. Check the number of affected records.
8. Validate the final data.

Examples

• Append new students imported from Excel into Students.
• Update Status to “Inactive” for students who completed a programme.
• Delete temporary import records after verification.
• Make a snapshot table for a fixed historical report.

Why use a form?

A form gives users a clear screen for entering, viewing and editing data. It can hide technical fields, show helpful labels and control what users can do.

Create a form with Form Wizard

1. Choose Create → Form Wizard.
2. Select the table or query.
3. Move required fields into Selected Fields.
4. Choose a layout.
5. Choose a name such as frmStudents.
6. Finish and test the form.
7. Open Layout View or Design View to improve it.

Common form controls

Useful form properties

• Record Source
• Default View
• Allow Additions, Allow Edits and Allow Deletions
• Data Entry
• Filter and Order By
• Navigation Buttons and Record Selectors
• Pop Up and Modal
• On Load, Before Update and After Update events

Subform example

A main Student form can show one student. A subform can show that student’s enrolments. Link Master Fields may be StudentID and Link Child Fields may also be StudentID.

What is a report?

A report presents data in a controlled layout. It is normally used for viewing, printing, PDF export or formal output.

Create a report

1. Create and test the query that will provide the report data.
2. Choose Create → Report Wizard.
3. Select the required fields.
4. Choose grouping levels.
5. Choose sorting and summary options.
6. Choose a layout and orientation.
7. Name the report such as rptStudentsByProgramme.
8. Open Print Preview and check page breaks, margins and totals.

Report sections

Useful report expressions

=Count(*)
=Sum([AmountPaid])
=Avg([Mark])
="Page " & [Page] & " of " & [Pages]
=Now()

Labels

Use the Labels wizard to create address labels, product labels or file labels. Test one page before printing many labels.

What is a macro?

A macro is a list of actions that Access performs automatically. It can open forms, apply filters, run queries, show messages, export reports or close objects.

Common macro actions

• OpenForm
• OpenReport
• CloseWindow
• GoToRecord
• SearchForRecord
• ApplyFilter
• SetValue
• RunSQL
• OutputTo
• MessageBox
• QuitAccess

Button macro example

1. Open the form in Design View.
2. Add a Button control.
3. Use the Command Button Wizard or create an Embedded Macro.
4. Choose an action such as OpenReport.
5. Enter the report name and view.
6. Save and test the button.

Navigation form

A Navigation Form gives users one main menu for opening forms and reports. Add only the objects normal users need.

Startup options

• Choose a display form.
• Set an application title and icon.
• Hide the Navigation Pane for normal users only after testing.
• Keep a safe method for developers to open the full database.
• Use AutoExec macro only when the startup task is necessary.

What is SQL?

SQL means Structured Query Language. Access creates SQL behind Query Design. SQL View helps you read, edit and reuse the statement.

Basic SELECT query

SELECT StudentID, FirstName, LastName
FROM Students
WHERE Active = True
ORDER BY LastName, FirstName;

Join two tables

SELECT Students.RegistrationNumber,
       Students.FirstName,
       Students.LastName,
       Programmes.ProgrammeName
FROM Programmes INNER JOIN Students
ON Programmes.ProgrammeID = Students.ProgrammeID;

Totals query

SELECT ProgrammeID, Count(StudentID) AS NumberOfStudents
FROM Students
GROUP BY ProgrammeID;

Parameter query

PARAMETERS [Enter programme ID:] Long;
SELECT *
FROM Students
WHERE ProgrammeID = [Enter programme ID:];

Action-query examples

UPDATE Students
SET Active = False
WHERE CompletionDate Is Not Null;

DELETE FROM TempImports
WHERE ImportStatus = "Rejected";

Access security has several layers

• Windows and folder permissions: control who can read or change the database file.
• Database password and encryption: helps protect an ACCDB file.
• Trusted locations and signatures: control whether macros and VBA can run.
• Application design: limits forms, commands and data users can see.
• Server permissions: apply when Access connects to SQL Server or another server database.

Encrypt with a password

1. Ask all users to close the database.
2. Open the database using the required exclusive-open method.
3. Choose File, Info and Encrypt with Password.
4. Enter a strong password and store it safely.
5. Test opening the database.
6. Keep a secure backup.

Privacy rules

• Collect only data that the work really needs.
• Do not expose personal or medical data in unnecessary reports.
• Restrict folders and backups.
• Remove hidden test data before distribution.
• Do not email an unprotected database containing confidential information.
• Set a data-retention rule and delete data safely when legally allowed.
• Record user actions when accountability is required.

Why split an Access database?

A split database has two files:

• Back end: contains shared tables.
• Front end: contains linked tables, queries, forms, reports, macros and VBA.

Recommended arrangement

• Place the back end in a secure shared network folder.
• Give every user a separate local copy of the front end.
• Do not let all users open one shared front-end file.
• Use a controlled update method when a new front end is released.
• Back up the back end regularly.

Split Database Wizard

1. Create a full backup.
2. Open the database when no other users are connected.
3. Choose Database Tools and Access Database under Move Data.
4. Choose a safe name and location for the back end.
5. Finish the wizard.
6. Test forms, queries and reports.
7. Give each user a local front-end copy.

Record locking

Record locking helps prevent two users from changing the same data at the same time. Choose settings based on the work process and test with real users.

Why files grow

Access database files can keep unused space after records or objects are changed. Compact and Repair reorganises the file and can reduce its size.

Compact and Repair

1. Create a backup.
2. Ask users to close the database.
3. Open Access.
4. Choose File, Info and Compact & Repair Database, or use the Database Tools command available in your version.
5. Wait for the process to finish.
6. Open and test the database.

Maintenance schedule

Corruption prevention

• Use reliable hardware, power and network connections.
• Do not force-close Access during a write operation.
• Do not edit the back-end file directly over unstable remote connections.
• Give users separate front ends.
• Keep enough disk space.
• Install tested Office updates.

Performance checklist

• Index fields used often in joins, criteria and sorting.
• Do not retrieve every field when only a few are needed.
• Use criteria to reduce records before opening forms and reports.
• Avoid many domain aggregate functions such as DLookup in repeated detail rows.
• Use bound forms carefully with very large tables.
• Store pictures and documents outside the database when attachments make the file too large, while keeping secure paths or IDs.
• Split multi-user databases.
• Compact when appropriate.
• Use pass-through queries or server-side views when working with a server database and large data.
• Test performance with realistic data volume.

Performance problem example

Use both programs together

• Store controlled records in Access.
• Use queries to prepare clean data.
• Export or link selected data to Excel for advanced analysis and charts.
• Return approved cleaned data through a controlled import process.

Test before real use

• Try valid, invalid, missing and duplicate values.
• Test every form button.
• Test reports with no records, one record and many records.
• Test different user roles.
• Test simultaneous users when the database is shared.
• Test backup and restore.
• Test on the real screen size, printer and network.

Important documentation

Data-quality dimensions

• Accuracy: values are correct.
• Completeness: required facts are present.
• Consistency: the same rules are followed everywhere.
• Timeliness: data is entered and updated at the right time.
• Uniqueness: duplicate records are controlled.
• Validity: values follow allowed formats and ranges.

Make forms easier to use

• Use plain language and meaningful labels.
• Keep a logical tab order.
• Use sufficient text size and colour contrast.
• Do not use colour as the only way to show meaning.
• Give buttons text labels, not only unclear icons.
• Place instructions near difficult fields.
• Write validation messages that tell the user how to fix the problem.
• Provide keyboard access for frequent actions.
• Keep layouts consistent across forms.
• Avoid flashing or unnecessary moving elements.

Helpful validation message

When an object stops working

1. Read the exact error message.
2. Write down what the user did before the error.
3. Test on a backup or development copy.
4. Check recent changes.
5. Check table fields, relationships and query sources.
6. Compile VBA and check missing references.
7. Compact and Repair only after creating a backup.
8. Restore a known good copy when repair is not safe or successful.

Programmes

Students

Subjects

Enrolments

Create a unique multi-field index on StudentID, SubjectID, AcademicYear and Semester to prevent duplicate enrolment.

Marks

Payments

Relationship test

• Try to add a student with a ProgrammeID that does not exist. Access should reject it.
• Try to add an enrolment for a missing student. Access should reject it.
• Try to enter the same student-subject-year-semester enrolment twice. The unique index should reject it.
• Decide whether deleting a student should ever delete payments or marks. In most real systems, historical records should be retained.

1. Active students with programme name

SELECT Students.StudentID, Students.RegistrationNumber,
       Students.FirstName, Students.LastName,
       Programmes.ProgrammeName
FROM Programmes INNER JOIN Students
ON Programmes.ProgrammeID = Students.ProgrammeID
WHERE Students.Active = True
ORDER BY Students.LastName, Students.FirstName;

2. Final result query

SELECT Enrolments.EnrolmentID, Students.RegistrationNumber,
       Subjects.SubjectCode, Subjects.SubjectName,
       Nz([CourseworkMark],0)+Nz([FinalExamMark],0) AS TotalMark,
       IIf([TotalMark]>=50,"Pass","Fail") AS Result
FROM ((Students INNER JOIN Enrolments
ON Students.StudentID = Enrolments.StudentID)
INNER JOIN Subjects
ON Enrolments.SubjectID = Subjects.SubjectID)
LEFT JOIN Marks
ON Enrolments.EnrolmentID = Marks.EnrolmentID;

3. Total payments by student

SELECT StudentID, Sum(AmountPaid) AS TotalPaid
FROM Payments
GROUP BY StudentID;

4. Student fee balance

Assume the required fee is stored in Programmes as ProgrammeFee. Join Students, Programmes and the total-payments query.

Balance: [ProgrammeFee] - Nz([TotalPaid],0)

5. Parameter query by programme

PARAMETERS [Enter programme code:] Text ( 20 );
SELECT Students.RegistrationNumber, Students.FirstName, Students.LastName
FROM Programmes INNER JOIN Students
ON Programmes.ProgrammeID = Students.ProgrammeID
WHERE Programmes.ProgrammeCode = [Enter programme code:];

frmMainMenu

• Buttons to open Students, Programmes, Subjects, Marks and Payments.
• Buttons to preview reports.
• A button to close the application.
• A clear title and simple instructions.

frmStudents

• Record Source: Students or a suitable student query.
• Use a combo box for ProgrammeID. Store the ID but display ProgrammeCode and ProgrammeName.
• Add a search box for registration number or name.
• Add a subform for Enrolments linked by StudentID.
• Add a button to preview the current student’s details.

frmPayments

• Use a combo box to select a student.
• Default PaymentDate to today’s date.
• Validate AmountPaid as greater than zero.
• Prevent duplicate receipt numbers.
• Show the student’s current total paid and balance in calculated controls or a subform.

Form test

1. Open the form and add a valid record.
2. Try leaving required fields empty.
3. Try entering an invalid date or mark.
4. Try entering a duplicate registration number.
5. Try selecting a related value.
6. Test all buttons and tab order.
7. Close and reopen the form to confirm saved data.

rptStudentsByProgramme

• Record Source: active students with programme query.
• Group by ProgrammeName.
• Sort by LastName and FirstName.
• Show Count of students in the programme footer.
• Show page numbers in the page footer.

rptStudentResults

• Show registration number, student name, subject, coursework, examination, total and result.
• Group by student.
• Use clear labels and mark ranges.
• Do not hide missing marks; show them clearly for checking.

rptPaymentBalances

• Show fee required, total paid and balance.
• Use Currency format.
• Filter or group students with outstanding balances.
• Show a report total for all outstanding balances.

Print-quality checklist

• Correct title and reporting period.
• Organisation name where required.
• No cut-off fields.
• Correct page orientation and margins.
• Repeated page headings.
• Correct totals.
• No confidential data that the audience should not see.

Some shortcuts depend on the Access version, current view and keyboard layout.

Exercise 1: Small contacts database

1. Create a Contacts table with ContactID, FullName, PhoneNumber, Email, Town and Active.
2. Set ContactID as AutoNumber primary key.
3. Prevent duplicate phone numbers.
4. Create a form for data entry.
5. Create a query for active contacts in one selected town.
6. Create a report sorted by name.

Exercise 2: Shop inventory

1. Create Categories, Products, Suppliers and StockTransactions tables.
2. Create relationships.
3. Create a query to calculate stock in minus stock out.
4. Create a low-stock query.
5. Create a product form and transaction subform.
6. Create a stock-value report.

Exercise 3: Library system

1. Create Members, Books, BookCopies and Loans tables.
2. Prevent one physical copy from having two open loans.
3. Create a query for overdue books using Date() and DueDate.
4. Create a loan form.
5. Create overdue and borrowing-history reports.

Exercise 4: Advanced automation

1. Create a split database.
2. Create a local front-end copy.
3. Create a startup menu.
4. Add a button that exports a report to PDF.
5. Add error handling.
6. Create a backup and restore test.

Questions

1. What is a database?
2. What is the difference between a field and a record?
3. Why does every table need a primary key?
4. What is a foreign key?
5. What does referential integrity do?
6. What is the difference between a form and a report?
7. What is a select query?
8. Name four action-query types.
9. Why should an action query be tested as a select query first?
10. Why is a database split for many users?
11. What is normalization?
12. What is the difference between importing and linking?
13. What does Compact and Repair do?
14. Why should users have separate local front ends?
15. When is Excel more suitable than Access?

Answer guide

1. An organised collection of related data.
2. A field is one type of fact; a record is one complete row about one item.
3. It uniquely identifies every record and supports relationships.
4. A field that stores the primary-key value from another table.
5. It prevents invalid relationships and orphan records.
6. A form is mainly for data entry and viewing; a report is mainly for formatted output.
7. A query that displays selected data without changing the stored records.
8. Append, Update, Delete and Make-Table.
9. To confirm exactly which records will be changed.
10. To separate shared tables from user-interface objects and improve multi-user reliability.
11. Arranging data into sensible related tables to reduce repetition and update problems.
12. Import copies data; linking keeps data in the external source.
13. It reorganises the database file, can reduce file size and may repair minor problems.
14. To avoid sharing one front-end file and to improve reliability and speed.
15. When the main work is flexible calculations, modelling, charts or worksheet analysis.