Microsoft Access Templates and Database Examples

Source:http://access-templates.com/

Before using data Check filter

Excel (pivot) or Access or other app,
there might be filter option.
So don't forget to check filter before using your data.

Access Basic (I)

Content
1.1 Starting Access
1.2 Opening an Existing Database
1.3 Moving Around in Access
1.4 Opening an Object in Datasheet View
1.5 Opening an Object in Design View
1.6 Switching Between Datasheet and Design View
1.7 Navigating in Datasheet View
1.8 Closing an Object

2.1 Creating a Database
2.2 Creating a Table
2.3 Adding Fields to Tables
2.4 Adding Records
2.5 Editing Records
2.6 Moving Fields
2.7 Deleting Records

3.1 Formatting a Table
3.2 Sorting Records in a Table
3.3 Finding Records in a Table
3.4 Applying a Filter to a Table
3.5 Removing a Filter
3.6 Establishing a Relationship
3.7 Enforcing Referential Integrity
3.8 Creating Subdatasheets
3.9 Importing Records from an External Source
3.10 Copying Records into a Table

4.1 Creating a Query Using the Simple Query Wizard
4.2 Specifying Criteria in a Query
4.3 Creating a Query in Design View
4.4 Creating a Calculated Field
4.5 Creating a Multiple-Table Query


5.1 Creating a Form Using AutoForm
5.2 Creating a Form Using the Form Wizard
5.3 Adding Controls to a Form
5.4 Modifying Control Properties
5.5 Resizing Controls on Forms
5.6 Creating a Form in Design View
5.7 Entering Records into a Form

6.1 Creating a Report Using AutoReport
6.2 Creating a Report Using the Report Wizard
6.3 Adding a Control to a Report
6.4 Creating a Report in Design View
6.5 Formatting a Report
6.6 Creating Calculated Controls

Learning Access Database with Northwind 2007 sample database (1)

Download Example: https://1drv.ms/u/s!ApI9pTaAYa6fiBOgkO4TQVE-bMBa

I. Basic Workflow

a) Startup/Login

When you open the Northwind 2007 sample first time you may see ‘Startup Screen’ or ‘Login Dialog’.

-You have to click ‘Enable Content’ and will see ‘Login Dialog’ form.

Next time, you open the Database, you will always see ‘Login Dialog’.

Q. How to control this event?

Ans: This is why the system has ‘AutoExec’ macro.

Let’s learn this ‘AutoExec’ macro.

1.       Go to Macro,

2.       Right Click ‘AutoExec’

3.       Select Design View and Click.

You see two If statements,

1.       IF not trust app THEN OPEN ‘Startup Screen’

2.       IF trust app THEN OPEN ‘Login Dialog’

When you click Login button in the ‘Login Dialog’ form, it will be closed and ‘Home’ form will appear.

To see this Embed Macro,

1.       Open ‘Login Dialog’ design view.

2.       Go to Login button properties, OnClick (Event) and click …

System uses the following logic:

1.       Check Combobox (DropDown list) of the Employee and

IF this is not empty

2.       Close the Login Dialog form and

3.       Open ‘Home’ form

IF there is no Employee name

-          Display Messagebox.

Basic Access (II)

Basic Access (II) Ref: Microsoft Office Support

2. Design tables

1-Introduction to tables
https://support.office.com/en-us/article/Introduction-to-tables-78ff21ea-2f76-4fb0-8af6-c318d1ee0ea7

2-Guidelines for naming fields, controls, and objects
https://support.office.com/en-us/article/Guidelines-for-naming-fields-controls-and-objects-120c27fa-7ae1-4182-9baa-dbd183179cc3

3-Guide data entry by using input masks
https://support.office.com/en-us/article/Guide-data-entry-by-using-input-masks-b69187b0-e01c-4a4d-bcbe-e3f714d0c62d

4-Normalize your data using the Table Analyzer
https://support.office.com/en-us/article/Normalize-your-data-using-the-Table-Analyzer-8edbb763-5bab-4fbc-b62d-c17b1a40bbe2

5-Add or change a table’s primary key
https://support.office.com/en-us/article/Add-or-change-a-tables-primary-key-0dde1a80-d913-4d9b-a1b3-061f24ccd991

6-Restrict data input by using a validation rule
https://support.office.com/en-us/article/Restrict-data-input-by-using-a-validation-rule-6c0b2ce1-76fa-4be0-8ae9-038b52652320

7-Guide to table relationships
https://support.office.com/en-us/article/Guide-to-table-relationships-55b8db2c-9480-4269-b1bb-f6ec09623dfd

8-Add a calculated field to a table
https://support.office.com/en-us/article/Add-a-calculated-field-to-a-table-34da23a1-4ac3-4daf-ac11-3147ebb2c2e9

9-Data types for Access desktop databases
https://support.office.com/en-us/article/Data-types-for-Access-desktop-databases-df2b83ba-cef6-436d-b679-3418f622e482

10-Create and use an index to improve performance
https://support.office.com/en-us/article/Create-and-use-an-index-to-improve-performance-aadbeff4-3373-4b60-8656-ed1ef73a3ee5

3. Understand queries

1-Introduction to queries
https://support.office.com/en-us/article/Introduction-to-queries-a9739a09-d3ff-4f36-8ac3-5760249fb65c

2-Examples of using dates as criteria in Access queries
https://support.office.com/en-us/article/Examples-of-using-dates-as-criteria-in-Access-queries-aea83b3b-46eb-43dd-8689-5fc961f21762

3-Examples of Access query criteria
https://support.office.com/en-us/article/Examples-of-Access-query-criteria-0c7e9394-c485-454f-bc00-3bd3ec617805

4-Create and run an update query
https://support.office.com/en-us/article/Create-and-run-an-update-query-9dddc97c-f17d-43f4-a729-35e5ee1e0514

5-Update data by using a query
https://support.office.com/en-us/article/Update-data-by-using-a-query-f01eb7a3-a673-4edd-a425-f6769d2bb717

6-Use parameters to ask for input when running a query
https://support.office.com/en-us/article/Use-parameters-to-ask-for-input-when-running-a-query-c2806d3d-d500-45a8-8507-ec6af351b6ed

7-Create a select query
https://support.office.com/en-us/article/Create-a-select-query-229f8dba-8e8b-4f21-8bec-1e734d7f0c94

8-Create a make table query
https://support.office.com/en-us/article/Create-a-make-table-query-787763ba-a9e4-42c0-b09f-98c01014808e

9-Create and run a delete query
https://support.office.com/en-us/article/Create-and-run-a-delete-query-6da65fe1-0fc7-4a64-8ef0-c052cd4c3ec5

10-Use an append query to add records to a table
https://support.office.com/en-us/article/Use-an-append-query-to-add-records-to-a-table-f9523ff0-98b2-4c62-a02f-1a32f5a9572c

4. Create forms

1-Create an Access form
https://support.office.com/en-us/article/Create-an-Access-form-5d550a3d-92e1-4f38-9772-7e7e21e80c6b

2-Create a list of choices by using a list box or combo box
https://support.office.com/en-us/article/Create-a-list-of-choices-by-using-a-list-box-or-combo-box-70abf4a9-0439-4885-9099-b9fa83517603

3-Add a subform
https://support.office.com/en-us/article/Add-a-subform-c59387f9-73aa-4082-82cb-5d29ef04299f

4-Set default values for fields or controls
https://support.office.com/en-us/article/Set-default-values-for-fields-or-controls-99508d03-b28b-4057-9652-dac1c4c60d86

5-Create a navigation form
https://support.office.com/en-us/article/Create-a-navigation-form-32e50477-3039-4503-9cd6-210a1a836007

6-Sum or count values on a datasheet with a Total row
https://support.office.com/en-us/article/Sum-or-count-values-on-a-datasheet-with-a-Total-row-d8393172-4ff4-4e54-8994-f6a0417b80d3

7-Filter data in a desktop database
https://support.office.com/en-us/article/Filter-data-in-a-desktop-database-4dbc43d7-cce2-4f34-8f73-eead5073ce16

8-Create a form by using the Form Wizard
https://support.office.com/en-us/article/Create-a-form-by-using-the-Form-Wizard-3184c37b-69c6-4646-ab03-61205841fcca

9-Create a form by using the Form tool
https://support.office.com/en-us/article/Create-a-form-by-using-the-Form-tool-db533230-4f33-449d-a7dc-639af724db44

10-Why does Access want me to enter a parameter value?
https://support.office.com/en-us/article/Why-does-Access-want-me-to-enter-a-parameter-value-0e71a1dd-e498-49a1-9e2e-356538d226ff

5. Create reports

1-Introduction to reports in Access
https://support.office.com/en-us/article/Introduction-to-reports-in-Access-e0869f59-7536-4d19-8e05-7158dcd3681c

2-Create a grouped or summary report
https://support.office.com/en-us/article/Create-a-grouped-or-summary-report-f23301a1-3e0a-4243-9002-4a23ac0fdbf3

3-Set print options
https://support.office.com/en-us/article/Set-print-options-bc8a0421-c6b7-4a5a-a813-90273f692517

4-Insert a page break control in an Access report
https://support.office.com/en-us/article/Insert-a-page-break-control-in-an-Access-report-7ce16f0a-7e13-4d39-a197-846ae155cf9a

5-Video: Use conditional formatting on reports
https://support.office.com/en-us/article/Video-Use-conditional-formatting-on-reports-06361278-d541-440c-8044-3a1ce519f9e2

6-Set the record source for a report
https://support.office.com/en-us/article/Set-the-record-source-for-a-report-9a28af3d-442e-4044-823f-5debb119111c

7-Filter data in a report
https://support.office.com/en-us/article/Filter-data-in-a-report-d4da9e93-6527-4ad7-9ae1-e0f2fe424eeb

8-Print a report
https://support.office.com/en-us/article/Print-a-report-ec3a49d5-5656-41f2-9aea-b27a03b78e58

6.6 Creating Calculated Controls

Download PDF>>

6.4 Creating a Report in Design View

Download PDF>>