Using Database Functions in Microsoft Excel - Syntax for database functions and rules for criteria tables

(Page 3 of 4 )

As with any other Excel functions, there is syntax that needs to be properly understood and followed in order for Excel to process those data correctly. We all know that if we are not able to use correct syntax, it can lead to errors or inaccurate results in the spreadsheet.

The database function syntax is this:

[database function](database,field,criteria)

It is impossible to understand without illustrating the syntax using an actual Excel table.

For the sake of illustration, please refer to the two tables below. The first one is called "criteria table" and the other table (below the criteria table) is the actual database to be analyzed:

Here are some rules that cover "Criteria Tables."

1. Criteria tables should always be above the database.
   
2. Criteria should be treated as an Excel table, NOT a database.
   
3. If you need to query the database, you should use the criteria.
   
4. Criteria tables are REQUIRED to query the database.
   
5. Criteria tables should be adjusted each time you need to make a unique query to the database. (see examples later)
   
6. The column names used in the criteria table should be exactly the same as the database field names (including casing and format).
   
7. The number of columns in criteria tables may not be equal to the number of columns used by the database. 
   
8. The first row in any criteria table should be the field names.

Next: Database function examples >>
 

More BrainDump Articles
More By Codex-M

Comments On: Using Database Functions in Microsoft Excel

· very simple presentation but very effective tool   >>> Post your comment now!