MS Access Queries Versus Tables
An Access database is a collection of various objects which include tables and queries. It’s important to know the distinction between MS Access queries and their close cousin; the table and below I’m going to give you a summary to get you started.
MS Access Tables
To hold and archive real data (or physical data) you use a table in your database. You can of course have more than one table which can be joined in a relationship to make your database application a relational system (using RDBMS).
Each field created in your table is assigned to an exclusive data type and includes Number, Text, Date/Time and Yes/No which is the first steps to validating the data input and saving on memory allocation.
MS database tables are normally the ‘back-end’ of the database where your data sits waiting to be utilised i.e. in a query!
MS Access Queries
To ask a question and get an answer, you need to query the database. Like a table, queries have a similar layout but that’s where it stops!
The language in which MS Access queries are automatically generated and also stored is known as SQL which stands for Structure Query Language and it’s this language (or small text file) that is actually saved and not the physical data.
The advantage with queries over tables is simply making data outputs more flexible and dynamic since the query connects and talks to a table (or several tables as joined) returning a collection of records known as recordsets.
Here a summary of the differences between queries and tables…
- Data is not physically stored in a query whereas a table is the physical data archive.
- Data can be organised and filtered dynamically in a query without the need to re-apply the criteria and sorting.
- You can calculate in a query by writing expressions and therefore create formulae and use functions to generate new fields. Though with the introduction of version 2010, you can now calculate in a table by assigning the ‘Calculate’ data type.
- In a query, you can bring fields from more than one table together for a query linking them into a relationship and therefore create more flexible reports.
- In a query, you can change the level of information from a detail level (line by line) to the summary level as group calculations.
In conclusion, certain queries can physically change the data in a table by calling one of the ‘Action’ queries which there are four different types to choose.
Want to know more about MS Access queries and other aspects of the database application? Take a look at some of my eBook offers which covers all the main objects and more.