[AccessD] Query SQL correct but not visible in design view

John W. Colby jwcolby at colbyconsulting.com
Mon Dec 27 20:06:45 CST 2004


Jim,

I actually have a very rigorous naming convention that I do follow,
religiously.  My convention is:

I NEVER use special characters in any object name -
`~!@#$%^&*()_-+=|\}]{["':;?/>.<,|\ are all forbidden characters in an object
name (as well as a space).

I ALWAYS prefix objects - frm/sfrm, rpt/srpt, qry (or qxx), tbl/tlkp/tmm, -
and of course I don't use macros except for autoexec and autokeys, neither
of which can have a mac prefix. 

I do NOT prefix fields with fld

I do NOT put the data type in the field name.  I have just had too many
instances where the data type changes, and it is a PITA doing the search and
replace to make the change.

In field names, I use the first two characters of the table name, unless the
name is multiple words, in which case I use the first letter of each word.

tblPeople = PE
tblAutos = AU
tblPeopleAutos = PA
Etc.

I then use an underscore to delineate the end of the table name prefix

PE_
PA_

I then use ID for the PK

PE_ID
PA_ID

I use words, sometimes abbreviations for the words

PE_LastName or
PE_LName 

Foreign keys are the PK name in the "parent" table, with the underscore
removed, and the 'ID' coming first

tblPeople
PE_ID  <<<
PE_Lname

tblAutos
AU_ID  <<<<
AU_Model
AU_Color

tblPeopleAutos
PA_ID
PA_IDPE  <<<<
PA_IDAU  <<<<

I place all FKs at the top of the table so that they can be easily found in
any table.  I "swap" the ID and the table prefix so that it is obvious that
a field is a FK.  If there is an ID after the underscore, then the field is
a FK (or the PK of the table).  _IDPE, _IDAU etc.

This convention makes it dead simple to work with tables.  There is NEVER a
field name in two different tables with the same field name, since the table
name prefix is always unique.  I never get the "can't figure out what table
this field comes from" message from the query builder.  

It is dead simple to look at a FK and "see" what the PK is called in the
"parent" table:

PA_IDPE  <<

Take the IDPE, swap the ID and PE, and place an underscore between them

PE_ID

After using this convention for a little while, it "just works" and makes
the process of finding relationships between tables a breeze.

I have run into a few instances where there are hundreds of tables where I
have to maintain a table of prefixes to ensure that there aren't collisions
(two tables with the same prefix).  In those instances it I usually have to
go with more than two characters to denote a table name word.

I have used this convention since about 1995 or thereabouts.  I have shown
many developers how to use it and most of them love it.  

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim
Sent: Monday, December 27, 2004 11:41 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Query SQL correct but not visible in design view


Do you use foreign key field names that are  different from the primary key
name in the parent tables? What sort of naming convention do you use? I
always find it easier to understand a database  when I can track fields such
as fldFirstName throughout a table structure. However, I must confess I
don't religiously follow this naming convention myself. Jim Hale

-----Original Message-----
From: John W. Colby [mailto:jwcolby at colbyconsulting.com]
Sent: Saturday, December 25, 2004 12:40 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Query SQL correct but not visible in design view


That would explain it.  I do not use the table qualifier unless the field
name is the same in different tables which is generally not the case in my
systems.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
Sent: Saturday, December 25, 2004 12:23 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Query SQL correct but not visible in design view


John,

    If an SQL statement is put directly in SQL view of QBE,  without having
first dragged any field into the QBE grid, relevant fields get displayed in
the grid only if their names in  SELECT clause are qualified by the table
name.

    For example -
    (a) SELECT *  FROM  tbl1 - will not show any field in QBE grid.
    (b) SELECT  tbl1.*  FROM  tbl1 - will result in correct display in QBE
grid.

    Note - This is as per tests conducted on Access XP running on Win XP.

A.D.Tejpal
--------------

  ----- Original Message ----- 
  From: John W. Colby 
  To: 'Access Developers discussion and problem solving' 
  Sent: Friday, December 24, 2004 09:04
  Subject: [AccessD] Query SQL correct but not visible in design view


  I am building a SQL statement "on the fly" which essentially compares
matching fields in two different tables - Tbl1.Lname <> Tbl2.Lname.  The SQL
runs but only the tables and their joins are visible in the QBE grid.  There
are no fields visible etc.  Does anyone understand what causes Access to do
  this?  
  I have had the same thing occur even with something as simple as a SELECT
* from tbl1 inserted into the SQL view.  Tbl1 is displayed, but nothing in
the grid.  The query executes correctly.

  Anyone?

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



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

***********************************************************************
The information transmitted is intended solely for the individual or entity
to which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of or
taking action in reliance upon this information by persons or entities other
than the intended recipient is prohibited. If you have received this email
in error please contact the sender and delete the material from any
computer. As a recipient of this email, you are responsible for screening
its contents and the contents of any attachments for the presence of
viruses. No liability is accepted for any damages caused by any virus
transmitted by this email.





More information about the AccessD mailing list