Flashcards in Midterms 2 Deck (106):
A set of field values. A row.
A field or a collection of fields that uniquely identifies each record in a table. Should be unique, minimal, and static.
What is a good example of a good primary key option
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
The constraint specifying that each nonnull foreign key value must match a primary key value in the primary table
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
The constraint that says that the primary key can't be null. For a composite key, none of the individual fields can be null.
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
A set of values from which one or more fields draw their actual values
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)
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
Where can you find the lookup wizard
In the data type section of the field in design view
Displays a dialogue box that prompts a user to enter one or more criteria values when the query is run
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]
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)
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
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] & "*"
An exact Match. (optional, this is Access default
<>, =, , <=, >=, Between...And, In, Like
How would you write a Between...And
Between 10 and 100 (this will include both 10 and 100)
How do you write an In operator?
In("pit bull", "yorkie", "poodle")
A wild card that will match any string of characters before or after the letter you specify
Like "S*" (Starts with S)
Matches any one character
Like "Sa????" (Starts with Sa and has 6 letters total)
Matches any single digit
Multiple selection criteria
AND and OR
Narrows down the record selection
expands the record selection
Allow you to do summarized data and view it in aggregate form
SUM, AVG, MIN, MAX, COUNT, Group by's
TRUE or FALSE: You can update data in a total's query
Allow you to combine the contents of two or more text fields into one
Example of how you would concatenate FirstName and LastName
FullName: FirstName & " " & LastName
How would you concatenate a FirstName and City field to say that a person is from a city
Info: FirstName & " is from " & City
Short Text, Long Text, Number, Date/Time, Currency, AutoNumber, Yes/No, Hyperlink
Letters, digits, spaces, and special characters. Used for names, addresses, descriptions, and fields containing digits that are not used in calculations.
Short text field size
0 to 255 characters
Letters, digits, spaces, and special characters. Used for long comments and explanations
Long Text Field Size
1 to 65, 535 characters
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
Number field size
1 to 15 digits
Allows field values containing valid dates and times. You can perform calculations and you can sort them
Date/Time Field Size
Similar to number, but used for storing monetary values
Currency field size
Accurate to 15 digits on the left side of the decimal point and 4 digits on the right
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!)
Autonumber field size
Limits field values to yes and no, on and off, or true and false
Yes/No field size
Consists of text used as a hyperlink address
HyperLink Field size
up yo 65,535 characters total for the four parts of the hyperlink
When does Access save changes to a record
When you move to a new record and when you close the table/form
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
Backing up a database
The process of making a copy of the database file to protect your database against loss or damage
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
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
Example of when to use a default value
When you want to always add the current date to new orders
Where to add default values
In the default Value property for the field in design view
Specifies how the field name is displayed in database objects
What happens if you don't set the Caption?
Access will display the field name as the column heading or label
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
What does a 9 represent in Input Mask
A numeric value that is not required
What does a 0 represent in input mask
A numeric value that is required
What does an L represent in Input Mask
A letter that is required
Input mask example for JES213 format with a # used as a placeholder
Field Validation Rule
Validates data being input so that it meets the criteria you specify
A message that pops up when a user enters a wrong amount that tells them what they did wrong
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
Uses your computer system to determine todays date
What does the pencil symbol in a datasheet indicate?
That the record is being edited
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)
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];
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
Immediate If function. Returns a value based on a test condition.
IIF function format
IIF(part 1 [test condition], part 2 [value if true], part 3 [value if fales]
Example of an IIF Function
InvoiceAge: IIF (InvoiceDate > 1/1/2016, "New Invoice", "Old Invoice")
Returns the numeric value of a specified date part (d, m, y)
MonthNumber: DatePart("m", InvoiceDate)
If the invoice date is 1/1/2012 the MonthNumber will be 1
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)
Form Naming tag
Macro Naming Tag
Module Naming Tag
Query Naming Tag
Report Naming Tag
Table Naming Tag
Check Box Naming Tag
Combo Box Naming Tag
Command button Naming Tag
Image Naming Tag
Label Naming Tag
Line Naming Tag
List box Naming Tag
Option Button Naming Tag
Rectangle Naming Tag
Subform/subreport Naming Tag
Text Box Naming Tag
A table is in 3NF if...
Every determinant is a candidate key
A table is in 2NF if...
It does not contain any partial dependencies
A table is in 1NF if...
It does not contain any repeating groups
The process of identifying and eliminating anomalies
Problems caused by data redundancy and by partial and transitive dependencies
Occurs when you cannot add a record to a table because you do not know the entire primary key value
Occurs when you delete data from a table and unintentionally lose other critical data
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