CODEWITHSUNDEEP
sqlms-access
Rick
Rick

Reputation: 2308

Access: Query Join on Alias field

I am trying to Join an alias field with a function in a query. Is this possible in MS Access?

SELECT Mid([StockNum],1,9) AS StockTrim
FROM POs LEFT JOIN [STOCK DICTIONARY] 
    ON POs.[StockTrim] = [STOCK DICTIONARY].[Stock]
GROUP BY Mid([StockNum],1,9), [STOCK DICTIONARY].Stock
HAVING ((([STOCK DICTIONARY].Stock) Is Null));

In this example, I am trying to join on the calculated field StrockTrim, but Access brings up the "Enter Parameter Value" input box for POs.StockTrim

Upvotes: 1

Views: 1647

Answers (2)

Smandoli
Smandoli

Reputation: 7019

Since you accepted mwolfe02's great answer, I hope the problem is solved. I want to point out that since StockTrim is an immediate construct, rather than a field in POs, you may have trouble with:

ON POs.[StockTrim] = [STOCK DICTIONARY].[Stock]

You may have success with:

ON [StockTrim] = [STOCK DICTIONARY].[Stock]

That would assume there is no field named StockTrim in STOCK DICTIONARY.

Just an aside -- you'll be better off in Access if you avoid all use of spaces in names for objects, including tables and queries. STOCK DICTIONARY will work better as STOCK_DICTIONARY, for example.

Upvotes: 0

mwolfe02
mwolfe02

Reputation: 24207

Sometimes you can use the alias in a query, sometimes not. In your case, the following should work (I also cleaned up your GROUP BY/HAVING field to a more appropriate WHERE clause):

SELECT Mid([StockNum],1,9) AS StockTrim
FROM POs LEFT JOIN [STOCK DICTIONARY] 
  ON Mid(POs.[StockNum],1,9) = [STOCK DICTIONARY].[Stock]
WHERE [STOCK DICTIONARY].Stock Is Null
GROUP BY Mid([StockNum],1,9)

Understanding when you can use an alias in an Access query and when you cannot is a bit of dark art. Generally speaking, you may use an alias if it occurs later in the query plan than it first appears. For instance, you could use: 

SELECT Mid([StockNum],1,9) AS StockTrim, "Trim #:" & StockTrim AS StockTrimNumber

The StockTrimNumber field, which occurs later in that sample SELECT clause than the StockTrim calculated field, can use the StockTrim calculated field in its own field definition.

In your situation, the join occurs before any of the fields are calculated or returned, so you need to repeat the field definition in your JOIN clause.

Likewise, if you wanted to filter based on your calculated field you would need to use the field definition itself (e.g., WHERE Mid([StockNum],1,9) LIKE "???XYZ???") but using the alias alone would fail (e.g., WHERE StockTrim Like "???XYZ???").

Upvotes: 3

Related Questions