Creating Combo-boxes in Access

As we've been designing database tables over the past few weeks, it often seems like we've got primary keys everywhere - our joining tables are just all numbers which makes them really hard to understand!

It can be very tempting to add Lookup Fields to our database tables which lookup the corresponding value and display that instead (for example, instead of displaying the student ID of 945, the related name of "Henry the Octapus" is displayed instead). But one of the 10 commandments of Access is:

thou shalt abhor the use of "Lookup Fields" which art the creation of the Evil One.

(If you're interested, follow the link on Lookup Fields to find out why.)

So how do we then make our database more user friendly? The key is to do it through our forms (our users should never see our tables anyway!).

Open up your Student Enrolments database, and create a form to enrol students into courses.

• Initially, after creating your form with the Wizard, you should have fields for Student ID, Course ID and perhaps Enrolment Date or other fields associated with the enrolment in your database.
• Test your form to make sure it works.
• Now replace your Student ID text control with a combo box, and see if you can get it to display student names.
• Test your form again... does it work? If not, see if you can find out why it's not working.

The key is that, even though we want to display the students name to the user, we still need to store the student ID in the enrolments table - not the name! How do we do that? TechOnTheNet has a great tutorial to step you through the process, see Access: Bind combo box to primary key but display a description field. Don't you love the information age! You might want to print it out.

See if you can apply this to your Student ID field, as well as the Course ID field, and test out your Enrolments form.

Challenge: See if you can modify your Student ID combobox so that it displays the students name in the format "lastname, firstname" (you'll need to join lastname and firstname together somehow in your query.)

Once you're happy with your combo boxes in the Student Enrolments database, practise with your Traffic Fines database!

Mountain Music Shop

After a 30min trivia quiz on Access and VBA (and a break!) we're going to have a go at our next Database Design problem:

You and a few friends have just been employed by a local music shop that sells DVDs, CD’s, Videos and games. They’ve heard that you’ve got some experience with designing databases and are offering you all a pay rise if you can together come up with a design. The boss of the place has worked out that she wants the following information to be collected for each sale:

· LastName

· FirstName

· PostCode

· SalesDate

· Title

· MediaType

· Artist

· Price

· Quantity

Knowing that the first commandment of Access is:

“Thou shalt design normalized tables and understand thy fields and relationships before thou dost begin,”

you sit down to design your database schema together.

Hints:

· What primary tables will you need – tables that contain information that isn’t dependant on any other info in the database?

· When you’ve got a design, does your design allows a customer to purchase a number of different items in one sale? How can you further split thy database to allow this?

VBA Challenge 3!

Just to get us warmed up, here's our weekly VBA challenge!

Finally, you're going to create your own game, based on the vba warmups from the last few weeks!

In your VBAChallenges XLS file, create a Sub called GuessingGame. Our sub will do the following:

1. Create a new random number between 0 and 100 (check your previous challenges to find out how to do this).
2. Ask the user to enter a number.
3. Check whether the user has guessed the number correctly - if so, you should use a MsgBox to say "Congratulations! You got it!"
4. If the user's guess is higher than your number, your program should respond with a MsgBox saying "Too high", or if it's lower, "Too low"

Test this out by using a breakpoint and watching your code as it runs to see what the number is and whether you program gives the right response.

Once we've got that working, we'll modify our if-statement into a while loop so that our game will keep on asking you to guess the number as long as you don't get it right!

Advanced Queries in Access

Just in case anyone struggled with the exercises in our text about the more advanced queries in Access (such as Parameter Queries and using Totals in queries) you might want to check out Using Parameters with Queries and Reports from Microsoft, as well as Calculating Totals with Access Queries.

Warming up with VBA II

OK... ready for this weeks VBA warmup?? Try creating subs for these two problems below (using the InputBox and MsgBox functions, together with two mystery functions that you'll have to find!)

1. Before the sale of Telstra, Sol is keen to rename the company back to it's previous name of Telecom. Create a new Sub called Telecom that will ask the user to enter some text. Your sub will then modify the text entered by the user by replacing the word "telstra" with "telecom" and display the result in a MsgBox.
2. Create a new Sub called Translate(), that asks the user to enter some text to translate. Your sub will then replace any occurrence of "Hello" with "Bon jour" (or a word from another language that you are familiar with) .(Remember to do this in steps, testing every time!
   
3. Modify your Translate() sub so that any occurrence of the word "what" is replaced with "was" (or a word from another language you are familiar with!)
4. Modify your Translate() sub so that any occurrence of the word "dog" is replaced with "hund" (or the relevant word from another language that you are familiar with!)
5. Create a new Sub called RandomNumber that will display a random number between 0 and 100 in a MsgBox each time you run it.

A Traffic fines database!

You're currently employed as an IT support person at the local police station, and you nearly fall over when you are asked to do some data entry, only to find the following "database" being used to record traffic fines!?

DriversLicenseID	FineType	IncidentLocation	DriverName	FineAmount	Reporting officer	Date
26545LD	Speeding (-15)	Springwood	Harry Potter	$350	Jenny Rainer	12/5/05
65475TN	RedLight	Katoomba	Bob Smith	$200	Jeff Skillset	15/5/05
75163LD	Speeding (15+)	Lawson	Rachel Rogers	$350	Nadia Smith	20/5/05

What's wrong with this database? Can you see how it might be possible to enter inconsistent information? (Unfortunately I can't get the whole table to display, but there should also be a "Reporting Officer" column and a "Date" column).

We'll see if we can design a new database that is more efficient and consistent!

Over the past few weeks, we've been practising the task of improving database tables by breaking them down into smaller tables with relationships. The technical term for the process that we've been practising is Normalisation. It's probably a good time now to find out a bit more about Normalisation as we tackle our Police Fines database. After a break, take 10mins to read this useful Microsoft Knowledgebase article: Database Normalization Basics. We'll go through the example there together, discovering 1st, 2nd and 3rd normal form.

A prototype for Student Enrolments

Last week we started designing the tables for our Student Enrolments database.

This morning we'll work in small groups to do the following:

1. Check the table designs from last week, (the database Student Enrolments is on the P:, note - there might be some mistakes... or not!)
   
2. Add some sample data (for eg, 3 courses and 8 students)
3. Discuss together how you will enrol a student in a course (you'll need to add at least one table to your design)
4. Make the appropriate changes and test (Don't worry about forms or making your app easy to use just yet, our priority is that the design is correct).
5. Enrol all of your students in different courses.
   

We'll then get together and take a look at the different designs. If you finish early and are waiting, you might want to try the following:

• Create a query which displays all the enrolments in a way that is useful to users
• Create a report which shows all the student enrolments for each course
• Add totals to your report so that it automatically calculates how many students are enrolled in each course!
  

All about Access Queries

After some warmup fun with VBA macros, we're going to start our day by seeing how all the VBA Macro stuff we've learned in Excel works exactly the same in MS Word! (We'll be recording and editing macros in Word in no time)

After a brief break, we'll get a chance to work in pairs or small groups to have a go at creating the Student Enrollment database that we began designing last week. Our aim is to create the necessary tables, link them together with relationships so that we can enrol students into courses! We'll get together to discuss the pro's and con's of our implementations.

Finally, we'll tackle some of the more advanced Queries in Access. Specifically, we'll:

• Create queries based on multiple tables
• See how we can use calculated fields in a query
• Use special aggregate functions in a query (like summing or averaging the results of our query)
• Create action queries (to delete or modify our data!)
• Create parameter queries that ask us a question before running the query!

Any time left over will be for working on our second assignments! (Our Excel applications) See you then!

Fun with VBA functions!

So far we've only been using the built in VBA functions (such as MsgBox, CInt or IsNumeric) as we've needed them. Today we're going to have some fun finding out what else is out there! Check out the list of VB functions at Microsoft's Visual Basic Scripting Functions (right-click and open in a new window).

How many of the functions do you recognise? We'll see how many we can count in class!

Open a new Excel Spreadsheet and save it as "vba_functions.xls". See if you can use this reference to achieve the following:

1. Create a new Sub call Capitals that asks the user to input some text (using an Input box) and store it in a variable. Your sub should then use a MsgBox to repeat the text back to the User. When you've got that working, modify your function so that when your MsgBox pops up, the text is all in upper-case (capital letters).
2. Create a new Sub call Reversi that will ask the user to enter in some text, and will respond with the text completely reversed! For e.g., if the user types in: Hi there!, your Sub will then display: !ereht iH, in a MsgBox.

A student Enrollment database

You've been poached by a local community college (who heard about your excellent IT skills) to design an enrolments database for use next semester.

All that's required at the moment is a bit of a prototype that will show the database structure etc. The college has given you the following information so far (but it might change in the future):

• Courses cost $500 for Cert IV level, and $1000 for Diploma.
  
• Each student can be enrolled in any number of courses.

Unfortunately that's all the info they've given you at the moment, so you'll have to work with it! Start trying to figure out the data that you'll need to store in your database, and if you're keen, grouping it into tables!

The power of pivot charts'n'tables...

... but what is a pivot chart or a pivot table? Why would we even want to use one?

See what you can find out! If you find a link that might be helpful for others, then add a comment back here with a link to the page you found.

After finding out a bit about them, we're going to download an example file from MS and create a few Pivot Charts to see why they might be useful.

The 10 Commandments of Access

And it came to pass that the cries and lamentations of the Access newbies were heard on high by the gods of the Database, and their hearts were moved to pity for their followers. And they opened their mouths and spake, saying: "Nevermore shall the young and innocent wander witless on their journeys!

Checkout the 10 Commandments of Access from the Access Web :-) Which laws are we breaking or following!? My favourite is this one:

8. Thou shalt not copy and paste other people's code without at least attempting to understand what it does.

Which one do you reckon will be the most useful commandment?