Page 2

Click any image to view a larger version



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.

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



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

TableA TableB
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

SelectInner1

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

SelectInner2


4.   SELECT Query – INNER JOIN (Filtered)                                                                                             Return To Top

SelectInnerFilter1

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

SelectInnerFilter2


5.   SELECT Query – Multiple INNER JOINS (Filtered)                                                                           Return To Top

SelectMultInnerFilter1

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)

SelectMultInnerFilter2


6.   SELECT Query – Left Join                                                                                                                 Return To Top

SelectLeft1

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

SelectLeft2


7.   SELECT TOP Query                                                                                                                 Return To Top

SelectTop1

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

SelectTop2


8.   SELECT Query with Ambiguous Joins                                                                                                                 Return To Top

Ambiguous1

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

Ambiguous2
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

Ambiguous3


9.   Aggregate Query – GROUP BY                                                                                                         Return To Top

Aggregate1

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

Aggregate2


10.   Append Query – INSERT                                                                                                                   Return To Top

Append1

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

Append2


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

Append Values

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

Update1

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

Update2


13.   DELETE Query                                                                                                                                     Return To Top

Delete1

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

Delete2


14.   CROSSTAB Query (TRANSFORM)                                                                                                     Return To Top

Crosstab1

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

Crosstab2


15.   MAKE TABLE Query (INSERT INTO)                                                                                               Return To Top

MakeTable1

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

MakeTable2


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

Union
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 Parameter1

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

Parameter2


18.   Cartesian Join Query (No Join)                                                                                                     Return To Top

Cartesian1

SELECT tblLookup.ID, tblLookup.NumberValue, tblSettings.ItemName, tblSettings.ItemValue
FROM tblLookup, tblSettings;



Attribute 7 is missing as there are no joins

Cartesian2


19.   Query to External Database                                                                                                           Return To Top

ExternalQuery1

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

ExternalQuery2


20.   SELECT query with Attachment Data                                                                                             Return To Top

Attach1

SELECT tblAttach.ID, tblAttach.AttachFiles
FROM tblAttach;



Attribute 12 Flag = 2 indicates the query includes an attachment field

Attach2

The Attachment data can be expanded:

AttachFull1

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

AttachFull2


21.   SELECT Query with MultiValued Field data                                                                                     Return To Top

Similar to Attachment field. The example below includes the attached MVF data

MVF1

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

MVF2


22.   SELECT query with Version History Data                                                                                       Return To Top

This is the third type of complex data field – memo field version history

ColHist1

SELECT tblColumnHistory.ID, tblColumnHistory.MemoField
FROM tblColumnHistory;



Attribute 12 has Flag = 1 for this complex datatype

ColHist2


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

NonEquiJoin1
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

Upend1

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

Upend2


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

DataDefinition


26.   Passthrough Query                                                                                                                         Return To Top

SQL view only

EXEC spGetStudentAttendanceMarks 12876



Attribute 1 has Flag = 8 indicating a passthrough query

Passthrough


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)

Subquery1

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)

Subquery2


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.

Temp1

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

Temp2


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

DeletedQuery
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



Return to Access Articles Page Return to Top 1 2
Page 1 of 2