Some useful smatterings of information, and some more substantial ones too, to help make your working life easier.
Have Some of Your Spreadsheets Outgrown Excel?
Many small businesses start out keeping track of their data in an Excel spreadsheet. As your business grows and becomes more complex, so your spreadsheets grow and become more complex, until eventually Excel really isn't suitable anymore. When you get to this stage it's time to think about switching to an Access database.
So what are the signs that you've outgrown Excel and should switch to Access? Here are seven key indicators that your data needs a database like Access.
1) You need to see the same information on multiple spreadsheets. In a spreadsheet system, you will often end with spreadsheets that are and should be separate, but which use the same information. For example, your customers need to appear on your order information, and a summary of the order data needs to appear on the invoices. Usually, Access is the better tool for the job. Access is specifically designed to connect up different sets of information. Remember, if you have to enter the same thing more than once, Access can probably do it better.
2) You have more than one person needing to view or edit the data at the same time. Initially it is likely to be only you that needs to use the spreadsheets. But as your business grows and you start to take on your own staff, or as the job your spreadsheet does grows, you will find that you want more people to be able to view and edit the data at the same time. In Excel you each have to wait until no-one else is using the spreadsheet to open it and save changes. In Access however you can have multiple users all making changes to different pieces of information simultaneously.
3) You often enter the same data more than once. Do you end up needing to enter the same person's name and email address in multiple rows? Do you want to see details of a product whenever you enter its code number? Whenever you find yourself typing in data that already exists elsewhere in your spreadsheets, you're making extra work for yourself. You're also increasing the chances of typing errors cropping up. Access, on the other hand, is designed to link data together so you only enter new information and you do it once only - you never have to repeat yourself.
4) You regularly produce reports from the data. Do you produce monthly reports? Or a list of outstanding invoices? If you are copying and pasting data in Excel or have spreadsheets with complex formulae to pull the information together, you should definitely consider moving on to Access. Access is ideally suited to collating data and presenting it in a useful format. You can restrict the data with criteria that you specify each time you produce the report. You can display the data grouped by, for example, Customer or Product. You can also use these same tools to produce invoices and receipts.
5) You need more than 65,536 rows. Excel has a limit of 65,536 rows per sheet. If you find yourself approaching this limit, which is easier than it sounds, you will have to spend a significant amount of time reworking your spreadsheets. Access does not have this limit.
6) You need more than 256 columns. Excel has a limit of 256 columns. If you need to store more data than this, you could set up a complex system with multiple spreadsheets. However you will probably be better served switching to Access. Not only does Access not have this limit, but as your data will be stored more efficiently you will most likely not need this many columns either. Your data will be easier to search through and quicker to access.
7) You want to make regular changes to the layout. Do you often want to add or move columns in your spreadsheets? Do you want to change easily the order your data is displayed in? Any design changes in Excel carry with them a high risk of accidentally altering your data. This is at best problematic; at worst you lose information that is impossible to recover. In Access however the data is stored completely separately from the layout, so you can make as many changes as you like to help you view your data without ever risking changes to your data.
So if any one of the above points apply to you, or you think they will in the near future, you should think about switching to Access. And best of all, your existing Excel spreadsheets can be used to populate your new database, so you won't lose any data and it will all be available in your new system!
Want more information? Check out this issue's offer for details of how to contact us and get a fantastic deal on your new database system!
You can set Word & Excel 2007 to save files in a compatible mode by default so that you can send your files to people with earlier versions. Alternatively, if you use the earlier versions, you can download converters to let you open 2007 files.
out·grow ( out-grō' ):
tr.v. out·grew (-grōō'), out·grown (-grōn'), out·grow·ing, out·grows
- To grow too large for.
- To lose or discard in the course of maturation.
- To surpass in growth.
The American Heritage Dictionary of the English Language, Fourth Edition
We're looking this time at some of the standard Windows Accessories programs - after all, if you make the most of everything that comes with Windows you don't have to spend as much on other software. The programs below are all on the Start menu under Programs, Accessories.
1) How can you quickly find the total and average of a list of numbers?
2) How can you insert part of a screen dump into a document?
3) How can you quickly enter the current date and time into your notes to help you log your time?
Abbreviations Quiz answers
Check out the answers to last issue's Abbreviations Quiz. How many did you know?
On your computer
kB, MB and GB: kilobyte, megabyte and gigabyte. These are measures of the amount of memory or computing power available. A bit (binary digit) is the smallest unit of computer memory. A byte (binary element string) is usually a sequence of 8 bits. This is enough to store one letter of the alphabet. A kilobyte is 1,024 bytes, a megabyte is 1,024 kilobytes (or slightly more than a million bytes), and a gigabyte is 1,024 megabytes (or slightly more than a billion bytes).
RAM: Random Access Memory. This is the memory that your computer uses to run programs, and therefore this is the memory that affects how quickly your computer runs. Any individual part can be accessed without reference to what is stored around it, hence the term 'random'.
ROM: Read-Only Memory. Indicates memory that can be read from but not written to. CD-ROMs are read only, you cannot save your own data onto them.
CPU: Central Processing Unit. This is the part of your computer (usually a chip) that actually does the work, so a good CPU, or processor, is vital for a fast and reliable computer. Pentium Processors are probably the best known CPUs.
OS: Operating System. The system used to allow you as the user to give the computer instructions. Windows and LINUX are two different operating systems. The majority of people use a version of Windows.
BIOS: Basic Input/Output System. This is the system your computer uses to boot up. This is what turns on the computer and loads up the operating system.
Files and programs
MS: Microsoft. Often used in conjunction with a program name, e.g. MSWord is the program Microsoft Word.
ie: Internet Explorer.
Apps: Applications. Usually an Application is a program.
.mdb: Microsoft Database. This is a file extension for Microsoft Access files. A file extension, part of the file name which is often hidden, tells the computer which program should be used to open the file.
.doc and .dot: Document and Document Template. These are file extensions for Microsoft Word documents and templates respectively. Similarly, .xls and .xlt are Microsoft Excel files and templates respectively. Templates are useful when you have a master file, e.g. a standard letter that you send out, where you change some parts each time you use it but you don't want the original file to be changed by accident. When you double click on a template file to open it, you instead create a new file based on that template, which is a little like copying the template into a new file.
Note: File extensions for Office 2007 are different to those for the previous versions; .mdb has become .accdb, while .doc, .dot, .xls and .xlt now all have the letter x added to the end.
xfer: Transfer. When we email you a xfer file, this is a small Access file that contains only the objects we have changed and need to transfer into your system.
A97, A2003 and A2007: Access 97, Access 2003 and Access 2007.
Spec: Specification. The document that lists the specific requirements of a new database or a large set of changes, which we work directly from once it has been agreed.
Tech Notes: Technical Notes. Along with User Notes these make up the standard documents we supply with a database. The Technical Notes describe the structure of the database to help anyone working behind the scenes.
On your keyboard
Tab: Tabulator. Used to move you onto the next item, whether that's a button on a form, a tab stop or a table cell. Originally a part of typewriters where its purpose was to allow the typist to easily line up columns for tables.
Alt and Alt Gr: Alternate and Alternate Graphic. These are used like Shift or Ctrl to make use of extra functions for each key on the keyboard. For example most keyboards have a key at the top left with three symbols on it. The first is typed by simply pressing the key (`), the second is typed using Shift + the key(¬) and the third is typed using Alt Gr + the key (¦). In any program such as Microsoft Word where you can set your own keyboard shortcuts, it is often useful to use Alt and Alt Gr as you are less likely to clash with the built-in keyboard shortcuts. Often, Alt Gr works the same as using Ctrl+Alt, and despite its full name rarely has anything to do with graphics.
Esc: Escape. This key is commonly used to stop a process, and can usually be used as a keyboard shortcut for any message box with a Cancel, Quit, Abort or Stop option.
Fn (on laptops): Function. Common on laptops that do not have a separate number pad or F keys (F1-F12). Other keys on the keyboard will be marked up as a number pad and F keys, usually in blue, in addition to their normal letters or symbols, and can be used in this manner by pressing the Function key.
Email and the internet
CC and BCC: Carbon Copy and Blind Carbon Copy. When you want to copy someone into an email but the email is not specifically to that person, you add their email address to the CC line. If you want to copy someone into an email without the other recipients of the email knowing (e.g. to keep their email address private), you should add them to the BCC line.
http and https: HyperText Transport Protocol and HyperText Transport Protocol Secure. The standard set of rules used by web servers and browsers to talk to each other is http, and https is a security-enhanced extension of this.
www: World Wide Web. The internet.
HTML: HyperText Markup Language. The standard language that webpages are written in.
ADSL: Asymmetrical Digital Subscriber Line. A standard form of broadband internet connection through a phone line.
GUI: Graphical User Interface. This is what you actually see on screen. Programs and files are stored as lines of text and symbols; the GUI interprets this to display it in a (hopefully!) helpful manner.
VB and VBA: Visual Basic and Visual Basic for Applications. Visual Basic is a programming language. VBA is a modified version of Visual Basic designed to run as part of certain programs. VBA is the programming language built into the Microsoft Office programs, and is therefore what we use to automate Access, Excel and Word.
ASCII: American Standard Code for Information Interchange. All standard numbers, letters and symbols have an ASCII code that can be used to identify them. This is useful in programming.
CD and DVD: Compact Disc and Digital Versatile Disc. Originally DVD stood for Digital Video Disc, the idea being that where CDs were often used for music, DVDs would be used for films. However the acronym was soon changed to represent the wide range of uses for DVDs.
You're Working Hard, But Is Your IT?
In the still difficult financial climate it is vital to use all the tools at your disposal to help your business to weather the storm. It is worth checking if your current computers and IT systems are working as hard as they could. Sometimes it seems that computer systems are running us, not the other way round!
Correctly used, well-designed software will save time and money, by relieving you of routine administrative tasks, leaving time to concentrate on the important things, like developing new products, or winning new business. Your customer service will improve as you will have all the necessary information readily available whenever customers call you, and a computerised system can also help prevent mistakes.
It is likely that the software already installed on your computers could be doing much more than it is - it is estimated that most people use only a fraction of the capabilities of Microsoft Office, for example. A one-off investment in technology can save ongoing staff costs and enable you to manage with your current workforce whilst growing your business.
Your IT should be saving you time, not taking up your time. If you answer no to any of the following questions then your computers and IT could be working harder for you.
- Correspondence - If you send the same or similar letters to different people again and again, is this process simple and straightforward? Can you just choose a contact and the letter you want to send, and have the letter produced at the click of a button?- Better still, does your computer tell you when it needs to be sent out too?
- Company Finances - Are your key financial indicators available to you as and when you need them, at the click of a button?
- Project Milestones - Are you automatically reminded when jobs are due? Does your IT understand the processes involved in your projects, and remind you when project milestones are due, activities need to be billed, new processes need to be started, or letters need to be followed up?
- Invoicing and other documents - Are your invoices simple to produce because your IT system already holds all the necessary information from earlier in your process, such as job cards and delivery notes? Does it produce any statutory documentation you need, e.g. waste notes?
- Contact Information - Does your IT system manage your contact information, not just hold it? Does it remind you when it is time to ring someone again, or follow-up the letter you sent them two weeks ago? Is this information integrated with your day-to-day operation, rather than being held in two systems?
If you feel your systems are not achieving all this, then there is almost certainly scope for improvement. You may be able to tackle the job yourself - using Microsoft Office tools like Excel and Access, and the internet provides a vast range of information and help.
In larger companies your in-house IT department will understand the particular requirements of your business and may be able to provide what you need. An external software developer is a good answer for more complicated projects when there is no in-house expertise or capacity. A good software developer will take time to understand what you need and will give you a clear idea of the costs involved.
No doubt you and your staff are working extra hard at the moment. Make sure that your computers are playing their part too.
Meet Your Guardian Angel!
Do you run a small business?
Would you like to save money on your accountancy fees?
Would you like to avoid accountancy fees altogether?
Accounts Guardian is a simple yet powerful low-cost accounting software package suitable for any UK business. Accounts Guardian has all the standard accounting features you would expect from a standard bookkeeping software package. It was designed with an accountant to save people time and money by keeping their books in order whilst also keeping things simple. This package has functions that most people genuinely need and it is very easy to learn and use.
The system allows you to see your financial position at any time in the year, and you may be surprised at how much money you can save on accountancy fees when you have all the facts and figures readily available.
Want a similar package that is customised to your business? Check out Accounts Guardian, work out what additional features you want, then just get in touch on 01747 822616 to discuss your needs.
We have been helping all sorts of businesses to run more efficiently for fifteen years, and know that using simple accounts software like Accounts Guardian is a key to success, enabling you to focus on running your business, not doing the accounts.
Download your free, unlimited day trial version of Accounts Guardian here.
Did you know...
…many of the advertised
features of Windows 7 are
available in Vista and XP if you
know where to look - ask
where to find them!
We want to save you time on your administrative tasks, so you can get on with your real work!
Call us on 01747 822616 before 1st June 2010 to discuss transferring your exisiting Excel spreadsheets into an Access database and we'll give you a whopping 10% off the cost of the project!. Just remember to quote Smatterings7 to be eligible.
If you don't know whether you might need to move from Excel to Access, take a look at this article at the beginning of this issue of Smatterings.
(Reasonable terms and conditions apply. Just ask us for details.)
If you know someone else who might benefit from this offer or anything else in Smatterings, please feel free to pass on the information to them. Just give us the necessary details here and we'll send them a link for you.
PS: Don't forget about our referral scheme. Refer someone new to us and choose a crate of beer, bottle of wine or champagne, box of chocolates or £20 cash as a thank you - and they'll get a 10% discount off their first project too!