Tablenames in queries

J

John J.

I noticed that

SELECT Member.ID_member, Member.Name_member, Member.Prefix
FROM Member
ORDER BY Member.Name_member;

gives the same result as:

SELECT ID_member, Name_member, Prefix
FROM Member
ORDER BY Name_member;

Is there any reason to use one over the other?
Thank you.
John
 
D

Daniel Pineault

In your example, your query is a simple SELECT query where all the fields
come from the same table so yes, they both work fine. One is implicit and
the other explicit. However, in the case, for example, of a join query where
you can have fields from multiple sources, join tableA with tableB and return
me some fields from A and other from B, you would need to explicitly use the
tablename.fieldname format. It depends on your query.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
 
J

John J.

OK, thanks. So to be sure: it doesn't take more time for Access to
understand and run the short query?
 
J

John Spencer

It may take a few milliseconds for Access to understand the implicit field
names versus the explicit field names, but it won't really be noticeable to
anyone.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
K

Klatuu

If there is any difference at all, it will be minimal; however, the extra
time will not happen during runtime. It will happen when you save the
query.

When you save a query, it is optimized and compiled, so in either case, it
would be the same during execution.
 
J

John J.

Thanks guys for confirmation.

John Spencer said:
It may take a few milliseconds for Access to understand the implicit field
names versus the explicit field names, but it won't really be noticeable
to anyone.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
 
J

James A. Fortune

Daniel said:
In your example, your query is a simple SELECT query where all the fields
come from the same table so yes, they both work fine. One is implicit and
the other explicit. However, in the case, for example, of a join query where
you can have fields from multiple sources, join tableA with tableB and return
me some fields from A and other from B, you would need to explicitly use the
tablename.fieldname format. It depends on your query.
Click to expand...

I try to protect myself from joins on two tables where a field might be
added to, say, the second table that is already in the first table
causing a dynamically created SQL string to go awry because of the
resulting field name ambiguity. My concern is greater when the joined
tables come from separate databases. I wrote the following function:


'---Begin module code---
Public Function IsFieldInTableDef(strFieldName As String, strTableName
As String, Optional strDatabase As String) As Boolean
Dim MyDB As DAO.Database
Dim tdf As TableDef
Dim fld As Field
Dim boolFound As Boolean

If strDatabase = "" Then
Set MyDB = CurrentDb
Else
Set MyDB = OpenDatabase(strDatabase, False, True)
End If
Set tdf = MyDB.TableDefs(strTableName)
boolFound = False
For Each fld In tdf.Fields
If fld.Name = strFieldName Then
boolFound = True
Exit For
End If
Next fld
IsFieldInTableDef = boolFound
'The following line is not absolutely required
Set tdf = Nothing
MyDB.Close
Set MyDB = Nothing
End Function
'----End module code----

Then I check fields I am unsure about (usually in the remote database)
and prepend the table name where it's needed. I like using just the
field name whenever possible. Alternatively, you can skip using the
function altogether and always prepend the table name to be safe when
you're unsure :). The function is also useful in situations where a
join may or may not be needed in a dynamically generated SQL string
depending on which fields are required.

James A. Fortune
[email protected]
 
Top