Wednesday, 25 March 2009

Scammers and Young Tweeting

Hmm, so it seems that it's news that scammers take advantage of dodgy internet links to persuade people to click links and download oh so helpful anti-virus software.

I did kinda think that the days of people actually clicking on those buttons that appear as ads on web sites were over perhaps? Maybe not...
Anyone clicking on a booby-trapped page is then instantly re-directed to the site hosting the links to the fake security software.

Once they arrive, visitors are bombarded with pop-ups warning that their PC is infected. To clear up the infection, users are told, they must download and pay for anti-virus software which typically costs about $50 (£34).

'Scareware' scams trick searchers, BBC website 23/03/2009
I guess it's maybe the use of search engines that makes this a newer thing - up by over 200% in 2008 apparently.

Maybe teaching primary school kids how to use blogs and twitter will improve things? You'd hope so anyway.

Saturday, 21 March 2009

8 out of 10 parents say...

...they know what their kids are doing on their computers.

Although apparantly they seriously underestimate how long they spend using them.
UK parents believe their children are online for 18.8 hours per month. The true figure is 43.5 hours

From: Children work round web controls, BBC website 18/03/2009
Yeah, OK. Given that viruses seem to be rife on home machines that suggests many parents can't manage to install and update antivirus. If they can't do that then how do they know what their kids are doing?

Although it seems that Geek Mums are on the rise. Maybe there's hope then.

Thursday, 19 March 2009

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.


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

Wednesday, 18 March 2009

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

Thursday, 12 March 2009

Cyber crime report

Interesting report from the BBC Click! programme team about cyber crime.

Lots of good stuff on what happens and how to stop it happening. Well worth a read.

Wednesday, 11 March 2009

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?

Tuesday, 10 March 2009

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.

Saturday, 7 March 2009

Data Overload - need Protection...

Who's got your data on their computer?

Do you know? Probably more people than you might imagine - school, doctors, hospitals, supermarkets, the tax people, the police (perhaps...), anyone you ever registered with online or filled out a paper form for. The list could be, and probably is, endless.

But do you know what they do with your data? Or, rather, what they're allowed to do with your data?

There are rules - the good old Data Protection Act of course.

But does every company or organisation play within the rules?

Seems not.

Building Data Regulations:

Consulting Association, a company based in Droitwich (that's near Birmingham, sort of) certainly don't seem to have done so.

They hold data on people who work in the building industry. Big companies - Balfour Beatty for example - would go to them to find out about people they might employ. I suppose that might be quite useful: a company who can tell you what qualifications your future employee might have or what their experience is for example.

But, you see, amongst other things the firm would warn employers about possible "trouble makers".

You know, union organisers.
Following a raid ... investigators discovered that the Consulting Association's database contained the details of some 3,213 workers, the ICO said.

Employers paid £3,000 as an annual fee, and £2.20 for individual details, the ICO said. Invoices to construction firms for up to £7,500 were also seized during the raid.

From Firms in data row deny wrongdoing - BBC News report 6/3/09
Now, I didn't know being in a union or being an organiser was an offence. I thought it was just common sense - I've been a union organiser in my time. It means I might be prepared to stick up for myself in an argument with an employer. I might know about the rules and regulations surrounding things like over time or health and safety.

This is common sense - but it seems that, surprise surprise, the big firms don't like dealing with "union organisers". People who know their rights.

Or, it seems, people who might raise concerns about health and safety matters. You know, on building sites. Like, that isn't important or anything is it? Or about asbestos. The stuff that can kill you if you breath enough of it in: not straight away (like falling from scaffolding that's not been properly put up...) but in years after horrible pain.

You know, you wouldn't want to actually employ someone who'd made a fuss about any of that sort of stuff. Would you?

That stinks. Workers have a right and, to me, a duty to protect themselves. No wonder the Information Commissioner says that the public need to assert their data right.

Data Protection to the rescue:
Fortunately this has all come out into the open. And, even more fortunately, it seems that Consulting Association has fallen foul of the good old Data Protection Act this time.

You see, they didn't register with the Information Commissioner. They didn't stick to the rules. The company that noted that one of the people on its database was a problem because he was "Irish ex-Army, bad egg" didn't cover one of the most basic steps that anyone holding data about anyone else needs to do.


Thursday, 5 March 2009

A few Project Management links

Oooh, a Project.

I love a good project. It's just the management bit that's a pain...

Some things that might be slightly useful - all from Wikipedia:
The wiki page on Project Management has a series of Project Management Stages on it. These are probably a good base to use to break down a project.

  1. intiation - which "determines the nature and scope of the development"

  2. planning or development - which designs and perhaps creates a prototype which is tested

  3. production or execution - which produces the actual thingy

  4. monitoring and controlling - which includes checking that everything's going well. The wiki page on this is well worth a look for ideas. It can also include maintenance - making sure that whatevers produced continues to work

  5. closing - finishing off and handing it over. In the real world this is quite often the "getting paid" section!

It's well worth taking a look at the wiki section on that for ideas. Promise.

Wednesday, 4 March 2009

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.

Monday, 2 March 2009

Careful what you type!

Hmm, so my meetings are boring?

Good job I didn't put that up on Facebook or somewhere then; I could get sacked, rather like this Essex teenager did:
A 16-year-old girl from Essex was fired after she described her office job as "boring" on her Facebook page.

Kimberley Swann, 16, of Clacton, had been working at Ivell Marketing & Logistics, in Clacton, for three weeks before being fired on Monday.

"I think they've stooped quite low," she said.
Facebook remark teenager is fired, on the BBC Essex site

"Stooped quite low"?

Of course, if she hadn't added half her workmates as friends then I don't suppose they'd have seen the comment. Some people might say this raises issues about privacy and the internet. Yes, it might - but it also raises issues about how careful you have to be about putting stuff up on the internet that you don't want other people to see.

It's all very easy to quickly blog something, or stick it on a Facebook profile or Tweet it or whathaveyou, but once it's there it can be tricky to get rid of, particularly if you put it anywhere near the public domain.

So, hard done by for getting sacked? Or a little bit naive about what the internet and the ubiquitous computing it brings with it means?

I'll go for the second of those please Bob.

Oh, I should just say how much I adore the meetings I go to for my proper job by the way...


Stuck in meetings all day.

Ugh: this is one good reason not to grow up and get a proper job!

Meetings all day yesterday (yes, that was a Sunday...) and all day today. My brain will try and rebel I'm sure...