A
Allen Browne
Any suggestions no how to programmatically examine a QueryDef to discover
where a Field came from?
Candidate properties: Name, SourceTable, and SourceField.
But these do not reliably identify the field.
For an example, take the Employees table in Northwind, and add a ReportsTo
field (type Number.) Self-join, related to the EmployeeID of the person's
boss (in the same table.)
So the query is:
SELECT Emp.EmployeeID
FROM Employees AS Emp
LEFT JOIN Employees AS Boss
ON Emp.ReportsTo = Boss.EmployeeID;
Access reports the QueryDef field properties like this:
Name: EmployeeID
SourceTable: Employees
SourceField: EmployeeID
That all makes sense, but I don't know from that which EmployeeID I'm
looking at.
This query reports the same properties for its field:
SELECT Boss.EmployeeID
FROM Employees AS Emp
LEFT JOIN Employees AS Boss
ON Emp.ReportsTo = Boss.EmployeeID;
So does this one:
SELECT [Emp].[EmployeeID] & "-" & [Boss].[EmployeeID]
AS EmployeeID
FROM Employees AS Emp
LEFT JOIN Employees AS Boss
ON Emp.ReportsTo = Boss.EmployeeID;
(Ok, that's a daft alias, but it illustrates the point that I don't know
(programmatically) what field I'm looking at.)
Is there a solution in Access, other than writing a SQL parser to split the
SELECT clause into fields?
where a Field came from?
Candidate properties: Name, SourceTable, and SourceField.
But these do not reliably identify the field.
For an example, take the Employees table in Northwind, and add a ReportsTo
field (type Number.) Self-join, related to the EmployeeID of the person's
boss (in the same table.)
So the query is:
SELECT Emp.EmployeeID
FROM Employees AS Emp
LEFT JOIN Employees AS Boss
ON Emp.ReportsTo = Boss.EmployeeID;
Access reports the QueryDef field properties like this:
Name: EmployeeID
SourceTable: Employees
SourceField: EmployeeID
That all makes sense, but I don't know from that which EmployeeID I'm
looking at.
This query reports the same properties for its field:
SELECT Boss.EmployeeID
FROM Employees AS Emp
LEFT JOIN Employees AS Boss
ON Emp.ReportsTo = Boss.EmployeeID;
So does this one:
SELECT [Emp].[EmployeeID] & "-" & [Boss].[EmployeeID]
AS EmployeeID
FROM Employees AS Emp
LEFT JOIN Employees AS Boss
ON Emp.ReportsTo = Boss.EmployeeID;
(Ok, that's a daft alias, but it illustrates the point that I don't know
(programmatically) what field I'm looking at.)
Is there a solution in Access, other than writing a SQL parser to split the
SELECT clause into fields?