Mendip Data Systems
Database applications for businesses and schools
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
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
Click to download this article as a PDF file: MSysQueries Info
Click to download the sample database: MSysQueriesExample
What does the data in MSysQueries Mean?
I would be grateful for any feedback on this article including details of any errors or omissions
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:
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
The attached database includes several queries to illustrate how the MSysQueries table works
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;
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;
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;
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;
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));
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"));
8. DELETE Query
DELETE tblCurrencies.*, tblCurrencyExchange.Currency
FROM tblCurrencies LEFT JOIN tblCurrencyExchange ON tblCurrencies.CurrencyCode = tblCurrencyExchange.Currency
WHERE (((tblCurrencyExchange.Currency) Is Null));
9. CROSSTAB Query (TRANSFORM)
DELETE tblCurrencies.*, tblCurrencyExchange.Currency
FROM tblCurrencies LEFT JOIN tblCurrencyExchange ON tblCurrencies.CurrencyCode = tblCurrencyExchange.Currency
WHERE (((tblCurrencyExchange.Currency) Is Null));
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.