SQL for Query Referring to Itself

[PhilOfWalton]

I need to write some SQL to be used with VBA where a query refers to itself.

Not very clear I know, so here is the finished result for Query21.
I am trying to get the value of CountTables.

This is the SQL for Query21

SELECT TblPSysAllFields.TableName, TblPSysAllFields.FieldName, 
TblPSysAllFields.InputTable, TblPSysAllFields.InputField, 
DCount("TableName","Query21","TableName = '" & [TableName] & "'") AS CountTables
FROM TblPSysAllFields
WHERE (((TblPSysAllFields.FieldName) Is Not Null) 
AND ((TblPSysAllFields.InputTable) Is Not Null) AND ((TblPSysAllFields.Level)=1) 
AND ((TblPSysAllFields.IsPrimary) Not Like "*P*"))
ORDER BY TblPSysAllFields.FieldName, TblPSysAllFields.TableName;

So the problem is that the SQL refers to Query21, but I don't want Query 21 to be a saved Query because in the code, I will be changing the value of "Level"

Is there a way round this please?

Phil

[twinnyfo]

Phil,

Try using a subquery. You would just have to make sure to change "Level" in each section.

SELECT TblPSysAllFields.TableName, 
       TblPSysAllFields.FieldName, 
       TblPSysAllFields.InputTable, 
       TblPSysAllFields.InputField, 
       tblSubTables.CountTables
FROM TblPSysAllFields 
INNER JOIN (SELECT TblPSysAllFields.TableName, 
                   Count(TblPSysAllFields.TableName) AS CountTables
            FROM TblPSysAllFields
            WHERE (((TblPSysAllFields.FieldName) Is Not Null) 
              AND ((TblPSysAllFields.InputTable) Is Not Null) 
              AND ((TblPSysAllFields.Level)=1) 
              AND ((TblPSysAllFields.IsPrimary) Not Like "*P*"))
            GROUP BY TblPSysAllFields.TableName) AS tblSubTables 
ON TblPSysAllFields.TableName = tblSubTables.TableName
WHERE (((TblPSysAllFields.FieldName) Is Not Null) 
  AND ((TblPSysAllFields.InputTable) Is Not Null) 
  AND ((TblPSysAllFields.Level)=1) 
  AND ((TblPSysAllFields.IsPrimary) Not Like "*P*"))
ORDER BY TblPSysAllFields.FieldName, 
         TblPSysAllFields.TableName;

Hope this hepps!

[PhilOfWalton]

Thanks Twinnyfo

Other than a small typo error, it worked perfectly. I must admit that among my many Access weeknesses, SubQueries is high on the list.

Grateful thanks

Phil

[twinnyfo]

I think I learned everything I know about Sub-Queries here on Bytes!

[NeoPa]

Subqueries in SQL may help.