Introduction to Queries

In MS Access, what is a query?

This series of articles on MS Access Queries will start off from raw basics. Most of my regulars will already know this material, and so please allow for the fact that this is meant for “newbies” to some degree. I promise to get into more challenging topics as we go along.

Simply put, a query is a question asked of the database, specifically of one or more of the tables in the database. A query’s answer returns values from selected fields (columns) and records (rows).

A basic example could be, “Which of our customers are located in Springfield?”

A more complex query (or question) could be, “Which of our customers in Springfield have not ordered any products in the past 6 months?”

The above examples are of just one type of query; the select query. MS Access has a number of types of queries available.

This series of articles will be written based on Access 2000. Newer versions of Access may have added to this list. The principles explained here should work fine on version 2000 and any newer ones.

Types of Queries

If you create a query manually (more on this later) you choose from a list of 6 query types.

  • Select Query retrieves data from one or more tables (or queries!)
  • Crosstab query displays summarized data in a column/row format (like pivot tables in Excel)
  • Make Table query creates a new table based on data from one or more existing tables (or queries!)
  • Update query updates the data in a table (a simple example would be “Add an across the board raise of 2% to each employee’s pay rate”)
  • Append query adds new records (rows) to a table
  • Delete query will delete records (rows) from a table

Most of the above queries accept criteria (parameters) such that only some of the records in a table are retrieved or updated.

NEXT: How to create a basic query.

2010 New Year’s Resolution

I know what you are saying. “Ya, right!”

However, I am serious. My main resolution is to post more often on databaselessons.com.

I will start with a serious of postings about queries.

If you have been at the MS Access game for a while, you may find most of these query postings too simple. Well, this site is for people that are just shedding the pure wizard approach to MS Access, and so you may be right.

As always, please leave comments if you have a good one.

Thanks for reading.

Richard “Manxman” Killey

Open Source Alternative to MS Access

The open source alternatives to MS Access that I have looked at over the years did not match my favourite database’s full set of features.

A new offering is Kexi, which is a Free/Libre and Open-Source integrated data management application. Kexi is a long awaited Open Source competitor for products like Microsoft Access. Kexi can be used for creating database schemas, inserting data, performing queries, and processing data. Forms can be created to provide a custom interface to your data. All database objects – tables, queries and forms – are stored in the relational database, making it easy to share data and design.

Unfortunately, although Python and Ruby programming languages are supported (beta stage) and JavaScript support is in development, scripting is not yet available on the Windows version.

I do not think I could live without VBA. It makes Access what it is for me. Here is a link to the scripting info for Kexi.

For now, I have to sit in the wings, until the scripting is ported to the Windows version of Kexi.

Access, FTP, PHP, MySQL – Part 3

Continuing with my series about using Access on a PC to feed a MySQL database on the web! Read part 3 here. This part describes the structure of the 2 databases involved. One is an MS Access database on a local PC. The other is a MySQL database on a webserver.

Remember that this method was created for websites that exist on servers that do NOT allow remote MySQL connections. Some servers do allow the remote connections, which simplifies the whole process.

Access, FTP, PHP, MySQL – Part 2

I have finally resumed my series about using Access on a PC to feed a MySQL database on the web! Read part 2 here. This part describes the purpose of the database and describes 2 of the websites that get fed by this little homegrown system.

Remember that this method was created for websites that exist on servers that do NOT allow remote MySQL connections. Some servers do allow the remote connections, which simplifies the whole process.