[AccessD] Alias Table Name

Can't the field from the query be specified  like you qualify a field from a
table?  For example if the field ProductNumber is in both Table tblA and
tblB, when referring to ProductNumber you'd have to specify
tblA.ProductNumber or tblB.ProductNumber.

Rocky





 	
	

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
Sent: Tuesday, April 15, 2008 8:16 AM
To: Access Developers discussion and problem solving
Subject: Re: [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.


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

No virus found in this incoming message.
Checked by AVG. 
Version: 7.5.519 / Virus Database: 269.22.13/1377 - Release Date: 4/14/2008
9:26 AM