Thursday, August 4, 2016

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)

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.

Wednesday, May 4, 2016

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

Saturday, March 5, 2016

3.7 Enforcing Referential Integrity

Relationships
Define how the data in tables is related, such as ID fields or name fields in different tables that should match.

A one-to-one relationship exists when two tables have an identical field containing the same information, meaning each record in a table has one matching record in a related table.

A one-to-many relationship exists when each record in a table has one or more matching records in the related table.

An advantage to establishing relationships between tables is that you can enforce referential integrity, which helps prevent inaccurate values from being entered into related fields. Referential integrity requires that the record(s) in one table in a relationship have one or more corresponding records in the other table in the relationship.

ကြ်န္ေတာ္တို႕မွာ ျပည္နယ္၊တိုင္း tblStateRegion Table တစ္ခုရွိမယ္။
1 Kachin
2 Kayah
12 Yangon
ID က 1,2,3...(PK: Number)
StateRegionName က (Text) ေပါ့။
ေနာက္ထပ္ ျပည္နယ္၊တိုင္း Table တစ္ခု။
ျပည္နယ္တိုင္း ID က 1,2,3,4
ေနာက္ လူဦးေရ(က်ား)၊ လူဦးေရ(မ)၊ အက်ယ္အ၀န္း၊ တည္ေနရာ (လတၱီတြဒ္၊ ေလာက္ဂ်ီတြဒ္)၊ စတဲ့ အေၾကာင္းအရာေတြ ပါမယ္။
ဒီ ျပည္နယ္တိုင္း Table ႏွစ္ခုကို ID နဲ႕ relationship လုပ္ပါမယ္။
တစ္ခုခ်င္းဆီ ဆက္စပ္တဲ့အတြက္ ဒါမ်ိဳးကို A one-to-one relationship လို႕ေခၚပါတယ္။
(ဘာေၾကာင့္ Table တစ္ခုတည္း မသံုးသလဲ။)

Township Table တစ္ခုရွိမယ္။
Kachin ရဲ႕ Township ေတြ
1. Myintkyina
2. ...
Kayah ရဲ႕ Township ေတြ
1. ..
2.
Yangon ရဲ႕ Towship ေတြ
ဒီ Township Table ကို Desing စဥ္းစားတဲ့အခါ
Township ID ရွိမယ္။ Township Name ရွိမယ္။
ဘယ္တိုင္း၊ ဘယ္ျပည္နယ္လည္းသိေအာင္ ျပည္နယ္တိုင္းရဲ႕ ID ထည့္မယ္။
TownshipID, TownshipName, StateRegionID ဒီလိုျဖစ္မယ္။
ဒီTownship Table ရဲ႕ Primary Key ကို ဘယ္လိုေပးမလဲ။
TownshipID ကို တစ္ခုနဲ႕တစ္ခု မတူေအာင္ေပးႏိုင္မယ္ ဆိုရင္
ဒီ TownshipID တစ္ခုတည္းကိုပဲ PK ေပးလို႕ရတယ္။
(ကခ်င္အတြက္ ျမစ္ၾကီးနားကိုလည္း ၁ ကစခ်င္တယ္။ ရန္ကုန္အတြက္ ကမာရြတ္ကိုလည္း ၁ ကစခ်င္တယ္။
ဘယ္လိုလုပ္ရလဲ)

ျပည္နယ္တိုင္း Table နဲ႕ Township ကို StateRegionID နဲ႕ ဆက္စပ္ပါမယ္။ one-to-many relationship ျဖစ္ပါတယ္။

If referential integrity were enforced between tblStateRegion and tblTownship, for example, you could not enter a StateRegionID into tblTownship if the ID was not also listed in tblStateRegion.

Enforce Referential Integrity မွာ checked လုပ္ထားရင္ tblStateRegion မွာ မရွိတဲ့ ID ကို tblTownship မွာ ထည့္သြင္းလို႕မရပါဘူး။
Cascade Update Related Fields မွာ checked လုပ္ထားရင္
tblStateRegion မွာ ID နံပါတ္ျပင္တာနဲ႕ tblTownship မွာ ျပင္ျပီးသားျဖစ္ပါတယ္။
Cascade Delete Related Records ကို checked လုပ္ထားရင္
tblStateRegion မွာ Delete လုပ္တာနဲ႕ လုပ္တဲ့ ျပည္နယ္တိုင္း ID နဲ႕ ဆက္စပ္တဲ့ tblTownship က Record အားလံုး Deleted ျဖစ္ပါတယ္။
Download Example DB>>