Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

How Access Stores Queries - the MSysQueries table

Return to Home Page

Access uses the MSysQueries system table in conjunction with other system tables to display the query structure in the query design window.

The query design is optimised by Access so it runs in the most efficient way possible

The stored data for each query is automatically replaced each time a query is used to ensure any design changes are saved.

 

 

Colin Riddington      Mendip Data Systems        03/02/2019

Return to Top
* Required

To provide feedback on this article, please enter your name, e-mail address and company (optional) below, add your comment and press the submit button. Thanks

Return to Access Articles

Difficulty level :   Advanced

 

SELECT MSysObjects.Name, MSysQueries.Attribute, MSysQueries.Flag, MSysQueries.Expression, MSysQueries.Name1, MSysQueries.Name2

FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id = MSysQueries.ObjectId

WHERE (((MSysObjects.Flags)<>3))

ORDER BY MSysObjects.Name, MSysQueries.Attribute, MSysQueries.Flag;

1.   SELECT query – INNER JOIN & ORDER BY

The easiest way to interrogate this table is in conjunction with the MSysObjects system table (also READ ONLY)

Not all fields are required for this purpose:

Click any image to view a larger version

NOTE:

Flags value 3 is used for TEMP queries used with form and report record sources so has been OMITTED here

 

Running the query shows these values for the query itself:

MSysQuery1 MSysQuery2 MSysQuery3

The table below explains the meaning of each field for different query types.

 

The main fields are Attribute, Flag, Expression, Name1 and Name2

The Expression field is blank unless stated otherwise

Not all Attribute values will appear in each query

MSysQuery4A MSysQuery4B

The attached database includes several queries to illustrate how the MSysQueries table works

MSysQuery5 MSysQuery6

 

SELECT tblCurrencies.CurrencyCode, tblCurrencies.Currency, tblCurrencyExchange.Base, tblCurrencyExchange.Date, tblCurrencyExchange.Currency, tblCurrencyExchange.Rate

FROM tblCurrencies INNER JOIN tblCurrencyExchange ON tblCurrencies.CurrencyCode = tblCurrencyExchange.Currency

ORDER BY tblCurrencies.CurrencyCode, tblCurrencyExchange.Date DESC;

2.   SELECT query – INNER JOIN (Filtered)

 

SELECT tblCurrencies.CurrencyCode, tblCurrencies.Currency, tblCurrencyExchange.Base, tblCurrencyExchange.Date, tblCurrencyExchange.Currency, tblCurrencyExchange.Rate

FROM tblCurrencies INNER JOIN tblCurrencyExchange ON tblCurrencies.CurrencyCode = tblCurrencyExchange.Currency

WHERE (((tblCurrencyExchange.Base)="GBP") AND ((tblCurrencyExchange.Date)=#12/14/2018#))

ORDER BY tblCurrencies.CurrencyCode;

MSysQuery7 MSysQuery8

3.   SELECT query – Multiple INNER JOINS (Filtered)

 

SELECT tblCurrencies.CurrencyCode, tblCurrencies.Currency, tblCurrencyExchange.Base, tblCurrencyExchange.Date, tblCurrencyExchange.Currency, tblCurrencyExchange.Rate

FROM tblCurrencies INNER JOIN tblCurrencyExchange ON tblCurrencies.CurrencyCode = tblCurrencyExchange.Currency

WHERE (((tblCurrencyExchange.Base)="GBP") AND ((tblCurrencyExchange.Date)=#12/14/2018#))

ORDER BY tblCurrencies.CurrencyCode;

MSysQuery9 MSysQuery10

4.   SELECT query – Left Join

 

SELECT tblCurrencies.CurrencyCode, tblCurrencies.Currency, tblCurrencyExchange.Base, tblCurrencyExchange.Date, tblCurrencyExchange.Currency, tblCurrencyExchange.Rate

FROM tblCurrencies LEFT JOIN tblCurrencyExchange ON tblCurrencies.CurrencyCode = tblCurrencyExchange.Currency

WHERE (((tblCurrencyExchange.Base)="GBP"))

ORDER BY tblCurrencies.CurrencyCode, tblCurrencyExchange.Date DESC;

MSysQuery11 MSysQuery12

5.   Aggregate Query – GROUP BY

 

SELECT DISTINCT qryCars.Manufacturer, qryCars.Year, qryCars.Model, Count(qryCars.Colour) AS Colours

FROM qryCars

WHERE (((qryCars.Price)<15000))

GROUP BY qryCars.Manufacturer, qryCars.Year, qryCars.Model

HAVING (((qryCars.Year)=2011))

ORDER BY qryCars.Manufacturer, qryCars.Model;

MSysQuery14 MSysQuery13

6.   Append Query – INSERT

 

INSERT INTO tblCars ( Manufacturer, [Year] )

SELECT DISTINCT tblVehicles.Manufacturer, 2019 AS [Year]

FROM tblVehicles LEFT JOIN tblCars ON tblVehicles.Manufacturer = tblCars.Manufacturer

WHERE (((tblCars.Manufacturer) Is Null));

MSysQuery15 MSysQuery16

7.   UPDATE Query

 

UPDATE ((tblCars INNER JOIN tblCarsModel ON tblCars.ID = tblCarsModel.ID) INNER JOIN tblCarsColour ON tblCarsModel.ID = tblCarsColour.ID) INNER JOIN tblCarsPrice ON tblCarsColour.ID = tblCarsPrice.ID

SET tblCarsPrice.Price = [Price]*1.05

WHERE (((tblCars.Manufacturer)="Audi") AND ((tblCars.Year)=2011) AND ((tblCarsModel.Model)="GS"));

MSysQuery17 MSysQuery18

8.   DELETE Query

 

DELETE tblCurrencies.*, tblCurrencyExchange.Currency

FROM tblCurrencies LEFT JOIN tblCurrencyExchange ON tblCurrencies.CurrencyCode = tblCurrencyExchange.Currency

WHERE (((tblCurrencyExchange.Currency) Is Null));

MSysQuery20 MSysQuery19

9.   CROSSTAB Query (TRANSFORM)

 

DELETE tblCurrencies.*, tblCurrencyExchange.Currency

FROM tblCurrencies LEFT JOIN tblCurrencyExchange ON tblCurrencies.CurrencyCode = tblCurrencyExchange.Currency

WHERE (((tblCurrencyExchange.Currency) Is Null));

MSysQuery21 MSysQuery22

The contents of the MSysQueries table can be used to view the structure of all saved queries

Most complex queries work in exactly the same way as those listed above

 

In theory, it would be possible to 'reverse engineer' the MSysQueries table data to construct query SQL baesd on the rules above

Thankfully, there is no need as Access does the hard work instead!

Conclusions

IMPORTANT

System tables are used by Access to make databases function correctly

 

Some system tables can be viewed & a few can be edited

But that doesn't mean you should do so ....UNLESS YOU ARE ABSOLUTELY SURE WHAT YOU ARE DOING

Altering one table may have 'knock on' effects on other tables

 

Incorrectly editing system tables may corrupt your database or prevent you opening it

 

Anyway, having made that point, I'll continue…

 

Although it is ALWAYS important to take great care when viewing system tables, the MSysQueries table is READ ONLY so no damage can be done.

Even so, it is better to create a query for this purpose than view the system table directly.