Midterms 2 Flashcards Preview

Access > Midterms 2 > Flashcards

Flashcards in Midterms 2 Deck (106):
1

Record

A set of field values. A row.

2

Primary Key

A field or a collection of fields that uniquely identifies each record in a table. Should be unique, minimal, and static.

3

What is a good example of a good primary key option

SS#

4

Foreign Key

A field or a collection of fields in one table in which each field value must match the value of the primary key of some table or must be null

5

Referential Integrity

The constraint specifying that each nonnull foreign key value must match a primary key value in the primary table

6

Referential Integrity Rules

You can;t add a row containing an unmatched foreign key value and you can't change or delete a primary key

7

Entity Integrity

The constraint that says that the primary key can't be null. For a composite key, none of the individual fields can be null.

8

Domain Integrity

A rule you specify for a field. Ex: when you choose a data type you impose a constraint on the set of values allowed for a field

9

Domain

A set of values from which one or more fields draw their actual values

10

Lookup Field

Lets the user select a value from a list of possible values. You can set the field to only allow values that you specify (That the user can select or from a related table)

11

Lookup Wizard

Used to create a lookup field and hide columns, select what it displays, select how the values are sorted, and select what is sorted in the field

12

Where can you find the lookup wizard

In the data type section of the field in design view

13

Parameter Query

Displays a dialogue box that prompts a user to enter one or more criteria values when the query is run

14

What would a parameter query used for a report based on a time frame between certain points look like?

[Enter a start date] [Enter an end date]

15

What happens if you misspell criteria or press ok without entering criteria in a parameter query prompt?

You will get a datasheet without results (blank. No records)

16

Why would you get a prompt in a query that is not a parameter query and how do you fix it?

If you misspell a field name in a calculation. Fix the calculation to get rid of the prompt

17

Write a pattern match criteria that will diaply results if a user only enters a letter or doesn't enter anything

Like "*" & [Please enter a city] & "*"

18

=

An exact Match. (optional, this is Access default

19

Comparison operators

<>, =, , <=, >=, Between...And, In, Like

20

How would you write a Between...And

Between 10 and 100 (this will include both 10 and 100)

21

How do you write an In operator?

In("pit bull", "yorkie", "poodle")

22

*

A wild card that will match any string of characters before or after the letter you specify

23

* example

Like "S*" (Starts with S)

24

?

Matches any one character

25

? Example

Like "Sa????" (Starts with Sa and has 6 letters total)

26

#

Matches any single digit

27

Logical Operators

Multiple selection criteria
AND and OR

28

AND operator

Narrows down the record selection

29

OR operator

expands the record selection

30

Totals queries

Allow you to do summarized data and view it in aggregate form

31

Aggregate functions

SUM, AVG, MIN, MAX, COUNT, Group by's

32

TRUE or FALSE: You can update data in a total's query

FALSE

33

Concatenated fields

Allow you to combine the contents of two or more text fields into one

34

Example of how you would concatenate FirstName and LastName

FullName: FirstName & " " & LastName

35

How would you concatenate a FirstName and City field to say that a person is from a city

Info: FirstName & " is from " & City

36

Data Types

Short Text, Long Text, Number, Date/Time, Currency, AutoNumber, Yes/No, Hyperlink

37

Short Text

Letters, digits, spaces, and special characters. Used for names, addresses, descriptions, and fields containing digits that are not used in calculations.

38

Short text field size

0 to 255 characters

39

Long Text

Letters, digits, spaces, and special characters. Used for long comments and explanations

40

Long Text Field Size

1 to 65, 535 characters

41

Number

Positive and negative numbers, digits, a decimal point, commas, a plus sign, and a minus sign. Used for fields that will be used in calculations, except for those involving money

42

Number field size

1 to 15 digits

43

Date/Time

Allows field values containing valid dates and times. You can perform calculations and you can sort them

44

Date/Time Field Size

8 bytes

45

Currency

Similar to number, but used for storing monetary values

46

Currency field size

Accurate to 15 digits on the left side of the decimal point and 4 digits on the right

47

AutoNumber

Consists of integer values created automatically by Access each time you create a new record. Can specify sequential numbering or random numbering (Good for primary key!)

48

Autonumber field size

9 digits

49

Yes/No

Limits field values to yes and no, on and off, or true and false

50

Yes/No field size

1 character

51

HyperLink

Consists of text used as a hyperlink address

52

HyperLink Field size

up yo 65,535 characters total for the four parts of the hyperlink

53

When does Access save changes to a record

When you move to a new record and when you close the table/form

54

Compacting and repairing a database

Rearranges the data and objects in a database to decrease its file size, thereby making more storage space available and enhancing the performance of the database

55

Backing up a database

The process of making a copy of the database file to protect your database against loss or damage

56

How to back up a database

Click file tab, click Save As, click Backup Database in the Advanced section of the Save Database As pane, then click the Save As button

57

Default Values

You add a default value to a table or field or form control whenever you want Access to enter a value in a new record automatically

58

Example of when to use a default value

When you want to always add the current date to new orders

59

Where to add default values

In the default Value property for the field in design view

60

Caption Property

Specifies how the field name is displayed in database objects

61

What happens if you don't set the Caption?

Access will display the field name as the column heading or label

62

Input Mask Property

A predefined format used to enter and display data in a field like a phone number to make sure all of the pone number formatting is consistant

63

What does a 9 represent in Input Mask

A numeric value that is not required

64

What does a 0 represent in input mask

A numeric value that is required

65

What does an L represent in Input Mask

A letter that is required

66

Input mask example for JES213 format with a # used as a placeholder

LLL000;;#

67

Field Validation Rule

Validates data being input so that it meets the criteria you specify

68

Validation Text

A message that pops up when a user enters a wrong amount that tells them what they did wrong

69

Example of Validation rule and corresponding Validation Text if an invoice amount has to be more than 5

Validation Rule: >5
Validation Text: Invoice amount must be greater than 5

70

Date()

Uses your computer system to determine todays date

71

What does the pencil symbol in a datasheet indicate?

That the record is being edited

72

How do you modify a lookup value property to hide column values?

Write it as 0" under column width and make sure there are as many widths as there are columns (0";2" is 2 columns)

73

How do you modify a lookup value property to sort columns? (Example)

 Originally: SELECT [tblOwner].[OwnerID], [tblOwner].[FirstName], [tblOwner].[LastName] FROM tblOwner
 SELECT [tblOwner].[OwnerID], [tblOwner].[LastName] &”, “ & [tblOwner].[FirstName] FROM tblOwner ORDER BY [tblOwner].[LastName];

74

How do you fix referential integrity between tables when a "can't create this relationship" error message occurs?

Use an unmatched query to find which record in the second table does not have a match in the primary table

75

IIF Function

Immediate If function. Returns a value based on a test condition.

76

IIF function format

IIF(part 1 [test condition], part 2 [value if true], part 3 [value if fales]

77

Example of an IIF Function

InvoiceAge: IIF (InvoiceDate > 1/1/2016, "New Invoice", "Old Invoice")

78

DatePart

Returns the numeric value of a specified date part (d, m, y)

79

DatePart Example

MonthNumber: DatePart("m", InvoiceDate)

If the invoice date is 1/1/2012 the MonthNumber will be 1

80

Crosstab query

Allows you to view data in aggregate format grouped by category and type.
Type is shown in each row and categpry typically refers to a time frame (month, order, or year)

81

Form Naming tag

frm

82

Macro Naming Tag

mcr

83

Module Naming Tag

bas

84

Query Naming Tag

qry

85

Report Naming Tag

rpt

86

Table Naming Tag

tbl

87

Check Box Naming Tag

chk

88

Combo Box Naming Tag

cbo

89

Command button Naming Tag

cmd

90

Image Naming Tag

img

91

Label Naming Tag

lbl

92

Line Naming Tag

lin

93

List box Naming Tag

lst

94

Option Button Naming Tag

opt

95

Rectangle Naming Tag

shp

96

Subform/subreport Naming Tag

sub

97

Text Box Naming Tag

txt

98

A table is in 3NF if...

Every determinant is a candidate key

99

A table is in 2NF if...

It does not contain any partial dependencies

100

A table is in 1NF if...

It does not contain any repeating groups

101

Normalization

The process of identifying and eliminating anomalies

102

Anomalies

Problems caused by data redundancy and by partial and transitive dependencies

103

Insertion Anomaly

Occurs when you cannot add a record to a table because you do not know the entire primary key value

104

Deletion Anomaly

Occurs when you delete data from a table and unintentionally lose other critical data

105

Update anomaly

Occurs when a change to one field value requires the DBMS to make more than one change to the database, and a filure by the database to make all the changes result in inconsistant data

106

Partial dependency

A functional dependency on part of the primary key instead of the entire primary key