First Published 3 Feb 2019 Last Updated 7 Aug 2022 Difficulty level : Advanced
Section Links:
Introduction
The MSysQueries Table Explained
SELECT Query – INNER JOIN & ORDER BY
SELECT Query – INNER JOIN (Filtered)
SELECT Query – Multiple INNER JOINS (Filtered)
SELECT Query – Left Join
SELECT TOP Query
SELECT Query- Ambiguous Joins
Aggregate Query – GROUP BY
Append Query – INSERT
Append Query – INSERT VALUES
UPDATE Query
DELETE Query
CROSSTAB Query (TRANSFORM)
MAKE TABLE Query
UNION Query
PARAMETER Query
Cartesian Join Query
Query to External Database
SELECT Query with Attachment Data
SELECT Query with MultiValued Field data
SELECT Query with Version History Data
SELECT Query with non equi-join & table aliases
UPEND / UPSERT Query
Data Definition Query
Passthrough Query
Subquery
TEMP query (form/report record source)
Deleted Query
Summary
Downloads
This is a significantly expanded version of the article which was first published in February 2019.
It now includes many more example queries together with additional information about the data stored in the MSysQueries system table.
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.
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:
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:
2. The MSysQueries table explained Return To Top
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 remainder of this article provides examples of almost all types of query that can be created in Access.
The attached database also includes many queries to illustrate how the MSysQueries table is used by Access to store query details.
3. SELECT Query – INNER JOIN & ORDER BY Return To Top
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;
Attribute 1 has Flag = 1 indicating a SELECT query
Attribute 5 shows table names, 6 has field names, 7 has join info, 11 has ORDER BY info
4. SELECT Query – INNER JOIN (Filtered) Return To Top
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;
Attribute 8 has the WHERE clause info
5. SELECT Query – Multiple INNER JOINS (Filtered) Return To Top
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;
Attribute 3 has Flag = 2 indicating unique values (DISTINCT)
6. SELECT Query – Left Join Return To Top
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;
Attribute 7 has Flag = 2 indicating a left join
7. SELECT TOP Query Return To Top
SELECT TOP 20 Count(WhoseOn.LogInID) AS TotalLogins, WhoseOn.TeacherID
FROM WhoseOn
WHERE (((WhoseOn.TimeOn)>=GetAcYearStart()))
GROUP BY WhoseOn.TeacherID
HAVING (((WhoseOn.TeacherID)<>"****"))
ORDER BY Count(WhoseOn.LogInID) DESC , WhoseOn.TeacherID;
Attribute 3 has Flag = 16 indicating a TOP clause
8. SELECT Query with Ambiguous Joins Return To Top
SELECT Table1.ID, Table1.DummyTest, Table1.NumberText, Table2.TextField, Table3.TextField2
FROM Table3
LEFT JOIN (Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID) ON Table3.ID = Table2.ID;
Attribute 7 has Flag = 2 for both rows indicating 2 left joins
NOTE:
Queries with ambiguous joins cannot be executed until the join directions are fixed.
To force one of the joins to be performed first, stacked queries must be used
9. Aggregate Query – GROUP BY Return To Top
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;
Attribute 10 shows the HAVING clause used to filter the Year field in the aggregate query
10. Append Query – INSERT Return To Top
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));
Attribute 1 has Flag = 3 indicating an INSERT (Append) query
11. Append Query – INSERT VALUES Return To Top
SQL view only:
INSERT INTO tblSettings ( ID, ItemName, ItemValue )
VALUES (9, 'WebPage', 'https://www.isladogs.co.uk/recover-deleted-objects/');
NOTE:
If query is saved in Design View, Access changes this to a standard INSERT INTO query
Attribute 1 has Flag = 3 indicating an INSERT (Append) query
NOTE:
The Flag for the 3 values to be appended = -32768 which is is the Flags value for a form in the MSysObjects table!
No, I don't understand it either . . .
12. UPDATE Query Return To Top
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"));
Attribute 1 has Flag = 4 indicating an UPDATE query
13. DELETE Query Return To Top
DELETE tblCurrencies.*, tblCurrencyExchange.Currency
FROM tblCurrencies LEFT JOIN tblCurrencyExchange ON tblCurrencies.CurrencyCode = tblCurrencyExchange.Currency
WHERE (((tblCurrencyExchange.Currency) Is Null));
Attribute 1 has Flag = 5 indicating a DELETE query
14. CROSSTAB Query (TRANSFORM) Return To Top
TRANSFORM Count(qryCars.Colour) AS CountOfColour
SELECT qryCars.Manufacturer, qryCars.Year
FROM qryCars
WHERE (((qryCars.Manufacturer)="BMW" Or (qryCars.Manufacturer)="Audi"))
GROUP BY qryCars.Manufacturer, qryCars.Year
ORDER BY qryCars.Manufacturer, qryCars.Year
PIVOT qryCars.Model;
Attribute 1 has Flag = 6 indicating a TRANSFORM (Crosstab) query
15. MAKE TABLE Query (INSERT INTO) Return To Top
SELECT tblImages.ID, tblImages.ImageName, tblImages.ImageType, tblImages.ImageInfo
INTO tblImagesBKP
FROM tblImages;
Attribute 1 has Flag = 2 indicating a SELECT . . . INTO (Make Table) query
16. UNION query
Return To Top
SQL view only
SELECT DISTINCT MSysObjectsEXT.Name, MSysObjectsEXT.Type, MSysObjectsEXT.Flags, Hex([MSysObjectsEXT.Flags]) AS HexFlag,
tblSysObjectFlags.Object, tblSysObjectFlags.Category
FROM MSysObjectsEXT INNER JOIN tblSysObjectFlags ON (MSysObjectsEXT.Flags = tblSysObjectFlags.Deleted)
AND (MSysObjectsEXT.Type = tblSysObjectFlags.Type)
WHERE (((MSysObjectsEXT.Name) Like '~TMPCLP*') AND (MSysObjectsEXT.Type) <>-32761)
UNION SELECT
DISTINCT MSysObjectsEXT.Name, MSysObjectsEXT.Type, MSysObjectsEXT.Flags, Hex([MSysObjectsEXT.Flags]) AS HexFlag,
tblSysObjectFlags.Object, tblSysObjectFlags.Category
FROM MSysObjectsEXT INNER JOIN tblSysObjectFlags ON (MSysObjectsEXT.Flags = tblSysObjectFlags.HiddenDeleted)
AND (MSysObjectsEXT.Type = tblSysObjectFlags.Type)
WHERE (((MSysObjectsEXT.Name) Like '~TMPCLP*') AND (MSysObjectsEXT.Type) <>-32761)
UNION SELECT
DISTINCT MSysObjectsEXT.Name, MSysObjectsEXT.Type, MSysObjectsEXT.Flags, Hex([MSysObjectsEXT.Flags]) AS HexFlag,
tblSysObjectFlags.Object, GetModuleType(MSysObjectsEXT.Name) AS Category
FROM MSysObjectsEXT INNER JOIN tblSysObjectFlags ON (MSysObjectsEXT.Flags = tblSysObjectFlags.HiddenDeleted)
AND (MSysObjectsEXT.Type = tblSysObjectFlags.Type)
WHERE (((MSysObjectsEXT.Name) Like '~TMPCLP*') AND (MSysObjectsEXT.Type) =-32761);
Attribute 1 has Flag = 9 and Attribute 3 has Flag = 3 indicating a UNION query
Attribute 5 uses internal identifiers for each UNION segment in the Name2 field
NOTE:
A UNION ALL query has Attribute 3 Flag = 1
17. PARAMETER Query
Return To Top
Filter parameters can also be entered using the Query Parameters dialog in the Query Design ribbon
PARAMETERS [Select Object] Text ( 255 ), [Select Type] Short, [Select Flags] Short;
SELECT tblSysObjectTypes.*
FROM tblSysObjectTypes;
Attribute 2 has the PARAMETER info where Flag 3 is INTEGER datatype & 10 is TEXT
18. Cartesian Join Query (No Join) Return To Top
SELECT tblLookup.ID, tblLookup.NumberValue, tblSettings.ItemName, tblSettings.ItemValue
FROM tblLookup, tblSettings;
Attribute 7 is missing as there are no joins
19. Query to External Database Return To Top
INSERT INTO tblTableList ( DBName, DBPath, TableName, [Connect], [Database], Flags, Type )
SELECT 'ViewExternalTables_v2.7 Pro.accdb' AS DBName,
'G:\MyFiles\ExampleDatabases\ExternalTableViewer\ViewExternalTables_v2.7 Pro.accdb' AS DBPath,
MSysObjects.Name AS TableName, MSysObjects.[Connect], MSysObjects.[Database], MSysObjects.Flags, MSysObjects.Type
FROM MSysObjects
IN '' [MS Access;PWD=;DATABASE=G:\MyFiles\ExampleDatabases\ExternalTableViewer\ViewExternalTables_v2.7 Pro.accdb]
WHERE ((MSysObjects.Type) IN (1,4,6)) ORDER BY MSysObjects.Name;
Attribute 4 shows the connection string to the external database
20. SELECT query with Attachment Data Return To Top
SELECT tblAttach.ID, tblAttach.AttachFiles
FROM tblAttach;
Attribute 12 Flag = 2 indicates the query includes an attachment field
The Attachment data can be expanded:
SELECT tblAttach.ID, tblAttach.AttachFiles, tblAttach.AttachFiles.FileData,
tblAttach.AttachFiles.FileName, tblAttach.AttachFiles.FileType
FROM tblAttach;
Attribute 12 Name1 field shows the deep hidden attached table info
21. SELECT Query with MultiValued Field data Return To Top
Similar to Attachment field. The example below includes the attached MVF data
SELECT tblMVF2.ID, tblMVF2.MVF2, tblMVF2.MVF2.Value
FROM tblMVF2;
Once again, Attribute 12 has Flag = 2 and the deep hidden attached table info is shown in the Name1 field
22. SELECT query with Version History Data Return To Top
This is the third type of complex data field – memo field version history
SELECT tblColumnHistory.ID, tblColumnHistory.MemoField
FROM tblColumnHistory;
Attribute 12 has Flag = 1 for this complex datatype
23. SELECT Query with non equi-join & table aliases Return To Top
In this example, 2 copies of the same table are joined (self-join) with one field connected with a non equal join (A.Reading > B.ReadingDate)
NOTE:
This method is commonly used to get a value from the previous record.
In this case, the previous meter reading
Aliases A & B have been used for each copy of the table
Non-equi-joins cannot be displayed in design view
SELECT A.MeterFK, A.ReadingDate, A.Reading, B.ReadingDate, B.Reading, [a].[readingdate]-[b].[readingdate] AS days, [A].[reading]-[b].[reading]-(100000*([a].[readingdate]=#11/12/2020#)) AS used, [used]/[days] AS perday
FROM tblMeterReadings AS A INNER JOIN tblMeterReadings AS B ON (A.Reading > B.ReadingDate) AND (A.MeterFK = B.MeterFK)
WHERE (((A.MeterFK) <=1) AND ((B.Estimate)=False) AND ((A.Estimate)=False))
ORDER BY A.MeterFK, A.ReadingDate;
Attribute 7 shows all joins correctly including the non equi-join
NOTE:
In this example, note that Attribute 1 (SELECT) and 3 are both missing
I will explain this point in the second part of this article (to follow)
24. UPEND/UPSERT Query Return To Top
An UPEND or UPSERT query combines both an APPEND and UPDATE into one query
To create this, make an UPDATE query to update one table from another then change it from an INNER join to an OUTER join from the source table to the destination table.
NOTE:
For more details, see my article: The UPEND or UPSERT query
UPDATE tblNew RIGHT JOIN tblOld ON tblNew.ID = tblOld.ID
SET tblNew.StartDate = [tblOld].[StartDate], tblNew.EndDate = [tblOld].[EndDate], tblNew.NumberField = [tblOld].[NumberField];
Attribute 1 has Flag = 4 indicating an UPDATE query
Attribute 7 has Flag = 3 indicating an RIGHT OUTER join
25. Data Definition Query Return To Top
SQL view only
ALTER TABLE tblAttach ADD COLUMN T TEXT(5);
Attribute 1 has Flag = 7 indicating a data definition (DDL) query
26. Passthrough Query Return To Top
SQL view only
EXEC spGetStudentAttendanceMarks 12876
Attribute 1 has Flag = 8 indicating a passthrough query
27. Subquery
Return To Top
In this example, a DELETE query uses a subquery to only delete records that do not match certain criteria.
To do this, the query checks if the subquery criteria are False (Not Exists)
DELETE tblData.*, Exists (SELECT 1 FROM tblImportNoPK WHERE (tblImportNoPK.StartDate =tblData.StartDate)
AND (tblImportNoPK.EndDate = tblData.EndDate) AND (tblImportNoPK.NCheck = tblData.NCheck)) AS Unmatched
FROM tblData
WHERE (((Exists (SELECT 1 FROM tblImportNoPK WHERE (tblImportNoPK.StartDate =tblData.StartDate)
AND (tblImportNoPK.EndDate = tblData.EndDate) AND (tblImportNoPK.NCheck = tblData.NCheck)))=False));
The subquery info is shown in attribute 6 (Field info) and attribute 8 (WHERE clause)
28. TEMP query (form/report record source) Return To Top
Access creates ‘temp’ queries for use as form/report record sources and for combo/listbox row sources.
These always begin with ~sq_ and are not visible in the navigation pane.
However, they can be viewed from the property sheet for the object or control.
SELECT DISTINCTROW PupilData.PupilID, [Surname] & " " & [Forename] AS Student, [YearGroup] & [TutorGroup] AS TGp,
ClassRecords.ClassID, PupilData.Gender, PupilData.COPLevel, PupilData.[Photo Available], PupilData.Surname, PupilData.Forename
FROM ClassRecords INNER JOIN PupilData ON ClassRecords.PupilID = PupilData.PupilID
GROUP BY PupilData.PupilID, [Surname] & " " & [Forename], [YearGroup] & [TutorGroup], ClassRecords.ClassID, PupilData.Gender,
PupilData.COPLevel, PupilData.[Photo Available], PupilData.Surname, PupilData.Forename
ORDER BY PupilData.Surname, PupilData.Forename;
Attribute 3 Flag = 9 indicates a temp query. In this case, it is used as a form record source
Attribute 2 Flag = 0 indicating the value of the filtered field used in the record source
Double underscores are used (__ChildID) to identify the master field used in the master/child field join
29. Deleted Query
Return To Top
Recently deleted queries are renamed with a ~TMPCLP prefix and are no longer visible in the navigation pane
However, their properties can be determined as for all other queries
Attribute values are identical to those for the same query before it was deleted
So, in theory, you can reconstruct the query from this information if it is still needed.
However, luckily there is a much easier method:
See my article: Recover Deleted Database Objects Example
NOTE:
The ~TMPCLP query is deleted permanently when the database is compacted or closed
30. Summary Return To Top
The contents of the MSysQueries table can be used to view the structure of all saved queries
All queries, no matter how complex, work in exactly the same way as those listed above
In principle, it is possible to 'reverse engineer' the MSysQueries table data to construct query SQL based on the rules above
Thankfully, there is normally no need as Access does the hard work instead!
31. Downloads Return To Top
Click to download:
This article as a PDF file: MSysQueries Part 1 - Updated Info
The example database: MSysQueries Example
Further reading: What does the data in MSysQueries Mean?
DataType enumerations (DAO)
When a query is saved, the view used (Design/SQL) during the save is stored and used next time the query is opened.
The second part of this article explains how Access retrieves that design view/SQL view information for future use
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 7 Aug 2022