[AccessD] Alias Table Name

Hey Rocky, just an FYI, there is another specific use for table aliases.

If you have a subquery, that needs data from the parent query, the table
must be aliased in the parent query.

For example:

tblProducts
ProductID
ProductName
ProductDescription

tblInventoryTransactions
ProductID
TransactionValue (negative for out of inventory, positive for into
inventory)

The typical way to get a query to return:

SomePartName Quantity 

Would be to use a Totals query, but the problem is, there are some
limitations there.  Memo fields can cause some issues there too.

So, you could use a subquery, like this:

SELECT ProductName, ProductDescription, (SELECT Sum(TransactionValue)
FROM tblInventoryTransactions WHERE
tblInventoryTransactions.ProductID=T1.ProductID)
FROM tblProducts AS T1

Without aliasing tblProducts, Jet isn't going to know to use the data
from the 'parent' query.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
at Beach Access Software
Sent: Monday, April 14, 2008 6:53 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Alias Table Name

Thanks to all for their replies.  

Rocky
 




 	
	

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Monday, April 14, 2008 8:37 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Alias Table Name

Rocky,

  For some, it's just a matter of formatting and/or saving some time as
Mark
and Susan said.

  But also in the past, Access had a hard "compile" limit on queries of
64K.
A lot of developers used that technique to shorten table references to
get
around that problem enabling them to run more complex queries.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
at
Beach Access Software
Sent: Monday, April 14, 2008 11:02 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Alias Table Name

Dear List:
 
In a legacy app I came across some queries which in the QBE had
'alias'ed
tables (or maybe queries).  one called P was joined to one called A.
The
list of field names didn't correspond to any table.  There are a lot of
queries so I didn't look through them to see if a query matched the
field
list in either P or A.
 
But I've never seen this done.  Why would you do this?  How can you tell
what the source of the fields in the aliased table is?  Is there some
performance gain or other reason for doing this?

MTIA,
 
Rocky
 
The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.