#building access databases

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 sign) is the exact convention and good check to ensure it’s the right data type too (date/time in this case).\

A revised example is shown below:

If the field is a actual date/time data type, you do not need to enter the # (hash sign) as it will automatically fill this in for you when moving the cursor elsewhere in the restrain (another convention checker for you).

If however, you see double-quotes (“ “) wrapped around the date expression, this suggests either you have chosen the wrong field or the date field is actually a text data type and it will simply not work as expected.

One final point to consider and even check for you ms access query database and those

queries are the regional setting of the operating system you are using. UK versus USA setting often throws up confusion and inaccuracies to so ensure you are using the correct date/time formats.

MS Access query is very powerful indeed and there are many permutations available – take

a look at my eBook Microsoft Access 2016 Database Queries.

Do you need for eBook relating to Access Database?

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 Button

Let’s work on and illustrate the alternative button option using a Label and some VBA code. Take a look at the following screenshot:

When you hover over an item in the dark blue banner, the item turns a subtle white from the resting light blue resetting all other menu items including the ‘Close‘ item.

Of course, you can set any format by adding a raise, sunken effect or enhancing with underline, bold, change the background or changing font sizes – it’s how creative do you want to be?

I used a Label control and set the default to a non-selected state. In this example, I used the following:

Set the Caption property as you may want to change the caption from say ‘On‘ to ‘Off‘.

Back Style property to ‘Normal‘

Back Color property to ‘#003366‘ which reflects the dark blue banner colour though could be set as transparent.

Border Style property set to ‘Transparent‘.

Border Width property set to ‘Hairline‘.

Border Color property set to ‘No Color‘.

Special Effect property set to ‘Flat‘.

Fore Color property set to ‘#7DBEDC‘ (which is light blue colour).

You may want to look at my eBook on How to Build Access Database Forms for more detailed explanation on the above properties and general design techniques for your forms.

I would suggest you also apply some consistency to the names for your Labels that will be acting as a button as you could be clever to group and loop your Access VBA code more effectively. You could also adopt the Tag property to help separate this type of control use with others.

What about the VBA code?

Well, you will probably want a public procedure to reset all controls and individually apply format changes to the selected item.

Therefore, using the main event called ‘On Mouse Move‘ would be advisable. You will still need to add code the ‘On Click‘ event so when users click the item it will execute the action of opening a form or report or whatever it is your want to action (which has been excluded in this article).

I created a public sub procedure called ResetMenuLabels that runs the following code:

(Please ignore the word ‘Private’ in the above code snippet. It should be ‘Public’ as it should really be stored in a module (which this example doesn’t use) making this available to any form).

The above Microsoft Access tutorial code will loop through all controls for your form but only check to first the active control is a Label type and that is had the Tag set as ‘lb‘ (standing for Label Button). Providing you don’t use this convention for any other control, you are pretty safe for this form to reset all label buttons back to the resting style.

I’ve only used a simple format here for illustrative purposes and of course would expect you take it to the next step and apply your chosen styles – simple fill in between the loop structure.

The added element now is to call this procedure for the individual control’s ‘On Mouse Move‘ event with the additions for highlighting this control standing out from all others.

Here’s an example for the ‘Close‘ button label:

So now this Microsoft Access tutorial on how to use alternative buttons opens the mind a little further.

The downside unfortunately is this will add to the time consuming task for your Access form designs!

This article talks about the importance of the  Append Query and the appropriate way of creating them in Microsoft access database.

In Microsoft access database and all the other database management systems, queries as the heart of the software system which can execute numerous actions to make your database more systematized and functional. Queries prove to be useful for working on various tasks including returning record-sets in an ordered and filtered way, updating values, editing or deleting data, and even making a new table in the access database.

Action Queries can be used for adding, changing, or deleting numerous records from a table, record or field at a click of a button.

The additional advantage of an Action Query is that user can preview their query results in Microsoft access 2016 before running it.

Microsoft access database provides the following types of Action Queries:

Append

Update

Delete

Make-Table

Note that users cannot undo an action query and therefore, they must create a backup of the data that they want to update using the query.

Steps for creating an Append Query

Append query is a kind of (SQL statement) action query which lets users add records to a table. It is also called an Insert Query since it uses ‘INSERT INTO’ command as its SQL syntax. Users can use Append Query to add new tables or data to another table. It can likewise be used for fetching data from multiple tables.

To make this process extra clear, let’s use two of our existing tables — StaffID and StaffSalary.

Step 1: Open the database that has the records that you want to copy.

Step 2: Click the Create tab and select Query Design to open the query designer.

Step 3: At this point, you should be able to see the Show Table dialog box. Double-click the tables or queries that hold the records that you want to copy. For our example, we are going to use StaffID and StaffSalary. Click Close.

Step 4: Double-click each field that you want to append. Each of the fields you select will be shown in the Field row of the query design grid.

Step 5: Click Run on the Design tab to see the query result.

Step 6: Verify that the query returned the records that you want to copy. If you see the need to add or remove some fields, return to Design view and add or delete the fields.

Step 7: Convert the select query to an append query. To do this, select Design View from the View drop-down menu on the Home tab.

Step 8: Click the Append button and a dialog box will show.

Step 9: Next, you need to specify whether to append records to a table in the current database or to a table in another database. If the table is in the current database, click Current Database in the dialog box and choose the table from the drop-down list. If the table is in another database, click Another Database then click Browse to select the destination database. Once you’ve chosen the database, select or enter the name of the destination table in the Table Name box. For our example, we will use the Staff table. After selecting, click the OK button.

Step 10: You then choose the destination fields. The way you select destination fields depends on how you created your SELECT query. If you added all the fields from your source table, Access will add all the fields in the destination table to the Append to row in the design grid. If you added every field to the query or used expressions, and the field names in the source and destination tables match, Access will automatically add the matching destination fields to the Append to row in the query. If you added individual fields or used expressions, and any of the names in the source and destination tables do not match, Access will add the matching fields and leave unmatched fields blank. In this case, you can click a cell in the Append row and choose a destination field.

Step 11: Preview the query before running. Do this by switching to Datasheet View.

Step 12: After that, return to Design View and click Run to append the records.

You will then be able to see all the additional data which have been appended to the destination table.

microsoft access database .The Task Tool utility was built to demonstrate the ease of use and how MS Access VBA code can communicate with other applications (and not necessarily just with Microsoft applications).

My utility comes with instruction notes and the code is left open for you to view and edit to enhance and adapt to your own systems – Click here for the sales offer page.

You can also use the form’s design as a template for your other forms and extend the colour code (VBA) to create a more polished application.

You do not need to know any VBA code – just download and plug in to operate. If however, you have some Access code knowledge, then expand on the simple procedures and make them more globally accessible to other parts of the database system.

Don’t forget to check out my other utilities which all ship with open source code and a money back guarantee plus 30 day free email support. My other Microsoft Access database offers.  microsoft access database

Within the framework of using microsoft access database , the HAVING clause can only be used with the GROUP BY type SQL statement which is also referred as the Groups and Totals query. The HAVING keyword when not used with the GROUP BY statement acts as a standard WHEREclause.

The HAVING clause specifies a condition that is similar to the purpose of a WHERE clause when  applying criteria but the two clauses are not interchangeable.

The key difference between these two keywords is down to one keyword; Aggregation!

Ms Access Queries: Difference Between The Where Clause And the Having Clause

The WHERE clause specifies the criteria which individual records must meet to be selected by the query. It can be used with or without theGROUP BY clause. However, the HAVING clause cannot be used without the GROUP BY clause.

Also, the WHERE clause selects rows before it applies any grouping where as the HAVING keyword selects rows after grouping takes place.

The WHERE clause cannot contain any aggregate functions but the HAVING keyword can contain aggregate functions like Sum, Count, Avg etc.

The following SQL statement will group all customers in the ‘UK’ using the GROUP BY keyword even which will detail individual records due to the unique ID included:

SQL Statement…

SELECT Customers.[Customer ID], Customers.[Company Name], Customers.City, Customers.Country FROM Customers GROUP BY Customers.[Customer ID], Customers.[Company Name], Customers.City, Customers.Country HAVING   ((Customers.Country)="uk"));

The same effect excluding the GROUP BY clause using the WHERE clause:

SQL Statement…

SELECT Customers.[Customer ID], Customers.[Company Name], Customers.City, Customers.Country FROM Customers WHERE (((Customers.Country)="uk"));

When using one of the aggregate functions, you can pass criteria too using the HAVING clause only. See example below which only returns a count of customers that exceed 5 hits for each country:

SELECT Count(Customers.[Customer ID]) AS [CountOfCustomer ID], Customers.Country FROM Customers   GROUP BY Customers.Country HAVING (((Count(Customers.[Customer ID]))>5));

ACCESS DATABASE TUTORIAL IS 4 YEARS OLD

ms access database tutorial .Spending the time to create a website offering various tools, resources and free tips and tricks was really down to my students which I’m very grateful to them for inspiring me to create a successful online service and it’s been an amazing journey so far but where has the time flown? 4 Years on!

With all the newer applications and online web processes of today, Micro…

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 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 Access database can support other Office applications.