MICROSOFT ACCESS QUERIES AND HOW IMPORTANT THEY ARE IN YOUR DATABASE

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. 
Microsoft Access Database
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 complex search.

Microsoft Access Queries and How Important They Are in Your Database

The main types of queries in Microsoft Access are select, action, parameter and aggregate.  How Many Types Do You Know And Use In Your Access Database?
Select Query
The select query is the simplest among them. It is used to retrieve data from tables. The data that will be displayed is dependent on the criteria of the user.
Action Query
Not only can a query be used in retrieving data, it is also used in deleting or updating records as well as creating tables and inserting data into tables. The type of query that does these is called the action query.
There are four kinds of action queries and they are as follows:
  • Append Query – gets the results of a query and add them to an existing table.
  • Delete Query – deletes the records in a table according to the set results of a query.
  • Make Table Query – creates a table based on the query set results.
  • Update Query – updates one or more fields in a table.
Parameter Query
A parameter query is a modified select query. It is either a value or a condition. This type of query functions along with other types of queries to get specific results that a user may want to retrieve. It basically refines the search by instructing the other query exactly what you want it to do.  An example of a parameter query is a specific date (3/30/2015) or a date in between start (3/1/2015) and end date (3/30/2015).
Aggregate Query
Like parameter query, aggregate query also works with other types of query. It chiefly generates an aggregated result from the selected groups. Some examples are Sum, Avg, Min, Max, Group By, Count, Where, First, and Last.
Obtaining data from a table consisting of a large amount of records without having to probe the table is the main importance of queries.
I have a couple eBooks about MS Access queries which may be of interest – why not check these and other Microsoft access tutorial eBooks out!

Comments

Post a Comment

Popular posts from this blog

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
Read more

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
Read more

Microsoft Access Tutorial: Creating A Many-To-Many Relationship

Image
Microsoft access tutorial In a relational database, a many-to-many relationship exists when a record in one table can be referenced by one or more records in another table and vice versa. You need to have two or more tables to create relationships. To link these tables, you will have to create another table (a third) which will be your interceding or junction table. Here are the steps again in creating a third table: 1.  Go to the Create tab. 2.  Click on Table Design. A new table will be opened. 3.  Create two fields (plus any additional supporting fields). NOTE:  The two key fields are going to be the primary keys from the two tables that you have created which are in effect known as secondary or foreign keys (duplicates OK). To identify the primary key from a table: Click on the existing table and select View on the upper left corner of MS Access. You can identify the primary key when you click on one of the fields and see the Primary Key button, right next to the
Read more