Click any image to view a larger version



Last Updated 3 Feb 2019                                                Difficulty level :   Advanced

Section Links:
          Introduction
          SELECT query – INNER JOIN & ORDER BY
          SELECT query – INNER JOIN (Filtered)
          SELECT query – Multiple INNER JOINS (Filtered)
          SELECT query – Left Join
          Aggregate Query – GROUP BY
          Append Query – INSERT
          UPDATE Query
          DELETE Query
          CROSSTAB Query (TRANSFORM)
          Summary
          Downloads



1.   Introduction                                                                                                                                     Return To Top

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.

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.

MSysQuery1

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:

MSysQuery2

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;


NOTE:
Flags value = 3 is used for TEMP queries used with form and report record sources - so it has been EXCLUDED here

Running the query shows these values for the query itself:

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


2.   SELECT query – INNER JOIN & ORDER BY                                                                                     Return To Top

MSysQuery5

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;


MSysQuery6


3.   SELECT query – INNER JOIN (Filtered)                                                                                             Return To Top

MSysQuery7

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;


MSysQuery8


4.   SELECT query – Multiple INNER JOINS (Filtered)                                                                           Return To Top

MSysQuery9

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;


MSysQuery10


5.   SELECT query – Left Join                                                                                                                 Return To Top

MSysQuery11

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;


MSysQuery12


6.   Aggregate Query – GROUP BY                                                                                                         Return To Top

MSysQuery13

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


7.   Append Query – INSERT                                                                                                                   Return To Top

MSysQuery15

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));


MSysQuery16


8.   UPDATE Query                                                                                                                                     Return To Top

MSysQuery17

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"));


MSysQuery18


9.   DELETE Query                                                                                                                                     Return To Top

MSysQuery19

DELETE tblCurrencies.*, tblCurrencyExchange.Currency
FROM tblCurrencies LEFT JOIN tblCurrencyExchange ON tblCurrencies.CurrencyCode = tblCurrencyExchange.Currency
WHERE (((tblCurrencyExchange.Currency) Is Null));


MSysQuery20


10.   CROSSTAB Query (TRANSFORM)                                                                                                     Return To Top

MSysQuery21

DELETE tblCurrencies.*, tblCurrencyExchange.Currency
FROM tblCurrencies LEFT JOIN tblCurrencyExchange ON tblCurrencies.CurrencyCode = tblCurrencyExchange.Currency
WHERE (((tblCurrencyExchange.Currency) Is Null));


MSysQuery22


11.   Summary                                                                                                                                        Return To Top

The contents of the MSysQueries table can be used to view the structure of all saved queries
More complex queries work in exactly the same way as those listed above

In principle, 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!



12.   Downloads                                                                                                                                     Return To Top

Click to download:

This article as a PDF file:     MSysQueries Info

The example database:        MSysQueries Example


Further reading:     What does the data in MSysQueries Mean?


Please contact me using the form below with any feedback on this article including details of any errors or omissions.



Colin Riddington           Mendip Data Systems                 Last Updated 27 Feb 2019



Return to Access Articles Page Return to Top