Where did the query field come from?

[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?

 

[Marshall Barton]

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?

Wouldn't it be easier to just alias the field?

 

[Allen Browne]

What I'm trying to do here Marsh, it a general utility to parse any
table/query and figure out what's going on. Uses could include modifying the
query to include a WHERE clause that searches in every field.

One workaround for that scenario would be a query into the existing query
(in which case I can get away with just using the Name property), but I
would love to be able to pin down exactly what the data means.

Any suggestions appreciated (if the question is clear.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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?

Wouldn't it be easier to just alias the field?

 

[Marshall Barton]

What I'm trying to do here Marsh, it a general utility to parse any
table/query and figure out what's going on. Uses could include modifying the
query to include a WHERE clause that searches in every field.

One workaround for that scenario would be a query into the existing query
(in which case I can get away with just using the Name property), but I
would love to be able to pin down exactly what the data means.

Any suggestions appreciated (if the question is clear.)

Well, the what is clear, but I'm not sure about the why ;-)

I don't see how you can get the table instance for an
ambiguous field name without parsing the SQL or getting into
the compiled query. Since I have no idea how to do the
latter, I think you would have to dig into the former.
OTOH, I would not want to do that because of expressions and
subqueries that can be entirely different cans of worms.

If you are going to add criteria, you can't even use the
field name if it's an alias. The more I think about it, the
more I like your idea of using a query to hide all the
possible complications in the original SQL.

Maybe you should post this question to cdma and mvp groups
where others are more likely to jump in with different
ideas.