Create a query with comparison operators use either gt lt


Access Project Requirements Document

Concept - The idea behind the Access Project:

You are an entrepreneur beginning a new business. It can be a totally new concept, such as a new nail salon, a pizza restaurant or service you provide. You could also be opening a franchise, such as Model sporting store, or a Subway fast food, Domino's Pizza or even a big hotel such as Hilton. The access database project supports this new business you are starting. You will be creating Access objects for the database.

I have picked a gym as my database!!!! My project is going to be called "Andrew's Gymnasium".

General Development Rules of the Project:

  • The database must have a name that relates to your business: I have picked "Andrew's Gymnasium" for my project.
  • Each requirement listed below must be completely and accurately completed. You are graded on each requirement.
  • The database and any supporting files must be maintained in one labeled folder
  • You are free to copy and paste any images/text from the internet. You can Google pictures; use existing websites that correlate to your business

Requirements Section -

1. Build a Database - Tables, Fields and Records

a) Create a new database with a name that corresponds to the topic you have chosen. My gym database is called "Andrew's Gymnasium".

b) The database must have two related*tables with meaningful names that correspond to the data in the table. There must be one common field on both tables to enable you to link the two tables, forming the relationship. One key must be a foreign key in one of the tables. Referential Integrity must be enforced. Please note:  it is helpful to have a customer or client table in your database as this is a business.

It is important to create your table relationship prior to adding any data to the tables.  Assure it is correct and after defining all of your fields, you can then enter the data for each record.

c) Each table must have a minimum of eight fields (you can have more)

d) Each table must have a primary key that is not an auto number. It can be short text or number.  Do not use last name as that is not unique. You can also make up unique alpha numeric characters for the primary key such as EN2014 or simply sequential numbers.

i. Both tables must have all non-currency, date, hyperlink, and attachment field data types defined as Short Text with reasonable field sizes.  For example, First Name, Last Name, Company, etc.  The field size cannot be the default 255 characters.

ii. The date field must have a date format done in Field Properties

e) The following data types are to be included in at least one table:

iii. Long Text (Memo)

iv. Number - minimum 1 fields.

v. Currency - minimum 2 fields.

vi. Date (you chose the date format)

vii. Attachment.

viii. All fields must have a description.

f) All fields must be filled in with data. You can make up the values.

The second table can contain fields that are appropriate for that table.

g) Create the following properties for selected fields (again you can use one table):

i) One field must be defined as Required

ii) There must be an Input Mask on at least one field. (example: an input mask for a  zip code or telephone number)

iii) All Fields of one table, must have the Caption (logical name of the field) defined (hint: you do this in Field Properties).

h) Create a Validation Rule with corresponding text for one field in only one table.

(1) There should be a default set for this field with the default value being one of the values in the validation rule.

(2) If your validation rules contains alpha numeric values, it should be set to force upper case.

(3) Must have a logical and meaningful Validation Text (the message one sees when entering the incorrect value).

i) Create a Lookup field providing a drop down list with valid entries for one field. It cannot be the same field you used for the Validation process. You can either use an existing table in the database for the lookup or create your own lookup values when you use the Lookup wizard.

j) All fields (on the table (s) where you added the lookup field and validation) must be filled in completely with both the lookup values and validation values.  On the field that is to be linked to the second table, be sure data is uniformly defined. (Note: make sure you have the relationship between the two tables correctly defined before adding the data.)

2) Forms

Create one form using the Form Wizard.  You chose the style and which fields go on the form.  The form should have logical design for it purpose. Include the following:

a) Header Section

i) Must have a form title in the Form Header.  The title must have a font style that is different from Calibri and be of a larger font size than 11.It cannot be truncated (cut off).

ii) Include a logo in the Form Header that represents your business (can be a google image, an image copied from a website, or clip art). Place the image in an appropriate position, but not on or covering the title.

iii) The form header must be formatted with background color.  You can also use a picture but make sure all the text can be seen.

iv) Include the Date and Time in the Form Header.

b) Detail Section

i) Provide a good design of the fields in the form with some type of special effects added to the field label controls (can be beveled, shadowed, etc.) and must be uniform. This is not the font.

ii) Change the color of the fonts in the field controls only, not the control labels.  Choose a color that compliments your overall theme.

iii) Be sure all data displays and none is truncated (#### are not acceptable).

iv) You must create one calculated field in your form.  You cannot use a function, such as SUM, AVG. etc. Your calculation must start with an equal sign.  It should be properly labeled and run accurately. (*If you need help with this go to the Information Kiosk in Blackboard/ Help How to Folder/Access folder for instructions.)

c) Save the form with a meaningful name.

d) Add one record using the form.

Create one split form using the table with the Memo (long text) field. 

a. Align all fields so no data is truncated.

b. The form must have a logo and a logical title in the form header that is related to the subject topic. 

c. No data can be truncated. Make sure the datasheet section (lower section) has no truncation of data.

d. Save the form with the name, Split Form.

e. Update the Memo field values in the table for all records in the table to which you added the memo field.

3) Queries

Create the following queries that make logical sense in your database:

a. Create a query with comparison operators (use either >, <, > = and <= or between).   Run the query and save it with as Comparison Query.  You cannot use Sum, Average, max or min as these are functions

b. Create a compound query using either an AND or an OR.  Run the query and save it with as Compound Query.

c. Create a query with a wild card using one of the fields. Run the query and save it as Wild Card Query.

d. Create a parameter query. Run it and save it as Parameter Query.

e. Create a query that has a calculation that you developed. Run it and save it as Calculation Query. Note: do not use a function such as SUM, MAX, etc.). Your calculation would start with an equal (=) sign and use fields in one of your tables.(*If you need help with this go to the Information Kiosk in Blackboard/ Help How to Folder/Access folder for instructions. Additional help can be found in the professor assignment Querying the Sales Database in lesson 6)

f. Create a query displaying totals using the Sum function. Save it as Sum Query. 

g. Do the same for Max. Save as MaxQuery.

h. Using one of your tables, create an Update action query.  Run the query and save it as Update Query

i. Using one of your tables, create aDelete action query.  DO NOT run the query but save it as Delete Query. If you run it you will delete data that may be needed for other functions.

4. Reports and Labels

1. Create a Report

a. Create a report from one table using the Report Wizard.

b. The report must be grouped and sorted on a field of choice.

c. There must be a calculation in the detail section that you create.  This cannot be a function such as SUM. MIN or MAX

d. Apply a theme to the report

e. Save as My Report

2. Create Shipping Labels

a. Create one set of shipping labels from one table.

b. Each label must have an image representing your company.  The image should display on each label.

c. Save as My Labels

5. Create a Navigation Form

a. Use Horizontal tabs and Vertical tabs left for the layout

b. There must be a way to exit the database and it should be in the form footer

c. Add you logo (or picture representing your topic to the form header).

d. You must use shape effects for all tabs and buttons. Allhorizontal, vertical tabs and buttons used on the navigation form must have the same shape effect applied (for example,  Bevel)

e. The Navigation form must automatically display when the database is opened

f. The Navigation Pane must be closed when you save the form.

6. Create attachments for a Table

In one table only create the attachment fields.

a) You must add the two attachment to each record in one of your tables

b) (the attachments can be an Excel workbook that you have [it doesn't have to correspond to the topic, you can use any excel workbook found in your student files.] or a photo which you googled or Binged)

c) Create a quick form that includes the attachment field and save it as Attachment Form.

7. Security

a. Encrypt the database, and add a password to the database.  Please use 20rcc17 (lower case letters)

When complete, submit the database to me.

Request for Solution File

Ask an Expert for Answer!!
Database Management System: Create a query with comparison operators use either gt lt
Reference No:- TGS02227463

Expected delivery within 24 Hours