Differences Between MS Access Queries And Tables

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 queriesMS 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…

  1. Data is not physically stored in a query whereas a table is the physical data archive.
  2. Data can be organised and filtered dynamically in a query without the need to re-apply the criteria and sorting.
  3. 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.
  4. 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.
  5. 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.

This entry was posted in Database Theory, MS Access, Queries, Tables and tagged , , , . Bookmark the permalink.

4 Responses to Differences Between MS Access Queries And Tables

  1. ken mulwa says:

    commendable job ..for an important information provided to learners

  2. Evans gikundi says:

    Important information for students

  3. Zenee Miller says:

    Good Info! Thanks!

  4. Ben Beitler says:

    On added point to mention here is that with Access tables you can store up to 255 fields not that I would recommend this as it can affect performance. The file size can grow to 2GB should you need the space.

    With MS Access queries, the capacity of the number of fields is the same but they can be combined from up to 32 tables with 16 joins – a heavy duty query!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Confirm you are human.