Lab 10

In this lab, you'll practice using forms and reports on a database table that you design yourself.

A database is the perfect thing to help banks manage their customers and their customers' accounts. So, let's create a database for everyone's favorite bank, BANKO BANK.

Let's pretend that BANKO BANK offers two types of accounts: checking accounts and savings accounts. Each customer can have an unlimited amount of accounts, although most people only have 1 of each kind. For each customer, the bank keeps track of the following bits of required customer information:

1. customer first name
2. customer last name (separate from first name)
3. customer address
4. customer social security number
5. customer sign up date (so they can give customers who have been using BANKO for 'X' years special benefits, including BANKO brand pens and BANKO brand stationary!)
6. customer preferred check design (some people like their checks to have pictures of kittens, others like beach pictures, some prefer formal/plain BANKO checks)
7. customer ID (automatically generated by the database system)

For each account, we need to store the following bits of information:

1. account number (automatically generated somehow)
2. account type (savings or checking)
3. account balance
4. interest rate (preferred customers may be given higher interest rates than usual): The default should be 0. Some accounts (like savings accounts) do have an interest rate that is nonzero. Interest rates will be expressed as a decimal (e.g., instead of 7% it should say 0.07) and can have up to 4 decimal places (e.g., 2.25% = 0.0225 is allowed).

It makes sense then to store these 2 types of information (person information and account information) in 2 tables. We can then use a third table to link them together. This third table will probably only have 2 fields in it: a customer ID field and an account number field.

Design the 3 mentioned tables and enter in some sample data for at least 4 accounts and at least 3 people. Keep the following in mind:

1. There are only a few check designs, and the database should not allow a customer or bank teller to input an invalid type. Furthermore, customers are only allowed 1 preferred check design. In other words, use the lookup wizard so that when selecting the check design type, the teller (who will be using the database) will be forced to pick one of the available designs.
2. You'll want to use primary keys where it makes sense to.
   1. For example, customer IDs should be primary keys as there cannot be two customers with the same ID.
   2. Account numbers should also be primary keys as again, two accounts cannot/should not have the same ID.
   3. In the third table, which links customers to their accounts, BOTH fields should be primary keys. Making both fields in that table primary keys will make sure that if a customer's ID is in the table more than once, it has to be with a different account number each time. Additionally, it ensures that if an account appears in that table more than once, it must be with a different customer each time. This is an important/useful concept.
3. All fields are required.

Now, establish the relationship(s) between the 3 tables.

The bank tellers want to be more productive at work, and further more, they're tired of looking at table after table of cells of data. They want color and they only want to see what is relevant to what they're currently doing. Most of what they're doing, is handling deposits/withdrawals. So, they have requested a form that once they load it up, it asks for the account number and then it lets them edit the balance (so that they can handle the customer's deposit/withdrawal). This can be done in 2 steps.

Using the simple query wizard, create a query that displays ONLY the account number, balance, interest rate, and name of the person (so the bank teller can verify the photo ID of the person making the transaction). Then, in query design mode, set the criteria so that when the query is run, a pop up box appears asking the user for the account number.

Test our your query. When you run it, a message box should pop up asking you for the account number and then, after entering that, it should display the information for that account ONLY. Remember, each account may have multiple people associated with it (e.g., a married couple sharing a joint-account). So, you may get 2 records returned as a result, but each record should be for the same account #.

Now, using the form wizard, have it pull its information from the query you just designed above. Test it out. Use the default options, but feel free to pick your own theme.

Use one of the form editing modes to customize the form. The form should feature in big red letters the words "Account Edit Mode" so that the bank teller is aware of the fact that this is a dangerous form and should be used carefully (because an error could result in a very unhappy customer¹⁾, or a very unhappy bank²⁾).

You should also reword some pieces of text to make it seem more user friendly. In other words, if you have a heading that says "account_id" you should have it instead say maybe "Account ID:". Do this for all the fields, using proper capitalization.

You should also add the BANKO BANK logo, designed by yours truly, available at: this place here (save it to your computer, and then insert it into the form). Spend several minutes marveling at its glory.

Lastly, rearrange the text boxes which display each field from the query. Some possible ideas include just reordering them, or displaying them side by side. You should arrange them in a way that makes sense.

As an employee of BANKO, you are expected to seem productive (seeming productive is not always the same as actually being productive). And, what better way is there to seem productive than by producing large reports of seemingly useful data? With rumors of downsizing, you hope that by producing large stacks of paper filled with numbers, that your boss will be impressed and you won't lose your job. So, go ahead and make a report that uses all of the customer and account information. You'll want the report to contain summary information (e.g., total balances, minimum balances, etc).

Be sure and sort the data based on some kind of criteria. For example, you might sort based on the customer names, the customer sign up date, or even balance.

Don't forget to add the official BANKO logo to your report. On the footer of the report, be sure to add something like, "© BANKO 2010" and "SENSITIVE DATA - DO NOT DISTRIBUTE"

In layout view, notice on the tool bar the conditional formatting button. Using this tool, which is very similar to the conditional formatting we used in Excel, cause the following to happen:

1. If an account balance is negative (< 0) it should be red
2. If an account is not negative, but is less than $100, it should be yellow
3. If an interest rate is less than 1%, it should be underlined: this way, we can notice this account and try to convince the customer to put their money into a high-interest savings account ³⁾
4. If a customer's preferred check design is NOT the plain/formal BANKO brand design, it should be made bigger than normal (and bold). The BANKO teller's will try to convince these customers into using BANKO brand checks, which effectively advertise BANKO BANKs wherever the checks are used.

Lastly, recall that BANKO bosses are very money-hungry and they love the color green. Change some of the text and headings to be green. Be sure that you change any goofy looking field titles (e.g., "account_interest" into something more presentable, such as "Interest").

As per usual, call me over to take a look at the lab and I'll give you feedback and/or credit. Congratulations, you are well on your way to becoming an under-appreciated BANKO BANK employee.

If we run out of time in class, you can email me what you did.