Posts

DATE CRITERIA MAY NOT ALWAYS WORK WITH MS ACCESS QUERY

Image
Learning about ms access query is the means to a good database management system as it is the heart of any database application. There are many ways and questions to ask a database using queries and mastering the special conventions and criteria will pay the dividend and avoid silly mistakes, the illogical recordset results, and even errors. One of the more commonly used criteria and prone to errors (if misunderstood) is the date/time data type and its conventions. Take a look at the ms access query below-showing orders before the year 2016 and the intend of the query which suggested a date range from 1st January 2016 to 31st December 2016. The criterion for the above shows >=#01/01/2016 < #31/12/2016# which will not actually return the correct range and instead show what’s known as logical errors (dates outside the range). It’s missing the ‘And’ operator and better still adding the ‘Between’ hand will capture the correct range too. The # (hash

MICROSOFT ACCESS DATABASE 2007 TUTORIAL

When Microsoft Access first appeared in the early 1990’s it revolutionized the desktop database market. Most versions have been similar to each other. Now with Access 2007 , we see the most deep-seated changes yet introduced by Microsoft. When working your way through an MS Access 2007 tutorial, a hardcore traditional user of MS  Access is either going to love or hate these new changes. So what is different about this new version? The interface is completely different and instead of menus and toolbars, there is the Ribbon. The Ribbon is separated into sections and each section holds the relevant commands for designing and running a database. Creating forms has become easier and there are some latest forms to choose from. One example is the split form. The database window has also been replaced with the routing pane. This offers more features and is not as straightforward to use as the old database window. As a fraction of the MS Access 2007 tutorial, you should expect to

MICROSOFT ACCESS TUTORIAL: HOW TO BUILD ALTERNATIVE ACCESS FORM CONTROLS – THE COMMAND BUTTON

Image
Building Access forms is a time consuming aspect to front-end database designs and in this Microsoft Access tutorial , I want to add some extra aesthetics and alternative control enhancements for you. There are many Access form controls, formats and properties that can be applied to a form over and above the basics ranging from the more advanced conditional formatting options to the custom built controls including my recent post on coloured tab controls . So here’s some alternatives for your traditional command button… Using the standard command button, you can still apply different format effects using the correct event with some Access VBA code or adopt a different control altogether (i.e. a Label) and make this your button click action instead. Hyperlinks are also another way to apply actions for opening another form or running a report and can exclude VBA code (for basic actions). Microsoft Access Tutorial: How To Build Alternative Access Form Controls – The Command

How To Run Microsoft Access On A Mac PC Using CrossOver Software – Alternative Options

Image
Firstly, let’s clarify that Microsoft Office for Mac does not include the  microsoft access database  application which is why we have to take such steps. The alternatives and work around could include: 1.  Using the free runtime version for an exectuable file only or a third party web app which is now available for the iPad so you can load Access as a viewer only should you need to just read information. 2.  Installing VMWare (or similar) which is a virtual image software tool that creates a separate instance on your Mac PC and therefore can have several versions as a quick way to restore a default environment. However, it will require you having a licenced copy of Microsoft Windows operating system whereas CrossOver has this already included 3.  Using some sort of remote access software like LogMeIn or SplashTop (which there is a free version. There are others too but the two mentioned I have used are very effective indeed but will require a live internet connection to a

MICROSOFT ACCESS TRAINING: BUILDING ACCESS LABEL REPORTS

Image
Continuing with the  Microsoft Access training  series, building Access label reports is pretty straight forward (especially with the built-in wizard tool). As a snapshot and quick peek into my  microsoft access database  eBook, here’s some more information to help establish your options. This type of report can be used for printing label address information to fit a pre-defined or custom defined label sheet (normally an adhesive or plain paper sheet). It can be based on a Table, Query or another external data source altogether (i.e. Excel or Outlook). It can also be used for creating badges, product labels, category name cards and other simple types of data layout reports. Some users will use Microsoft Word’s Mail Merge feature instead of the basic Access Label report where data from an Microsoft access database can support other Office applications. If you are going to take advantage of the ‘one-stop’ solution, then keeping it all within the Microsoft access  database env

Microsoft Access Queries: An Action Query

Image
You use this type of query to change data in tables, saving the need to manually edit records or use any of the manual tools namely,  Find  and Replace . Examples for this action could be to reset data, change prices, increase volume and correct data migration and population issues. One of the more commonly used examples is to carry out some sort of calculation and change prices for a range of products. The above example increases all products for UK suppliers by 10% using the  [Unit Price]  field containing the original price value multiplied by 1.1 (an increase by 110%). Just remember, this is an action query which means changes to any data values cannot be reversed once actioned which is why you should normally see a warning first. However, it is always a good idea to take a copy or back up of your data files before running  Microsoft Access VBA programming  of this nature. There are many other types of queries too and the more you know about Access queries, the more f

MICROSOFT ACCESS QUERIES AND HOW IMPORTANT THEY ARE IN YOUR DATABASE

Image
The heart of any MS Access database system resides with the queries and I’m often asked to give new users a quick overview of this powerful application –  here’s an example… A Microsoft Access Database is a desktop application which stores large amounts of data and is able to recognize relationships between those data. It consists of data and tools that enable users to manipulate that data.  One of these tools are the queries.  Microsoft Access Queries  are used specifically to organize data on the Access database. A database can hold multiple tables. Each of the tables can have hundreds of thousands of records. How the user extracts a specific section of the table or tables is by using the queries. Displaying data in simple or complex ways is the typical function of a query. One simple way is by getting the names of all the customers in a table. But you can modify your query to include only a list of customers residing in London sorted by last name, and this is a more com