Justification of Database software

This is a simple three marks to get.

Check the markscheme first - it's AO2 Row 3. Then this is what I'd do:

1. Clearly state exactly which version of Access is being used. Without this it won’t get 1 mark
2. Say that they already have it and so don’t need to buy any new software in
3. Say that it’s the only bit of database software they have so there isn't a real alternative
4. If anyone in the client organisation has any familiarity at all with it then say so – they may have used it at school or in another job for example or done some very basic training with it
5. Say that you need a number of tables for different entities (name the entities) and therefore need an RDBMS (a Relational Database Management System)

You could add about the ability to build forms to create a user friendly interface using things like drop down boxes, calendar controls etc… and use security features etc… Linking this to client needs (briefly!) isn't a bad idea.

Normal, Extreme and Erroneous

And that's just the usual Year 13 class...

So, this is about testing and test plans.

When you create them - and this applies equally for unit 10 (spreadsheets) and unit 3 (databases) you need sets of test data. That means data you want to use to test that your system works properly and check that it gives you the expected results.

You need to be really clear about the data you're going to use - that means I expect to see precisely what you're going to input - 10 in that box, Blue in that option and so on.

Now, the data needs to include 3 types:

• Normal - this is data which you would expect. Standard data that the system should accept without any problems
• Extreme - this is data at the extreme range of acceptable. You sometimes see this called 'boundary values'. You need to test each side of the boundary - so if a data value should accept values from 1-9 then I'd expect to see 9 and 10 being tested; if it has to be a maximum length then I'd expect testing at that length and one above the length and so on
• Erroneous - this is data which is clearly wrong - data which should never be accepted by the system. You want the system to reject this data. So, if I wanted a value 1-9 then I'd test things like 14, 473535, -1, 0, 4.5 and red.

OK?

Copying? That's fine for reports.

OK, so you have a report which you want to use for more than one query.

You could make the report several times, but that's complex and time consuming and boring. And they probably won't end up looking the same because reports are a bit of a pig.

So, the easy thing to do is to copy the report

1. Copy and paste the report, renaming it as you go


2. Opening the report shows you that the same data is attached to it. Easy enough to solve if you know how - so open it in design view first.


3. Now click on the properties button on the toolbar

4. Make sure the All tab is selected and click on the name of the Data source in the first row.

5. Use the drop down at the end of the box to choose the name of the query you want to attach the new report to.


6. Close it all down, saving as you go and test it out.

Pips.

What's the password?

First off, be careful using passwords on your work.

If you forget the password you won't (and that means won't!) be able to access your work. At all. I can't, network managers can't, noone can. So don't forget it...

So, you have a database and you need to password protect it. Seems easy, but there's a crucial step that's more important than sneezing into a clean hankie. Here's how you go about it.

1. Open Access. Not a database, just Access.
2. Go File > Open and click once on the database you want to open. This will select the database rather than open it
3. Now, find the Open button at the bottom right of the dialogue box. There's a little arrow next to it - click the arrow. Yes, the arrow.

4. Choose Open Exclusive. The database opens.
5. Now, go Tools > Security and set a password.

Hurrah, it works!

Inputting a date and producing a report from it.

OK, so this one's a little complex.

Imagine. You have a query which requires you to enter something into a parameter - like a date or something. That query is then used to generate a report. But you'd like the search criteria you entered into the box to go on the report.

So, say there's a list of appointments for a particular day for a dentist or whatever. It would make sense to put the date the appointments are for on the report.

Step 1 - First create a form:

The entire purpose of this form is to enter a date (or two dates, or whatever you're searching for).

• You need a textbox (and it must be a textbox!) on the form to type the date into.
• Right click on the textbox and go into the properties. Change the name of the textbox to txtDate (or something similar).
  

• Save the form as something like frmGetDate

Step 2 - Next you need a query:

Say to list the appointments someone has on that a day.

Like this:
BUT – instead of using a standard parameter criteria in the date column ([Enter the date] say) you type:

[forms]![frmGetDate]![txtDate]

What this does is put the date you enter in txtDate on frmGetDate into the parameter search. Clever eh? Like this:

But, if you run the query it won’t play. There are good reasons for this.

Step 3 - Create a report based on the query:

Just use a wizard for now, you can go back and work on this later.

Step 4 - Now the clever bit:

What you have to do is run the report from the form.

So go back to the form and put a Command Button in it which runs the query – you do this under Report Operations.

This should work – but it won’t show the date you typed into the textbox on the report. Yet.

Step 5 - The other clever bit:

What you need to do is go back to the report in Design view.

Create a textbox on the report somewhere – probably best to put it in the header of the report by the way

Right click on it and in the Data tab you need to type:

=Forms!frmGetDate!txtDate

into the control source box. Like this:

Step 6 - Now, try it out:

Open the form, hit the button and see what goes on!!This is a bit tricky of course, but you should be able to get there.

There’s no reason why you can’t enter two dates in the form – just give them different names (like txtDateStart and txtDateFinish). You could then stick two dates into the query using these names and then the report. Logically.

There’s also no reason why you couldn’t use a calendar control or spinners to populate the textboxes on the form for that matter – you could also set default values and/or input masks on the form (right click and properties) of course.

Total amounts of well coolness.

Blanking Stuff on Forms

You have a form. You want the user to do stuff on it but there are some records you need the user to not be able to change. How good would it be to be able to blank those fields so that the user couldn't change them (and, while they're at it, screw the entire database up...)?

Quite easy.

• Get the form in design view and select the field you want to stop the user changing
• Click the Properties button
• Find the Enabled property. It'll say Yes. Bet you can't guess what comes next?
• Change the enabled property to No (I bet you got that right...)
  

Now run the form and check it works!

Date Not Now

Seems to be a trend.

Using >=Now() in a validation rule for a date. Trouble is it won't let you enter todays date if you do something like that.

So, don't use Now(), use Date(). Then it'll work.

Promise.

Welcome to the Hotel California (The Room Invoice Query Thingummy)

Plenty of room. Any time of year, you can find it here.

Especially with a brand spanking new IT system to sort out the room booking thingummy.

The tables are easy enough to sort out. You can see from the diagram that we've got tblCustomer linking to tblBooking which also links to tblRoom. Note that the links need to have their referential integrity enforced (oh, lovely...).

You then need a query taking data from the three tables to work out the length of stay and the price to get a final price sorted out.

This is where the screenshot comes in...

Err, click on the image and it gets big enough to read...

There are other ways to work out the length of time (using the Date Diff thingy), but for now I've stuck with a simple sort of method. I think it'll do the basics.

All you do then is multiply the length of stay by the Price per night to get a total cost. If you use the same method as in the Order Form Dilemma below you should be able to get that cost into a data entry form - although you may well find that there's no real need to use the sub-form method after all: there's probably at least one other way around this problem.

I wonder whether this can be combined with the Double Booking Solution, perhaps using an adding to a table method, to make sure that there's a way of not selling the same room twice.

That would be bad. Wouldn't at all suit the Hotel California. Such a lovely place.

The Order Form Dilemma

Dear Deidrie,

I have a bit of a dilemma with my ordering system. I try and I try but it just won't work.

Can you help me get it all fixed and lovely?

Worried of Saxmundham

Hi Worried,

No problem you, we'll have you sorted in a jiffy.

To start with, this is how your table might want to look:

Notice that there's only one set of information in the tblOrderLine. I've cut off the screenshot to make it more visible - there's a lookup table over on the right for those of you not familiar with the admittedly unusual example Worried has chosen here.

You'll notice as well that the referential integrity has been enforced. I always think you can never have too much referential integrity.

What might not be obvious at first glance is that there's no Primary Key in tblOrderLine...

Once you have your tables sorted it's plain sailing. The juice covered textbook has information on or about page 250 which might come in jolly handy.

It will involve setting up a query a bit like this one.

Notice there's more than one table involved there. There's also a calculated field at the end for the cost: Cost:[Price]*[Quantity] or something similar.

Oh, a tip: try sorting the field on Order Number. You might just find it saves a bit of head scratching later on.

Then you can set up an Order Form a bit like this one.

You need to get the Form Wizard to wave his magic wand on this one as you're taking stuff from three places: tblCustomer, tblOrder and qryOrderedProduct. Fun - slightly confusing fun the first time you do it, but in my experience most of the best things are.

You'll notice I've used some of the Cool Formy Things, like a Calendar control and a spinner, to increase the usability of the form. I've also taken a bit of time to get the layout as clear and usable as I can. I'm like that. Some people would say obsessive, others might just say odd.

So, Worried of Saxmundham, hopefully that might help. If it doesn't then I can recommend a nice crisp Chablis if that helps?

Best wishes,

Deidrie xxx

Cool formy things

So, here's a form.

You might want to open the image in a new window and have it handy. This might get a little complex (those easily scared might want to look away now...)

It's for a newspaper delivery database (perhaps more of which at some point). Specifically it lets the user update when a customer will be on holiday. It's only actually updating some of the data in the record by the way, which is entirely possible but might not have occurred to you.

First off, top right there's a combo box to skip straight to a record based on the surname - although the drop down will also show other key data. That's easy (it's just a combo box which you Find a record on the form... using).

Then there's a calendar control to enter the date the holiday starts. This is just a standard click the calendar thing - the juice covered textbook covers it on pages 228-229.

But, and here's where it gets clever, I added some code.

You'll remember (or be about to look it up...) that there's some code you need to add in the Calendar control VBA editing scary looking bit: something along the lines of Me.HolsBegin.Value = Calendar3.Value?

Well, what I did was add some code below that to simply make the HolsEnd box be equal to the calendar click + 7 in the first instance. It's kinda like using a default value. Honest.

Look, here's the screenshot...Then, to avoid having two confusing calendars on the same form I added a spinner next to the HolsEnd box to allow that to be adjusted manually but without having to do the annoying typing thing. The code for this is on pages 229-230 of the juice covered textbook.

Now, the clever bit.

I wanted a button to enable me to clear both dates easily and without any fuss.

Could I find a way to do this? Could I heck. Nothing from the standard stuff seemed to work.

So, I experimented. And, guess what, it worked. Like, first time dude! Must be my lucky day...

Anyway, here's what to do:

1. draw a button using the grey toolbar button thing
2. choose any of the options and go for the text - you can change it later
3. click finish to add it to the form (although you may want to give it a sensible name as well)
4. make sure the button is selected
5. hit the Properties button on the toolbar (or right click...)
6. find the On Click bit, click in it and then click the three dots next to it
7. choose Code Builder to go to the VB editor
8. now, delete all the code apart from the first and last lines in the section related to your button - it'll be called Private Sub Name_Click()
9. then add the code from the screenshot, using the names of your fields you want to clear

Heck, the code's easy isn't it? All it's doing is clearing both fields by making their value equal to nothing.

Easy. Lemon. Whatever.

And, yes, I do feel quite proud of that even though it's probably not that complex. Took 39 years and 350-odd days to get to that...

Which just goes to prove the foolishness of trying to teach new tricks to old dogs

Type your data right (please)

Look, just use the right data type for a field please!

Really, it's not that hard to do:

• do you want to do some maths with it? Make it number or currency then
  
• is it numbers you'll never want to add up? Make it text already
  
• dates or times? Hmm, Date/Time then? But change the Format to make it appropriate OK? (like, I don't need the time if I'm just recording a date do I?)
  
• is it only going to be answered Yes/No? Hmm, what about a Yes/No field type (it's called a Boolean variable if you want to get geeky)
  
• and, please, if it's going to be an ID number than use the Autonumber type. It makes so much sense

There you go - easy peasy. Lemon. Squeezy.

So think about it, OK?

Oh, there's a Default Value option?

Yes, there's a default value option in the field properties bit of database table design.

So use it already!

There's all sorts of reasons and situation when you might use this feature. For starters, you might have a common value that many times will need to go into the box. Here's an example - let's say it's a stock control system being designed by someone who is completely hypothetically called "Steph"...

The field here is one where the user can say whether or not a bill has been paid.

The chances are that when the stock is booked in the bill won't have been paid - a bill is sent afterwards (the designer knows this because she spoke the the client - she knows the client's needs...). So a No is by far the most common value to go into the field when the record is created.

So, we use a default value of No!

You can also see some simple validation and whathaveyou. Of course, an alternative might have been to have used a Yes/No Data Type rather than a Text field...

Now, the same database also needs to enter the Date In of the stock being stored. Here again we can use a default value - the chances are that most of the time the value will be the current date.

Again, the designer knows this because she spoke to the client about it and found out. For sure. Can I stress that this step is really, really important in all of this? Thanks.

Here's the field properties:

We can use Date() to get the current date in that cell.

Note the validation here.

It's possible, although unlikely, that a different date might need to be entered. A container might be fortgotten about or not entered for some reason. Because of that the validation rule has been set to accept any date from 7 days before today until tomorrow - a container might sometimes be logged on the system the day before it's actually present on site.

Do I need to tell you how she knows this?

Here's the Planned Date Out field properties:

The minimum length of time a container can be in store for is 1 week apparantly. Seems a bit odd, but that's the way it works. So the Default Value can be set to a weeks time - Date()+7. The Valudation rule here might need to be >Date()+6 actually - I think you'd need to test all of this.

---

Now, testing...

All these rules and default values need testing. This goes in the Testing section of a report - in the Test Plan. In all this you clearly have to test the Boundary Values as well as values which are obviously going to the right and those that are obviously going to be wrong.

So, if I'm testing this all today (10/3/09) then I'd be checking if I could enter 2/1/06 (hopefully no - it's clearly wrong), 9/3/09 (OK), 2/3/09 (shouldn't accept it - and it's a boundary value) and 3/3/09 (should accept it - it's a boundary value again).

This demonstrates the importance of a clear test plan at this sort of level - it shows you the detail of testing that systems like this need.

Adding stuff to a database field

So, you have a database table.

There's a field that you want to add something like Amps to. So it's going to be, like 13 Amps or 5 Amps or whathaveyou. Always Amps.

Easy to do.

The field type'll need to be text for this.

Then you simply set up the Input Mask in this sort of way:

This should work for text fields as well - you simply need to make sure the input mask characters work properly the way you want them to.

The section in the textbook on Input Masks is p.28-29 - assuming you haven't spilt orange juice over your textbook so you can still read the pages.

You can do the same thing for an Autonumber field as well - say that you need to add a standard set of text before a stock code for example. In this case the Input Mask simply needs to be something like "STA"000 and the field type Autonumber.

All of this adds usability to your database. It makes it easier for the user to use it. This is a Good Thing.

Testing, Testing, 124

Test plans rock.

Really, they do. They're sooooo important. But they're always the stuff people leave out.

One of the key things with a Database Testplan (for the AQA Applied A Level course btw) is that the plan needs to relate clearly to the evaluation criteria. This is totally like crucial dude - check out the markscheme AO4 Line 3.

So, you can have all of your generic testing stuff like field sizes, queries and whathaveyou, but if you don't have specific testing points related to your evaluation critieria you're not getting very far down the mark line.

Here are a couple of someone's evaluation criteria. We'll call him "Alex" (it's OK, he's not a duck...):

Test Number 11
Test: User can edit data (eval crit 2)
How tested: I will try to edit data in tables
Expected Outcome: Able to edit data

Test Number 12
Test: The database prints weekly and daily reports related to that week/day (eval crit 3)
How tested: I will try and print reports which automatically print the records for today or this week
Expected Outcome: The right data will be printed without anything which is not needed

Ideally you want to refer all of your evaluation criteria to points in your test plan. I'll say it again: this is in addition to the generic testing points that you might have. That's really important. Like, really.

Or the ducks will know.

Date Validation for Databases

So, you need to validate a date for your database.

OK, so there are some easy things to do:

• you can set the field type to Date/Time


• you then need to set the Format in the Field Properties to Short Date probably - depending on the format you need

What else can you do? What can get you more marks and make you look like a champion database dude?

An input mask is possible - use 00/00/0000;;_ for example.

You can then set a validation rule of something like Date().

But you might want to get funkier than that. Look at the screenshot here.

This shows that we're validating the date to be between today-43800 days and today-3000 days. That's days OK - so I got the calculator out and figured out that 120 years old was 365 * 120 - easy as that.

This allows you to set a range of valid dates for, say, a date of birth if you need someone to be at least a certain age. The -43800 days bit means someone can't enter 1645 by mistake!

I've also set the Required property to Yes. This means that they user has to enter a valid date of birth. They aren't allowed to leave it blank. Take a bit of care with required properties though: if there's any chance that the client might not need to enter that field for any item then don't set required to yes!

Delivery Dates:

You might also need to set a delivery date (or a return date for a book or something similar).

Take a look at this for some ideas:

This time I'm setting a validation rule of between Date()+1 (i.e. tomorrow) and Date()+35, which is 35 days time. That's the set window I've got for delivering the product someone's ordered. The delivery date field has to be set within that window.

I've also set a default value here. My standard delivery date is tomorrow because the company does a next day delivery service. It will save time and effort having to type in the date each time if most of the deliveries will be made the next day.

For a library database I might set the default value for the return date to be Date()+14 for example - depending what the standard loan period is.

You can overwrite a default value, but it saves some time entering things that are going to be standard for many records if you use a default value.

Of course, you could get really clever and set up a calendar control on a form to make entering dates even easier - just like you see on the web for hotel booking or buying train tickets for example...

Double Booking Solution to a Database

We were talking about this at work the other day - how do you solve a problem like double booking a hotel room or a car hire (or, for that matter, Maria)?

There are various solutions, but a dead easy one it laid out in the textbook we give our sixth form AS students. If you had that book in front of you then you'd go to page 193 and find that it was quite complex looking, but really dead easy. There needs to be some thinking done as to how exactly to get it work of course, but we'll get there with that. Perhaps.

I'll add some screenshots to this when I get the chance.