Here we are again with some Smatterings of useful information for you. There's no better time than now to be thinking about working efficiently and keeping on top of all your admin and information - so this is what we've focused on here. We hope you find it interesting! And if times are looking tough for you, remember we can help more than ever. Whether it's credit control, stock reduction, growing without taking on more staff, preventing mistakes, better management information to see what's happening, or anything else similar that you need, computers are good at it!

Software-Matters has branched out! Here's a quick introduction to the web services we now offer. Keep an eye out for more details in future issues of Smatterings. Databases and websites go together extremely well whenever you want to collect information from the people visiting your site. The most obvious case for this is a Contact page – visitors fill in their details and you can collect and store those details so that you can then reply, send out an information pack, follow-up, or whatever.

Other situations where you may want people to send you information through your website range from questionnaires to feedback forms to providing a help desk. In each case, the marriage of database and website will allow this functionality to be blended seamlessly into your existing site and into your office system. Similarly, you may need to supply information to visitors to your website via a database. Examples here are search facilities for product or parts, and customers being able to log in and see details of their orders and payments.

And why stop there? The design of your website is incredibly important – if it's not clear and easy to use most people won't bother to read it, however well the Contact or Feedback page works! So we are now offering website design services as well. Whether you want to make changes or additions to your existing site or you want a brand new look, we can do it for you.

And of course there's no point having a beautiful website that no-one can find. Search engine optimisation is incredibly important if you hope to gain new business through your website. We can optimise your existing site or a new site we're building for you to help you appear high up on the list whenever people search for the products or services you have to offer. All these new services are backed by the Software-Matters philosophy: we offer a straightforward, no-nonsense and jargon-free service, and if it's not in your best interests to do the work we will tell you.

Last time we asked you which article you'd be most interested in reading. This is what came out top, so here it is!

Talking through your requirements By far the most important part of the whole process is talking to you.  The better we understand what you need from your database, the easier it is to build and build right first time.  This is why your consultation and spec meeting are important; the more we can get right now, the smoother the development of the system will be.

Designing the Database and writing Your Spec Document
The next stage is to work out what pieces of information you need to store and how they should be organised.

This part of the design process can be quite complex, but someone who is used to designing databases (like Philippa) can usually see it all in their head.  Sometimes it involves drawing things out on paper.  Whatever, it's another key stage, as this part of the design forms the foundations of the whole system.  Get this wrong and things can start to wobble later!  This part of the design will become the Back file, which is where all of your data will be stored.  The Back file consists of a number of tables, each of which stores information relating to one thing.  So for an address book system you might have one table that stores information about people and another that stores information about companies.  The tables are then joined together by relationships, so for this example there would be a relationship connecting the people to the company that they work for.

Once we've worked out what information belongs where and how it all connects up, we can start thinking about the screens and reports and any other bits and pieces you will need to use the database – either for getting information into it or for getting information out of it. 

This part of the design will ultimately become the Front file. The spec document we produce from all of this is designed to be readable for you, and to contain the information we need in order to create your system. We would normally expect you to need us to revise the spec document a bit before we start work.  It's well worth the investment of time here as it is much easier to changes things about now than it is to have to change the database later.

Building the Back file
With the design stage complete, the Back file is fairly straightforward to build.  We create each table in turn, with each piece of information in that table becoming a field.  Each field has to be a single data type.  The data type depends on what information is being stored, whether it is text, a date, a small or large number, a percentage or an amount of money (among others).  Once the tables and fields are created, the Back file is almost complete; it just remains to add the relationships and enforce them.  This ensures that your data connects up correctly; it is a quick but vital step.

The Front file – starting off
Now we come to the Front file.  This is the file where all the visual elements of the database are stored.  It's also where any code we write is located.  In fact it contains everything about your system that isn't your data.  This setup isolates your data and thereby protects it.  It also allows us to make later changes to the Front file with absolutely no risk that we will alter your data.

The starting point for the Front file is usually to import the Welcome screen (the blue screen shown as the database opens) and the Front screen.  We have a standard style for all the systems we create, so these elements can be taken from a template to give us something to work from. We also need to connect the Back and Front files together.  This is done by linking the Front file to the tables in the Back file.   The Front can be linked to tables in more than one Back file if necessary.

Building the screens
Putting together all of the screens in your database is a big job.  We do use the built-in Access Wizards to help speed up the process, but this is still where a lot of our time is spent.  Each screen is linked to a table or combination of tables.   The complexity of a screen is usually dictated by how much needs to happen automatically behind the scenes as users enter and amend data.

The library screens are the simplest to build.  These screens are based on the very simple tables that store information not often changed.  Other than an Add New button, there is normally very little that needs adding to these screens.

The other screens, the ones used on a day-to-day basis, are almost always considerably more complex.  We start by building them like simple library screens and then expand on them.  We normally have lots of lists to work though to make sure we include every feature that you've asked for, and then again to make sure we've thoroughly tested everything too.

Building reports from queries
The main stage left is building the reports.  Reports are usually a summary or analysis of certain information or a breakdown of some data using certain criteria.  To build a report we start by building a query.  Queries allow us to work out which data we are interested in and how it fits together.  We can use the query as the basis for a report through the built-in wizards, which drastically speeds up the formatting of the report.  Once the basic information is appearing correctly, we look at adding totals, etc, if this is appropriate.

A database is born And there you have it!  Although some systems can become much more complicated and have a number of elements added later, almost all of our systems started life through this process.  From here the system is delivered to you, usually in person, along with the User Notes and Technical Notes that we've compiled as the database has grown.  We show you the ropes, and then you're away!

If you are looking to reduce costs, like many other people at this time, then let us help you! Use our free initial telephone consultation to identify ways of improving efficiency and doing more with less. And don't forget we can help with web-based solutions as well as desktop-based ones. These apply to new and existing customers.

And for our existing customers, we are offering a loyalty bonus this time. It's a 10% discount for any new projects or significant add-ons initiated during September 2008 for completion by the end of the year.

NAME: Katherine Louise Rial AGE:  26 RIGHT-HANDED?:  Yes CAR:  Ford Focus HEIGHT:  5'5" Q: OK, so we know your name, but have you ever had any nicknames? A: My family calls me Katie, and many of my friends call me Kath (because they're lazy and Katherine is a long name!).  I've been Kafrin occasionally, and I have one friend who calls me Chopsy – something to do with being bright red all round my chops (cheeks) for a few days in primary school due to an illness. Q: Where are you from? A: Born in Huddersfield, West Yorkshire, but grew up in Peterborough, Cambridgeshire.  I now live in Wells, Somerset. Q: Who do you live with? A: My husband Ed and our dark brown hamster Chambray. Q: Which university did you attend and what did you study? A: The University of Birmingham, firstly to study for a BSc Natural Sciences (Maths & Physics) and later for an MPhil (Research) The Science & Engineering of Materials.

Q: Tea or coffee? A: Hot, strong tea. Q: What hobbies do you have and how did you get into them? A: My main hobby is archery, which I got into at university.  I'm now involved with a local club and also with a group of ex-student archers who gatecrash some of the Uni events!

Q: Do you have any siblings? A: One brother, Steven, who's two years younger than me. Q: Are you a morning person? A: Definitely not!
Q: Any interesting or strange facts about you? A: My first term at Uni I managed to slice my thumb open with a kitchen knife and sever the nerve.  One operation and six months of occupational therapy later I regained full movement but am officially 3% disabled due to a reduction in strength and sensation.  Now my left thumb tingles slightly all the time.

Access is a program that most people know about but are not familiar with.  Frequently we will be asked why we are using Access instead of Excel for a system.  And vice versa, since we normally work with Access, why are we using Excel for a particular system?  The short answer is that we use the best program for the system we are building.  Access and Excel are similar but different!  They are designed for different purposes although they do have some areas of overlap in what they can do for you.

So what are the differences between Access and Excel?  If you search the internet for an answer, you will probably find very little other than the statement, "Excel is a spreadsheet package, Access is a database package” – whilst accurate, this is not exceedingly helpful.

In many cases, people use Excel because it seems more straightforward and user-friendly and is easier to grasp.  Often they've used it before and feel comfortable with it, but this doesn't mean that Excel is the right tool for the job.  Essentially Excel is for number-crunching and Access is for handling data records.  So, for example, you would be unlikely to do advanced statistical analysis in Access, and you would be unwise to handle multi-dimensional information records in Excel. Take stock management as an example.  A company setting up a new system to keep track of its stock may well create an Excel spreadsheet that lists all the items in the stock room along with a number to show how many are currently in stock.  Later on, the company wants to keep better track of where its stock is being used, so a new spreadsheet is set up to keep track of stock movements.

Now, whenever someone takes an item out of stock they have to add a stock movement in one spreadsheet and change the stock level in another.  Not only does this take time but the possibilities for human error increase dramatically, whether someone reads or types a part number wrongly, clicks on the wrong row, or forgets to update one of the spreadsheets.

In Access this can all be drastically simplified for the user.  A screen for recording stock movements can automatically update stock levels as well.  In addition, it will prevent the user from entering invalid part numbers.  We can set up a report so that at the click of a button you can view the current stock levels and print them; another could show stock movements that occurred between particular dates or for a particular part.

Having set up the Access database it is also much easier to incorporate additional information than it would be in Excel.  You can store suppliers' details and link them to the parts they supply.  You can raise purchase orders.  You can record deliveries, and once again the system will update the stock level automatically.

And this is just one of many examples where Excel is often used but Access is better suited to the job. 

Excel does of course have advantages over Access in some areas.  You can highlight or colour individual pieces of information much more easily in Excel than in Access, for example.  If you want to quickly display some data in an easy format and the data truly stands alone, it is much quicker to produce something in Excel.  And for serious number work, Excel is the right tool.

Another main area where Excel is strong is graphs and charts.  Although charts can be created in Access, Excel provides greater flexibility and ease.   Where graphical output is required, we often link Access and Excel and get the best of both worlds! The Web is another area where Access scores.  You can easily collect information into an Access database using web pages.  This is useful if your users are spread over more than one location, or if you want to collect information from people visiting your website.

1) How can you make your column headings stay visible at the top of the screen when you scroll down the page (and at the side when you scroll right)? To do this, you use a feature called "Freeze Panes". To freeze both row and column titles, go to the first cell that will be allowed to move off the screen. Then go to Window -> Freeze Panes. If you now scroll down the page you will see your fixed row(s) at the top always, and if you scroll right you will see you fixed column(s) at the left always.
To remove this effect, click anywhere on the sheet and go to Window -> Unfreeze Panes. To change which rows/columns are frozen, you need to Unfreeze and then start from the beginning.
To freeze just a row heading, select the row below the one containing your column headings by clicking on the row number at the left hand side. Then Freeze Panes. Similarly to freeze just column titles, select the column to the right of the one you want to fix by clicking on the column letter at the top of the sheet and then go to Window -> Freeze Panes.

2) How do you stop the cursor jumping to the next cell when you press Enter? For some types of work, this is a useful feature, but for the rest of us it can drive you bananas. So, to switch it off: In Excel go to Tools -> Options... and then to the Edit tab. Untick the box for "Move selection after Enter" and click "OK". If you now type something into a cell and press Enter, you will see that the cursor stays on the same cell.

3) How do you rename your sheets to something that helps you identify them? First find the sheet tabs at the bottom of the screen. By default these are labelled Sheet1, Sheet2 and Sheet3. If you double-click on a sheet tab, you will find that the sheet name becomes highlighted. You can now edit the sheet name simply by typing the new name. When you are finished, press Enter.
If you have difficulty double clicking on the tab, you can instead right click on it to bring up a menu. One of the options is Rename; choose this and then type the new sheet name and press Enter. In this same shortcut menu, you can delete whole sheets. So, don't leave any empty sheets after the ones you have used – delete them!

This time we test your emailing skills with our quiz. Don't forget to look out for the answers in the next issue! 1) How can you have your incoming emails automatically put into different folders depending on who they are from? 2) How can you stop emails from a particular address going into your Junk Mail folder without adding the address to your contacts list? 3) How can you set up text to appear at the end of every email you write?