Access CIS117DM
For most queries, you can update the information in their datasheets. What two types of queries don't allow you to update the data within them?
1. A totals query 2. A query that uses 4 or more tables
Using a find unmatched query is useful for doing 2 specific things, what are they?
1. Finding records in one table that do not have matching records in a related table 2. Finding the cause of a data violation error when trying to create a relationship between 2 tables (a foreign key value(s) doesn't have a match to a primary key value).
What is wrong with the following concatenated column? PatientName FirstName, LastName
1.There should be a colon (:) between PatientName and FirstName 2. There should be ampersands (&) between FirstName and LastName 3. There should be two double quotations and a space between the ampersands PatientName: FirstName & " " & LastName
The default character length for the Short Text data type is ____ characters.
255
Logical operators
3: And - used when 2 criteria and both must be met Or - any of the 2 criteria can be met (expands) Not - used to negate criteria used with Like, Between and In operators
Which of the following examples shows an input mask for a telephone number with the first three digits optional and dashes separating the parts of the telephone number? 000\-999\-9999 999\000\0000 999-000-9999 999\-000\-0000
999\-000\-0000
List 4 examples of comparison operators:
=, <>, <, <=, >, >=, Between..And, In, and Like
To connect records from one table to records in another table, you must use this type of field:
A common field. OR Primary Key - Foreign Key
Primary Key
A field (or group of fields) that uniquely identifies a given entity in a table. Unique, static & minimal
Date Field
A field in a database that holds date data such as birthdays, due dates, and so on. Time stamp can be stored too. add a decimal and it indicates time; time would be stored as midnight behind the scenes
Lookup Wizard field
A field that uses the Lookup Wizard data type, which lets you create a lookup field in a table (visual)
Field
A meaningful category of information, such as FirstName or HireDate, is called a(n):
foriegn key
A primary key of one table that appears an attribute in another table and acts to provide a logical relationship among the two tables. Provides common field so records of 2 tables can be joined.
parameter query
A query in which the user interactively specifies one or more criteria values. Use [ ] for prompt. Like operator and wildcard operator along side of parameter box.
one-to-many relationship
A relationship between two tables in a database in which one record in the primary table can match many (zero, one, or many) records in the related table.
Referential Integrity
A set of rules that Access uses to ensure that the data between related tables is valid. A record in one table common field will match a value in another table.
examples of Logical operators in Access?
And, Or
Using criteria in a query, which comparison operator should you use when you want to return a range of values (i.e., if the result set should return date ranges of 1/1/2019 through 6/1/2019)?
Between comparison operator
Before creating a relationship between a primary key in one table, and a foreign key in a related table, what two things must be true regarding those fields so the relationship can be established?
Both fields must be the same data type and the same size.
How does the Lookup Wizard hide the column of key values?
By setting the column's Width property to 0".
A field used to connect one table logically with another table is called a ____ field.
Common
Which data type would be the best logical data type for a field named PaidDate?
Date/Time
The ____ property allows the database designer to explain the purpose of a field or how a field is used.
Description
A table can be created in which two views?
Design View Datasheet View
Query sort order
Determines the order of records in the query's Datasheet view from left to right
Query criteria
Expressions that are used to restrict the results of a query in order to display only records that meet certain limiting conditions. (comparison operators)
To begin importing data from an Excel spreadsheet, click on the ____ tab on the ribbon.
External Data
The specific content of a field is referred to as the ____.
Field Value
Which Access object is used to view and edit data on the screen in a customized layout?
Form
Using criteria in a query, which comparison/logical operator should you use when you want to return values where criteria is used from the same column (i.e., if the result set should include all customers from Arizona or California)?
In comparison operator/Or logical operator
many-to-many relationship
In databases, a relationship in which one record in Table A can relate to many matching records in Table B, and vice versa. Include a join table
A(n) _________ is a predefined format used to define a pattern for data to be entered into a field and control the values a user can enter into a field.
Input Mask
The sort priority for a query is:
Leftmost sort first
The ____ comparison operator is used to select records by matching field values specific to a pattern that includes one or more wildcard characters.
Like
Create a parameter criteria for a City field that prompts a user to "Please enter a city" but will return every record if the user doesn't enter a value
Like "*" & [Please enter a city] & "*"
Which criteria would return zip codes with the characters 704 anywhere within the field.
Like "*704*"
How to use like, In & between
Like: pattern matching (text fields) what letter it starts with or ends with *s*, S* (starts), *S (ends) In: list of values, elogant way of placing on one lines use () seperate. by commas Between: finding a range of value (numeric and date values) always use and
The process of adding, modifying and deleting records in a database to keep the records current and accurate is referred to as ____ a database.
Maintaining
Input Masks
Microsoft Access uses ________ to help in preventing errors when data is entered into fields, such as telephone numbers, postal codes, and Social Security numbers.
List 2 different actions that cause Access to automatically save changes you've made to the current record.
Move to another record, close the table/form
A criterion may be negated by using the ____ logical operator.
Not
On an Access datasheet, which symbol indicates a dirty record?
Pencil
A table's _____ uniquely identifies each record in the table.
Primary Key
Read a table in design view
Property sheet, field size,etc
Data organized as a collection of tables creates this type of data system.
Relational Database Management System (RDBMS)
Default sort of a table
Sorts table in primary key order
default values
Specifies what value will appear, by default, for the field in each new record you add to a table
comparison operators
Symbols used in search criteria. (>, <, =, >=, <=, <>)
What 4 database objects are created in Module 1?
Table, query, form, and report
pencil symbol
The symbol that appears in a row selector to indicate that the record is being edited.
What is the purpose of a parameter query?
To provide a prompt for users to see and then type in a value that will used as a criterion value when the query is run.
The ____ data type only allows a field length of 1 character and is used to indicate the presence or absence of a condition.
Yes/No
A field with the Short Text data type sorted in descending order will display the data in which order?
Z to A
This is true about the OR logical operator
at least one condition must be met to return records in a query criteria is placed on different lines expands the number of records returned in a query
Queries based on more than one table, must have a ____ field.
common
Entity Integrity
each entity has a unique value in a primary key and that there are no null values in the primary key.
IIF function
evaluates a condition and returns one value if the condition is true and another value if the condition is false
one-to-one relationship
exists between two tables when each record in the first table matches at most one record in the second table and each record in the second table matches at most one record in the first table. (primary key to primary key) to break up fields or security purposes
Which symbols would be used to create a custom date format that would display July 4, 2016 as 7/04/16?
m/dd/yy
Statistical information such as totals and averages is calculated using ____.
mathematical operators
Selection criteria that are placed in different rows in the query design grid indicate an ____ operator.
or
A set of field values is called a __________
record
This is true about AND logical operator
reduce the number of records returned in a query 2 or more conditions must be met to return records in a query all of the criteria is placed on the same line
Domain Integrity Constraint
set a permissable value where one or more tables draws their value. (Size of data, lookup field, validation rules)
Access Data types
short text, yes/no, hyperlink, long text 66k characters, lookup wizard, Date/Time
Consider the following calculated field: InvoiceMessage: IIF([InvoiceAmt] >= 100, "large invoice", "small invoice") If the value in InvoiceAmt is $99, what will be displayed in the InvoiceMessage field? Correct!
small invoice
Captions
specifies how a fields name is displayed in database objects, including tale and query datasheets, forms and reports
Validation rules
specify values that are allowable for a field, such as a certain range of numbers or dates. these are needed to ensure that only valid data is entered into the field
Backing Up a Database
the process of making a copy of the database file to protect your database against loss or damage. copy and paste makes a backup
concatenate columns
to combine columns to produce a compound key & " " & (first name & " " & Last name)
Which wildcard symbol represents a single numerical digit?
#