Microsoft Access Two Assignments

Week 5 & 6 Query Project

 Open the CUSTOMER file

 Create a Query screen with the CUST table and perform the following queries. Save the query as query1, query2 etc. there will be 16 queries at the end.

 Save the database file with your name, or change the file name to your name, and upload using the assignment tool “drop-box”, for grading.

1 Last name beginning with "S" and first name beginning with "R" from California

1

2 California with prices between $500 and $1000 1

3 Everyone who lives on a drive (meaning “Dr.” in Street address)

1

4 Street name beginning with W (remember that there is a number in front of the street name that you do not know) It’s tricky but try it. Think of the “pieces” that makeup the address.

3

5 California customers except for San Francisco 1

6 Parameter query (the interactive one with the [ prompt ] that requests “please enter the state name” .

1

7 Zip code beginning with 9 and ending with 0 1

8 The states NY, CA and WA only 1

9 Qty 4 and higher and from CA, NY and WA 1

10 Smith from CA as well as WA, along with anyone with a price over $300

2

11 Make another parameter query so the user can enter a Street name without knowing the “house number” Example: Pine. If more than one record show for “Pine”, the query is not correct!

3

12 Sort by STATE, LAST and FIRST. One step. One sort.

1

13 Calculate the Total (PRICE * QTY) Calculate a 10% discount Calculate the new Total

3

14 Create a new column, CUSTOMER as shown with both Last name, First Name:

2

Open CUST2 for the following queries. You will be using the second customer table provided for these queries. The field contents have been modified slightly so that corrections are needed. There is a CITY_STATE field that contains both city and state. This is a common occurrence, although I don’t know why companies insist on doing this.

15 Your task is to separate out the state as shown below.

4

16

Can you separate out the City as shown above?

You would need to extract from the left, correct? Use the LEFT function (LEFT(fieldname,# of characters), but the LEFT function needs to know exactly how many characters you want, and obviously the number of characters you want, changes based on the length of the city name. So what to do? In the second argument of the LEFT function, instead of specifying an actual number, maybe you could use something that can give you the exact length of the contents of the field, and you can subtract the two character state, space and comma?)

6

17. Extra- Credit for the brave and curious

Can you separate out the street name so you can sort by street name? This is a very common problem that not many can do. This is just for fun and curiosity. I will of course explain the whole thing after you have had time to think, chat, and sadly, get it from the internet. Well at least it shows persistence.

5