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?

#