Designing A Multi-Table Query – Part 3

To view Part-1 of this series, (by Ben Beitler ), Click here >>

Creating A Multi-Table Query

Since we have planned our question, we are prepared to design and run it. If you have made written plans for your query, make certain to reference them frequently all through the ms access query design process.

These are the steps in creating a multi-table query:

1. Choose the Query Design from the Create tab on the Ribbon.image12. In the Show Table dialog box that shows, choose each table you want to include in your query, then click Add. After adding the tables, click Close. In our example, we needed information from the Customers and Orders table, so we’ll add them. Continue reading “Designing A Multi-Table Query – Part 3”

Designing A Multi-Table Query – Part 2

To view Part-1 of this series, (by Ben Beitler ), Click here >>

accessdatabasetutorial-2Queries can be hard to comprehend and fabricate if you don’t have a smart thought of what you’re trying to search and how to find it. A one-table MS access query can be simple enough to make up as you go along. However, to construct anything more powerful, you will need to plan the query initially.

Planning a MS access query

When planning a query that uses multiple tables, follow these steps:

  1. Define precisely what you wish to know. If you could ask your database any question, what would it be? Building a MS access query is more complex than just asking a question, but knowing exactly what question you wish to answer is vital to building a suitable query.
  2. Identify each type of information you want to include in your query output. Which fields contain this data?
  3. Locate the fields you want included in your MS access query. Which tables are they contained in?
  4. Determine the criteria the information in every field needs to meet. Consider the question you asked in the initial step. Which fields do you have to look for particular data? What information are you searching for? How will you search for it?

Continue reading “Designing A Multi-Table Query – Part 2”

Microsoft Access Database Functions

Microsoft Access Database Functions

This is something I have touched on briefly in the past with an article called Microsoft Access Functions which introduced you to the popular functions and the tool to generally browse for other not so popular function calls.

So as a revisit to this topic, there’s no point describing each and everyone that’s at your disposal – that would be silly as we all have different uses for our Access databases but instead you can find a full list (by category) by going to Microsoft’s Office Access Functions (by category) page instead.

microsoft access database functions office

Microsoft Access Database Functions

It clearly states that this page contains links to articles that provide details about common functions used in expressions in Microsoft Access.

They are arranged first by Continue reading “Microsoft Access Database Functions”

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”

Microsoft Access Tutorial: How To Create A Leading Dot List

Microsoft Access Tutorial: How To Create A Leading Dot List

If you wanted to generate a table of contents or a set of index pages, the format for this type of document tends to have leading dots connecting narratives with page numbers.

Using Microsoft Word or other word processors and DTP applications; generating these pages is a breeze. With Microsoft Access however, it simply doesn’t exist and to mimic such a document we need to be a little creative here. 

In this Microsoft Access tutorial, I’m going to create a report showing the company name, a contact name and the telephone number sitting (as right aligned) at the end of a dot leader line (see image below).

microsoft access tutorial string function

In fact, the telephone number may appear as right aligned but is in fact Continue reading “Microsoft Access Tutorial: How To Create A Leading Dot List”

MS Access Functions: How To Build A Soundex Or Sounds Like Custom Access VBA Function

MS Access Functions: How To Build A Soundex Or Sounds Like Custom Access VBA Function

Working with MS Access functions (of the built-in variety), you may have noticed a vast list of functions across different categories. By combining (or nesting) functions, you can certainly produce complex output values which will serve you well with your queries, forms and reports.

However, there is one lacking feature (or function) in Microsoft Access that could hinder searching for values and that is a function that can find similar or ‘sounding like’ a string match of characters. This function is widely known as ‘Soundex’ and some database applications do provide their own algorithm to support this technique (including the Soundex SQL Server function).

ms access functions soundex

The algorithm is a phonetic list of character strings based on the pronounced English language and of course this Continue reading “MS Access Functions: How To Build A Soundex Or Sounds Like Custom Access VBA Function”

How To Convert Numbers To Words: Access VBA Function

How To Convert Numbers To Words: Access VBA Function

Once again, I’ve been asked by a client to create procedure to turn a monetary number (numerical value) into its text equivalent and so I wanted to share with you how to convert numbers to words using Visual Basic code that can also be applied not just to your Access database but with any application that entertains VBA code! how to convert numbers to words

How To Convert Numbers To Words: The Steps

This article is intended for users who know and have some VBA code knowledge but need a little push to Continue reading “How To Convert Numbers To Words: Access VBA Function”

How To Concatenate Access Fields In Queries

How To Concatenate Access Fields In Queries – Simple Really!

To join or not to join, that’s concatenation for you! How do you join or concatenate Access fields together for your queries or other objects (including forms and reports) is a matter of mastering the rules and knowing the operators.

There are plenty of simple demonstrations and video tutorials around on the web showing you how to combine and joins fields from tables in a calculation using the concatenate principle.

Most Access developers and users will use the & (ampersand) operator which will join the fields together – like the glue in between two fields. For example:

Fullname: [First name] & " " & [Surname]

However, have you seen this example?

Fullname: [First name] + " " + [Surname]

The above will provide the same results – or does it?

How To Concatenate Access Fields In Queries – Knowing The Difference

So the difference between using the & (ampersand) and the + (plus sign) will sometimes show Continue reading “How To Concatenate Access Fields In Queries”

Access DLookup Function: Why You May Need To Apply This Function

Using the Access DLookup function may be called upon where users need to output a value from other data sources (i.e. tables or queries) where there is no direct relationship.

Hang on a moment! I though Microsoft Access was a relational database system (RDBMS) so why calculate to connect to a value?

Yes it is an RDBMS but depending on how the database was designed and built or if you had inherited an Access database this could well mean having to handle workarounds and the collection of ‘D’ based Access functions are at your disposal.

access dlookup

The Access DLookup function amongst Continue reading “Access DLookup Function: Why You May Need To Apply This Function”