Ease your way through Microsoft Access!

Access Objects Short Definition

table - store information
query - get selected records
forms - navigate, enter, delete informations
reports- print or preview records
pages - html format
macros - fine tune system using built in commands
modules - visual basic programming for access, create customise functions

What is Primary Key? Its something that is ...
- unique
- required
- link tables


Filtering In Access Tables

Finding Records between 1500 and 2000 in range
1. Right click any of the numbers
2. Choose Filter for:
3. type between 1500 and 2000 in filter for box
4. Press Enter

or Filter for: >=1500 and <=2000
--------------------------------------------------------------
How do i filter date?

1. Right click any of the dates
2. Choose Filter for:
3. type between 1/1/92 and 31/12/92 in filter for box
4. Press Enter

or Filter for : >=1/1/92 and <=31/12/92
or Filter for: *92
-----------------------------------------------
How to use * or ? in finding records?

* Sales -- ends with Sales
Sales * -- begins with Sales
* Sales * -- contains sales
C???? --- finding 5 characters only and starts with C

Finding Not Equal to ...
Filter For: <> Sales
Filter For: ! Sales
Filter For: not sales

----------------------------------------------------
view only staff > 5 years of service Query

(today's date - hire date) / 365 >5 years

1. must be below Hire Date Field Name
2. Criteria: (now()-[Hire Date])/365 >5


simple query

criteria: "usa" <--- hard coded
-------------------------------------------------------------------------------------
Using Search Query

search employee by ID Query
1. go to Query Object
2. Create query in design view
3. Get Employees Table
4. Include all fields
5. from Employee ID column, Criteria: [enter employee Id]
6. save query: Q Search Employee by ID
--------------------------------------------------------------------------------------
search employee by hire date Query
1. go to Query Object
2. Create query in design view
3. Get Employees Table
4. Include all fields
5. from Hire Date Column,
Criteria:
"enter either one syntax"
between [enter start date] and [enter end date]

>=[enter start date] and <=[enter end date]
6. Save Query: Q Search Employee by Hire Date

How do i combine field names together?
(Concatenation)

1. Insert a new column
2. From Field:
Names:[First Name]& " " &[Last Name]

------------------------------------------------------------------------------
calculated Query

Total Amt = Quantity * Unit Price

1. go to Query Object
2. Create a new query design
3. get "Order Details" table
4. include all fields
5. from end of column, Field: TotalAmt:[Quantity]*[Unit Price]
6. Press Enter twice
7. Run query
8. Save query as "Q Total Amt Before Discount"

-----------------------------------------------------------------------
Custom Format for Currency
$ #,##0.00

"RM" #,##0.00

#,##0.00 "Kg"

¥(Atl+157) #,##0.00
-------------------------------------------------------------------------
Find Total Amt After Discount Query
Total Amt = Quantity * Unit Price *(1-Discount)

1. go to Queries Object
2. Create a new query design
3. get "Order Details" table
4. include all fields
5. from end of column,
Field: TotalAfter:[Quantity]*[Unit Price]*(1-[Discount])
6. Press Enter twice
7. Run query
8. Save query as "Q Total Amt After Discount"
-----------------------------------------------------------------------------
create a new query table that showcase only employees
> 5 years of service, show new salary, increment of 5%

Old Salary New Salary
$1000 $1050

1. go to Queries Object
2. Create new query in design view
3. Get Employees Table
4. Include First Name, Last Name, Hire Date, Basic Pay
5. go to end of column, Field: New Pay:[Basic Pay]*1.05
6. Under Hire Date Column, Criteria: (now()-[Hire Date])/365>5
*** get increment from user, then use this
[Basic Pay]*(1+[enter increment])
7. Press Enter twice
8. Save Query as Q New Salary for >5 years of service

--------------------------------------------------------------------------------
Finding Customer without orders

1. Go to Queries Object
2. Click on "New" Button above
3. Choose Find Unmatched Query Wizard
4. Screen1: Choose Customer Table, choose next
5. Screen2: Choose Orders Table, choose next
6. Screen3: Link Customer ID<=> CustomerID, choose next
7. Screen4: Display all fields, choose double arrow(center),choose next
8. Screen5: Name your queries: Customer without orders
9. OK
------------------------------------------------------------------------------------

Why we need forms in access?

- easy to navigate records
- user friendly interface
- delete records easily
- add new records with big "ADD" buttons
- search records, filter records
-----------------------------------------------------------------------------------