BAA3032: Week 17 - PowerPoint PPT Presentation

1 / 95

About This Presentation

Title:

BAA3032: Week 17

Description:

BAA3032: Week 1-7. Lect 2: Database Programming ... Scrollbars. Allow additions. Allow edits. Event on load Docmd.maximize. Text box properties ... – PowerPoint PPT presentation

Number of Views:23

Avg rating:3.0/5.0

Slides: 96

Provided by: notesU

Category:

more less

Transcript and Presenter's Notes

Title: BAA3032: Week 17

1
BAA3032 Week 1-7
2
BAA3032 Week 9-15
3

Assalamualaikum wbt
/ Good day

4
Last week practice

Learn to create form that has buttons for record
operation, record navigation form operation.
Learn to create table, relationship and form for
1-to-many records relationship.
REMINDER TEST NO.1 WEEK 6 PROJECT BASED ON
TABLE DEFINATION, FORM DEFINATION, RELATIONSHIP.
Every screen when creating form/table/relationship
is important to understand.
Project Assign in Week 4 after customization and
due in week 6.

5
Lessons for Week 3

Learn to create table, relationship and form for
1-to-1 records relationship.
Learn to create table, relationship and form for
many-to-many record relationship.

6
Lesson 1 - Lecture 1

Learn to create table, relationship and form for
1-to-1 record relationship.

7
Target Skills

To create 1-to-1 records data entry form.

Scenario When there sensitive info. Or may add
other data later and not touch the current info.
8
The Steps

Create table Bank Account
Create relationship 1-1 link between table
Project and table Bank Account.
Create form Form Bank Account - type columnar.

9
The Table
10
The Relationship
11
The Form
12
Lessons for Week 3

Learn to create table, relationship and form for
1-to-1 records relationship.
Learn to create table, relationship and form for
many-to-many record relationship.

13
Lesson 2 - Lecture 2

Learn to create table, relationship and form for
many-to-many record relationship.

14
Target Skills

To create many-to-many records data entry form.

15
Target Skills

To create many-to-many records data entry form.

16
Table Consultants
17
Table Projects
18
The Steps

Create table PNC(Assume Created table
Consultant)
Create relationship 1-to-many between table
Projects and table PNC1.
Create relationship 1-to-many between table
Consultants and table PNC1.
Create form based on table Projects, PNC and
Consultants.

19
The Table
20
The Table
21
The Relationship
22
The Form
23
The Form
24
Additional combobox
25
Practice Week 4

Create table, relationship and form for 1-to-1
records relationship.
Project Information Bank Account
Consultants Status
Staffs Password and Picture
Inventory Location and Picture
Create table, relationship and form for
many-to-many records relationship.
Project Information Consultants
Project Information Staffs
Inventory Suppliers

Thank you..

27
Last week practice

Learn to create table, relationship and form for
1-to-1 records relationship.
Learn to create table, relationship and form for
many-to-many records relationship.

28
Lessons for Week 4

Learn to customized - create menu system, create
form links and customized form.
Learn to customized - create menu system, create
form links and customized form.

29
Lesson 1 - Lecture 1

Learn to customized - create menu system, create
form links and customized form.

30
Target Skills

To customized forms and create the information
system.
Project Listing
Project Information
Payments
Bank Account
Find
Filter
Close / Quit
Consultants / Local Authority etc..
Menu
Startup

31
Built Event On Load DoCmd.Maximize
Popup True Modal True Allow additions
False Navigation buttons False
32
Properties

List of form properties to be customized.
Popup no menu
Modal require close it to switch to other
windows.
Record selectors hide record arrow
Navigation buttons
Scrollbars
Allow additions
Allow edits
Event on load Docmd.maximize
Text box properties
Enabled
Locked

Combo box require create table.

Record selector
Navigation buttons
34
Enabled ? Locked ? For text box
Allow additions
35
(No Transcript)
36
(No Transcript)
37
MyI InputBox("Enter state ('reset to all
states'", , "") If MyI "" Then Exit Sub
If MyI "" Then Me.FilterOn
False Else Me.Filter "State '"
MyI "'" Me.FilterOn True End If
38
Validation

Private Sub VALUE_BeforeUpdate(Cancel As Integer)
If VALUE.VALUE lt 1000 Then
MsgBox Is it number less than 1,000.00
CancelTrue
End If
End Sub
Private Sub END_DATE_BeforeUpdate(Cancel As
Integer)
If END_DATE lt START_DATE Then
MsgBox "Date less than start date?"
Cancel True
End If
End Sub

39
Calculated Fields

1. Add field in table Tax
2. Add field in form Tax
3. Add code
Private Sub VALUE_AfterUpdate()
TAX.VALUE VALUE.VALUE
End Sub

Dim MyT1, MyT2 as String
Private Sub Text2_AfterUpdate()
MyT1 Text2.Text
End Sub
Private Sub Text4_AfterUpdate()
MyT2 Text4.Text
End Sub
Private Sub Command0_click()
Dim MyDb As Database
Dim MyRs As Recordset
Set MyDb CurrentDb
Set MyRs MyDb.OpenRecordset("SELECT FROM
Users WHERE Username '" MyT1 "' and
Password '" MyT2 "'")
If MyRs.RecordCount 0 Then
MsgBox "Wrong username or password"
End If

41
Menu Tools / startup
42
Practice Week 5

Recreate form shown in week 4
Create information system for-
Consultants
Inventory
Daily Reports
Staffs

43
Project No.1

Create Project Information System. Link related
tables below.
Projects
Payment Details
Progress Details
Bank Account
Staffs
Consultants
Inventory
Create Information System that has form based on
single records, 1-1, 1-many, many-many
relationship. Project based on your selection.

44
Project No.1

Create Project Information System. Link related
tables below. (5)
Projects
Payment Details
Progress Details
Bank Account
Internal Staffs
Consultants - Staffs
Inventory Received Consumed
Create Information System that has form based on
single records, 1-1, 1-many, many-many
relationship. Project based on your selection.
Show relationship and flow of information. (5)
1) Bank Accounts 2) Students Info 3) Staffs info
4) Library system 5) Faculties 6) Sales 7)
Minute of Meeting 8) Water Bills 9) Phone Bills
10) Electric Bills 11) Monthly Spending 12)
BAA3032 Markings 13) List of Hotels 14) List of
Restaurants 15) List of Universities 16) List of
Journals 17) List of Letters in/out 18) Site
Daily Reports 19) Cars Registration Info. 20)
Pipe Information System

Thank you..

46
Last week practice

Recreate form shown in week 4
Create information system for-
Consultants
Inventory
Daily Reports
Staffs

47
Lessons for Week 5

Introduction to SQL
SQL Data Retrieval
SELECT, WHERE, ORDER BY
WHERE String, Numbers, Date, Logical AND, OR,
NOT.
SQL Data Summary (Aggregate functions)
SQL Data Manipulation

48
Lesson 12 - Lecture 1

Introduction to SQL
SQL Data Retrieval
SELECT, WHERE, ORDER BY
WHERE String, Numbers, Date, Logical AND, OR,
NOT.

49
SQL

SQL (commonly expanded to Structured Query
Language see History for the term's derivation)
is the most popular computer language used to
create, modify, retrieve and manipulate data from
relational database management systems.

50
SQL Data Retrieval

SELECT is used to retrieve zero or more rows from
one or more tables in a database. In most
applications, SELECT is the most commonly used
Data Manipulation Language command. In specifying
a SELECT query, the user specifies a description
of the desired result set, but they do not
specify what physical operations must be executed
to produce that result set. Translating the query
into an efficient query plan is left to the
database system, more specifically to the query
optimizer.
Commonly available keywords related to SELECT
include
FROM is used to indicate from which tables the
data is to be taken, as well as how the tables
join to each other.
WHERE is used to identify which rows to be
retrieved
ORDER BY is used to identify which columns are
used to sort the resulting data.

51
SQL Data Retrieval - general

SELECT FROM PROJECTS
WHERE STATE 'MELAKA' ORDER BY 'File No'
Me.RecordSource "SELECT FROM PROJECTS
WHERE STATE 'MELAKA' Order by File No"
This example retrieves the records from the
projects table that have a state field which is
equal to Melaka. The result is sorted
alphabetically by File No. The asterisk () means
to show all columns of the projects table.
Alternatively, specific columns could be named.

52
(No Transcript)
53
SQL Data Retrieval - string

SELECT FROM PROJECTS
WHERE STATE 'MELAKA' ORDER BY 'File No'
Me.RecordSource "SELECT FROM PROJECTS
WHERE STATE 'MELAKA' Order by File No"
VARIABLE AS PART OF SQL STRING
MyI Inputbox(Type in state)
If MyI then Exit sub
Me.RecordSource "SELECT FROM PROJECTS
WHERE STATE MyI ' Order by File No"

54
SQL Data Retrieval - string

Asterisk is for pattern matching.
SELECT FROM PROJECTS
WHERE STATE LIKE P' ORDER BY 'File No'
Asterisk is for pattern matching.SELECT FROM
PROJECTS
WHERE STATE LIKE K' ORDER BY 'File No'

55
SQL Data Retrieval - Numbers

SELECT FROM PROJECTS
WHERE VALUE gt 10000 ORDER BY 'File No'
Me.RecordSource "SELECT FROM PROJECTS
WHERE VALUE gt 1000 Order by File No"
VARIABLE AS PART OF SQL STRING
MyI Inputbox(Type in VALUE)
If MyI then Exit sub
Me.RecordSource "SELECT FROM PROJECTS
WHERE VALUE gt MyI Order by File No"

56
SQL Data Retrieval - Date

SELECT FROM PROJECTS
WHERE End Date gt 1-JUL-2006 ORDER BY 'File
No'
Me.RecordSource "SELECT
FROM PROJECTS
WHERE START DATEgt1/13/2006
VARIABLE AS PART OF SQL STRING
MyI Inputbox(Type in VALUE)
If MyI then Exit sub
Me.RecordSource "SELECT FROM PROJECTS
WHERE START DATEgt MyI Order by File
No"

57
SQL Data Retrieval Logical - AND

SELECT FROM PROJECTS
WHERE STATE 'MELAKA' AND
VALUE gt 1000 ORDER BY 'File No'
SELECT FROM PROJECTS
WHERE STATE 'MELAKA AND
End Date gt 1-JUL-2006 ORDER BY 'File No

58
SQL Data Retrieval Logical - OR

SELECT FROM PROJECTS
WHERE STATE 'MELAKA' OR
STATE PERLIS' ORDER BY 'File No'
SELECT FROM PROJECTS
WHERE STATE 'MELAKA and End Date gt
1-JUL-2006 ORDER BY 'File No

59
SQL Data Retrieval Logical - NOT

SELECT FROM PROJECTS
WHERE NOT STATE 'MELAKA'
ORDER BY 'File No
SELECT FROM PROJECTS
WHERE NOT (STATE 'MELAKA' AND
VALUE gt 1000) ORDER BY 'File No'

60
SQL Data Retrieval Order by

SELECT FROM PROJECTS
ORDER BY STATE ASC
SELECT FROM PROJECTS
ORDER BY VALUE
SELECT FROM PROJECTS
ORDER BY STATE, VALUE
--------------------------------------------------
-----------------------------------------
SELECT FROM PROJECTS
ORDER BY VALUE ASC
SELECT FROM PROJECTS
ORDER BY VALUE DESC
SELECT FROM PROJECTS
ORDER BY STATE ASC, VALUE DESC

61
Lessons for Week 5

Introduction to SQL
SQL Data Retrieval
SELECT, WHERE, ORDER BY
WHERE String, Numbers, Date, Logical AND, OR,
NOT.
SQL Data Summary (Aggregate functions)
SQL Data Manipulation

62
Lesson 34 - Lecture 2

SQL Data Summary (Aggregate functions)
SQL Data Manipulation

63
SQL Data Summary

SQL Aggregate Functions
Using the SQL aggregate functions, you can
determine various statistics on sets of values.
You can use these functions in a query and
aggregate expressions in the SQL property of a
QueryDef object or when creating a Recordset
object based on an SQL query.
Avg Function
Count Function
First, Last Functions
Min, Max Functions
StDev, StDevP Functions
Sum Function
Var, VarP Functions

64
SQL Data Summary Sum

SELECT SUM(VALUE) AS JUM
FROM PROJECTS

65
SQL Data Summary Sum

Dim MyDB As Database
Dim MyRs As Recordset
Set MyDB CurrentDb
Set MyRs MyDB.OpenRecordset("SELECT Sum(Value)
as Jum FROM PROJECTS")
MsgBox MyRs(Jum")
MyRs.Close
MyDB.Close

66
SQL Data Summary Sum

SELECT SUM(VALUE) AS JUMKONTRAK FROM PROJECTS
SELECT COUNT(VALUE) AS BILKONTRAK FROM PROJECTS
SELECT SUM(VALUE) AS JUMKONTRAK, COUNT(VALUE) AS
BILKONTRAK FROM PROJECTS

67
SQL Data Summary Sum where

SELECT SUM(VALUE) AS JUMKONTRAK FROM PROJECTS
WHERE STATE MELAKA
SELECT COUNT(VALUE) AS BILKONTRAK FROM PROJECTS
WHERE STATE MELAKA
SELECT SUM(VALUE) AS JUMKONTRAK, COUNT(VALUE) AS
BILKONTRAK FROM PROJECTS WHERE STATE MELAKA

68
SQL Data Summary Sum

SELECT COUNT(VALUE) AS BILKONTRAK,SUM(VALUE) AS
JUMKONTRAK , MAX (VALUE) AS MAKKONTRAK,
MIN(VALUE) AS MINKONTRAK FROM PROJECTS

69
Lessons for Week 6

Test No.1
Review Test No.1

70
Lessons for Week 7

SQL Data Manipulation
Update
Delete
Insert
Reports
Listing
Listing with summary
Group Listing
Group Listing with summary
Group Listing with summary grand summary.

71
SQL Data Manipulation - Update

Creates an update query that changes values in
fields in a specified table based on specified
criteria.
Syntax
UPDATE table SET newvalue WHERE criteria
UPDATE is especially useful when you want to
change many records or when the records that you
want to change are in multiple tables.
You can change several fields at the same time.
The following example increases the Order Amount
values by 10 percent and the Freight values by 3
percent for shippers in the United Kingdom
UPDATE PROJECTSSET TAX VALUE 0.1,UPDATED
1-JUL-2006WHERE ShipCountry 'UK'

72
SQL Data Manipulation - Update

UPDATE PROJECTSSET TAX VALUE 0.1
UPDATE PROJECTS SET TAX VALUE 0.2
WHERE State Melaka
UPDATE PROJECTS SET TAX VALUE 0.3
WHERE ID 5

73
SQL Data Manipulation - Update

Dim MyDB As Database
Dim MyRs As Recordset
Set MyDB CurrentDb
MyDB.Execute (UPDATE PROJECTS SET TAX VALUE
.2")
MyRs.Close
MyDB.Close

74
SQL Data Manipulation - Update

UPDATE PROJECTSSET TAX VALUE 0.1
UPDATE PROJECTSSET TAX VALUE 0.2,UPDATED
1-JUL-2006
UPDATE PROJECTSSET TAX VALUE 0.3,UPDATED
7-JUL-2006
WHERE STATE MELAKA
UPDATE PROJECTSSET STATUS BIDDING PROCESS
WHERE STATUS PRE-CONSTRUCTION

75
SQL Data Manipulation Delete

Creates a delete query that removes records from
one or more of the tables listed in the FROM
clause that satisfy the WHERE clause.
Syntax
DELETE table. FROM table WHERE criteria

76
SQL Data Manipulation - Update

DELETE FROM PROJECTSWHERE ID 4
DELETE FROM PROJECTSWHERE State Pahang

77
SQL Data Manipulation - Update

Dim MyDB As Database
Dim MyRs As Recordset
Set MyDB CurrentDb
MyDB.Execute (UPDATE PROJECTS SET TAX VALUE
.2")
MyRs.Close
MyDB.Close

78
Practice Week 8

Recreate commands using SQL as shown in week 7
Create commands using SQL as shown in week 7 for
1-1 form Project Payments

Thank you..

80
Lessons for Week 7

SQL Data Manipulation
Update
Delete
Insert
Reports
Listing
Listing with summary
Group Listing
Group Listing with summary
Group Listing with summary grand summary.
Fields can be sorted

81
General Listing
82
General Listing
83
General Listing with Summary
84
General Listing with Summary
85
By Group
86
By Group
87
By Group with summary
88
By Group with summary
89
By Group with grand summary
90
By Group with grand summary
91
Week 9 Database Programming