Microsoft Access Database: Pass Through Queries

Microsoft Access Database:  Pass Through Queries

There are many Microsoft Access queries to choose from that will manage your database reporting requirements but there are a handful of lesser known types of queries that you may not be aware of.

One in particular is a query called ‘Pass Through Query‘ as the following short video tutorial quickly explains…

https://youtu.be/DBchzNZXru4

Putting aside for the heavy American accent, it clearly explains the use for this type of query and the power behind how Continue reading “Microsoft Access Database: Pass Through Queries”

Microsoft Access Queries and How Important They Are in Your Database

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 Queries eBook previiew

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 Continue reading “Microsoft Access Queries and How Important They Are in Your Database”

Microsoft Access Database Versus Excel – Which To Choose?

Microsoft Access Database Versus Excel – Which To Choose?

Why Access? Why Excel?

In the modern age, ‘the books’ are done digitally. Individuals and businesses alike use software like Microsoft Excel to do a bunch of database stuff… – but strangely, bizarrely, bafflingly, Microsoft Access database remains a highly underused product in the Microsoft Office package.  There seems to be a general misconception about Access and Excel, with many people believing Excel to be both easier and more applicable to their needs.

microsoft access database versus excel image

Databases vs Spreadsheets

So what’s the difference between the two? Microsoft Access is a database, not just a large spreadsheet like Excel. Both have their uses and only the situation can dictate the need for one over the other. Excel is generally more convenient for crunching numbers and producing figures and graph that represent technical data. Access, however, is able to store a huge amount of data – far more than in any one spreadsheet. Additionally, Excel Continue reading “Microsoft Access Database Versus Excel – Which To Choose?”

Microsoft Access Queries: Some Pointers To Building Those Reports

Microsoft Access Queries: Some Pointers To Building Those Reports

Database developers have admired Microsoft Access because it’s scalable and user friendly (for a development application that is) and provides a good ‘one-stop‘ shop balance for database management meeting small and medium sized company needs.

I have always harked on about the key to a good database is in the query and just in case you are not yet familiar with Microsoft Access queries, here is just a little bit of information.

What are Access Queries?
Well the simple answer is…

"Access, I have a question for you!"

…It then goes off and finds the answer.

In other words, a query is simply an instruction or request for a report or some other form of output of information.

The language it uses to communicate and ask that question of your data is known as SQL (Structured Query Language) and it uses this SQL query syntax to provide answers very quickly indeed.

microsoft access queries some pointers

The advantage with Microsoft Access database queries however, is that there is no requirement to learn the language at all as it Continue reading “Microsoft Access Queries: Some Pointers To Building Those Reports”

Microsoft Access Database Tutorial – Selecting Unique And Distinct Values In A Query

Microsoft Access Database Tutorial – Selecting Unique And Distinct Values In A Query

There are many different types and ways to run queries that your Microsoft Access database application provides and one useful ms access query is where you can create a distinct or unique value list from duplicate or repeating values from other records.

Check out this 4 minute video tutorial which clearly explains two ways to create a unique value query report.

httpvh://www.youtube.com/watch?v=CymoHytiIWM

As the video shows, you can use either the property sheet to select unique value or the SQL statement approach using the keyword Distinct. In fact, an extension to Continue reading “Microsoft Access Database Tutorial – Selecting Unique And Distinct Values In A Query”

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

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

Within the framework of using Microsoft Access queries, 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 WHERE clause.

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.

microsoft access queries difference between the where clause and the having clause

The key difference between these two keywords is Continue reading “Microsoft Access Queries: Difference Between The Where Clause And the Having Clause”

Microsoft Access Update Queries: An Action Query

Microsoft Access Queries: An Action Query

I witnessed a cardinal sin of one person manually editing the same value across multiple records instead of using one of Microsoft Access queries well known action query; the update query.

Take a quick look at this preview video which is seven minutes long and covers the essentials (and ignore the title with reference to programming in 2013 – not applicable here).

httpvh://youtu.be/NT1xvodbUQE

In this video, the author mentions the exact match (in double quotations marks) when setting the criteria as well as Continue reading “Microsoft Access Update Queries: An Action Query”

Concatenating Multiple Rows Into Single Line In MS Access

Microsoft Access Database: Concatenating Multiple Rows Into Single Line In MS Access

Now I’m not going to take credit for this next piece of Microsoft Access database VBA code as it was originally created by a well-known Allen Browne – many thanks!

Instead, I’m going to borrow his code for you (my regular readers) just in case you haven’t seen this before and add an element of quickly adding criteria to a query as a workaround to avoid expanding the VBA code itself.

concatenating multiple rows into single line in ms access

There are potential problems and errors this code will throw at you if you try to add criteria in the normal manner or embed any SQL directly into the calling custom function which means using some common sense and logic to deal with the challenge here.

Microsoft Access Database: Concatenating Multiple Rows Into Single Line In MS Access

First of all, let’s get the function to work and the query understood. In a new module, you will need Continue reading “Concatenating Multiple Rows Into Single Line In MS Access”

How To Create A Running Totals Query In Microsoft Access

How To Create A Running Totals Query In Microsoft Access

If you wanted to calculate a running sum (or a cumulative total) for a report; this is simply set by using the correct ‘Running Sum‘ property for a selected control like a numeric bound field (text box) and all you need to do is decide whether this will be over a group or the whole report.

However, this doesn’t exist in a query and to mimic the report feature to recreate running totals query in Microsoft Access, you will need to be a little creative!

running totals query in microsoft accessEven though MS Access queries you do have a ‘group by’ and ‘where clause’ features where you can choose one of the aggregate functions, there is Continue reading “How To Create A Running Totals Query In Microsoft Access”

SQL Commands: MS Access SQL Versus SQL Server – The Differences

SQL Commands: MS Access SQL Versus SQL Server – The Differences

It’s a common mistake when MS Access developers assume their know SQL commands being common to all platforms. Yes, there is a common standard syntax but at the same time, there are variances between software systems.

It’s like choosing a flavour of ice cream; different tastes but it’s still ice cream and if you mix them, again different tastes but may not always be compatible to the palette!

I’ve seem many questions come up on forums about the differences and conversions between MS Access SQL and T-SQL (SQL Sever) which there are of course some differences between the two language syntax’s.

sql commands ms access sql server

So, here’s a few example SQL commands to help you out with or when needing Continue reading “SQL Commands: MS Access SQL Versus SQL Server – The Differences”