How many tables What does the Supplies table look like

1
How many tables? What does the Supplies table
look like?

Vendor
Warehouse
Supplies
Ternary
Part
2
What does the ORDER LINE table look like?
3
Could Order_ID be primary key?
4
Handy Date/Time stuff

• Calculate differences in time
• You can use the following logic to calculate the
  difference between two times which works even
  across midnight.
• Format(StartTime -1 -EndTime, "Short Time")
• Calculate Age of a person
• Assuming that the birthdate field is called
  BDate and is of type date, you can use the
  following calculation
•  AgeDateDiff("yyyy", Bdate, Now()) _ Int(
  Format(now(), "mmdd") lt Format( Bdate, "mmdd")
  )

5
To return the minimum value for which x of the
values are lower or equal to it

• FName Field name
• TName Table name
• x decimal percentile (0.68 for 68)
• XPercenValue DMin(FName, TName,
  "DCount("""", """ TName """, """ FName
  "lt"" " FName " ) gt " X DCount("",
  TName))

6
If you want to show a total at the bottom of each
page of a report.

• The problem is that access does not allow the
  Sum() function in the page footer. So to get
  around that problem, create a control in another
  section of your report that performs the
  calculation, and set its visible property to
  false. Then create another unbound text box in
  the page footer. Enter the name of the control
  containing the calculation as the text boxs
  controlSource property setting.

7
Put a simple text clock on a form

• To put a simple text clock on a form,  create a
  Label called lblClock on the form, set the form's
  TimerInterval to 1000, and the following code
  behind the Timer Event.
• Private Sub Form_Timer()
• Me!lblClock.Caption Format(Now, "dddd, mmm d
  yyyy, hhmmss AMPM")
• End Sub

8
To create a copy of a Microsoft Access table
without copying all the data it contains

• In the Database window under Objects, click
  Tables.
• In the Object list on the right side of the
  Database window, click the table that has the
  structure or data you want to copy, and then
  click Copy on the toolbar.
• Click Paste on the toolbar.
• Click Structure Only under Paste Options.
• The new table inherits all the field properties
  from the original table. You can then alter field
  names and properties as appropriate.

9
(No Transcript)
10
If you are worried about the increasing size of
your database.

• To ensure optimal performance, you should compact
  and repair your Microsoft Access files regularly.
  On the Tools menu, point to Database Utilities,
  and then click Compact and Repair Database. To
  let Access do it for you automatically
• Open the Access database or Access project that
  you want Access to compact automatically.
• On the Tools menu, click Options.
• Click the General tab.
• Select the Compact on Close check box.

11
To Insert current Time/Date in Excel or Access
2002

• Current date Press CTRLSEMICOLON
• Current time Press CTRLSHIFT SEMICOLON
• Current date and time Press CTRL SEMICOLON then
  SPACE then CTRLSHIFT SEMICOLON
• In Access, this keyboard shortcut only works if
  you are entering data in the Datasheet or Form
  view.

12
Change the Default Style for Controls in Access

• If you want to customize the text box labels you
  use when designing an Access form
• Open a form in Design view.
• Click the Text Box tool on the Toolbox toolbar.
• To open the property sheet for the text box,
  click Properties on the Form Design toolbar.
• In the AutoLabel box, click No.

13
To Convert Your Access Database for Better
Performance (Access 2002 )

• Access 2002 automatically saves all database
  files in Access 2000 format, which is great for
  smaller databases and enables you to share your
  database files with Access 2000 users. If,
  however, you don't plan to share your database
  with Access 2000 users, or you are creating a
  large database, you can achieve better
  performance by converting your database to the
  new Access version 2002 format. Access 2002 will
  create a separate database file in the new
  format, leaving your original database file
  intact.
• To convert your database to Access 2002 file
  format
• On the Tools menu, point to Database Utilities.
• Point to Convert Database.
• Then click To Access 2002 File Format.

14
Query Criteria Tips

• There are two wildcards supported by Access ?
  and . The question mark represents any single
  alphanumeric character, while the asterisk
  (sometimes called a splat) represents a character
  string of any length.For example Like "B?T"
  locates bit, bat, bet, bot or but.   Like "BT"
  will also locate boot, beruit, bait, and
  ballot.A good tool for select queries is the
  square brackets, which will find any characters
  listed inside the brackets. Youll generally use
  this operator along with other wildcards. For
  example Like "E-J" locates records where the
  entry starts with E, F, G, H, I, or J.Use the
  exclamation point ! (sometimes called a bang
  symbol) to create a "not like" expression. For
  example Like "!E-J" locates all records that
  start with anything except the letters E, F, G,
  H, I, or J.

15
The Business of Data

• Evolution of Architecture
• Centralize Data/Processing
• Costly
• Data Re-Keyed from Function to Function
• Delay, Costly, Error Prone

16
New Data Model

• One Physical Data
• Many Views

VIEWS
DATA
17
Architecture - One-Tier
Client Running Application With local Database
18
Two-Tier (Client-Server)
Client Running Application
TCP/IP
Database Server
19
Three-Tier (Client-Server)
Client
http
Application Server Eg IIS
SQL
Database Server
20
Example 3-Tier

• Web Page Creation
• Changes and Modifications

21
Advantages of n-Tier

• Scalability
• Adaptation to Traffic
• Technology Flexibility
• Best H/W for Apps
• Lower Long Term Cost
• Reduce Risk

22
Challenges with n-Tier

• High Short-term Cost
• Tools and Training
• Experience
• Incompatible Standards
• Lack of End-Users Tools/Middleware Svc