A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Best Practice for tables?



 
 
Thread Tools Display Modes
  #11  
Old August 21st, 2007, 04:41 AM posted to microsoft.public.access.gettingstarted
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Best Practice for tables?

Tony,

Just my opinion...

I use the object naming and then take it a step further...

tblOrders
oOrderID-PK
oDate
oClientID
etc...

tblOrderDetail
odOrderID-FK
odItemID
odDescription
etc...

qryOrders may contain
oOrderID (from tblOrders)
cpCompanyName (I know this comes from tblClientProfile)
oDate (from tblOrders)
odItemID (from tblOrderDetails)
odDescription (from tblOrderDetails)

If I have to do a DLookUp, I know whether I went to the table or the query
to get my information. I think that best naming convention is the one that
works for you and you will follow thru on.


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
"Tony Toews [MVP]" wrote in message
...
Kamitsukenu wrote:

(tblCountry) - holds the Country names
(tblDispatchMethod) - the service the item is sent by
(tblSupplier) - who the item is mailed by


Don't bother with the tbl prefix. Basically it's a waste of time.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/



  #12  
Old August 21st, 2007, 04:46 AM posted to microsoft.public.access.gettingstarted
Steve[_10_]
external usenet poster
 
Posts: 608
Default Best Practice for tables?

50 some character table names and query names and you are advising the OP to
drop 3 characters from his table names???? Not much logic to that.

You need the advise rather than the OP. Shorten your table names and query
names and save a lot of time typing and reduce the probability of typos.

Unbelievable, Tony!!!!!!!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications




"Tony Toews [MVP]" wrote in message
...
"Steve" wrote:

QryCountry
QryDispatchMethod
QrySupplier

Would you also advise the OP to not bother with the Qry prefix?


Yes.

Suppose he has Country, DispatchMethod and Supplier tables as well as
Country, DispatchMethod and Supplier queries. When he uses these as
recordsources for forms and reports or in code, how is he suppose to know
if
the reference is to the table or query?


My queries have much more descriptive names than one word. I
frequently run up against the 50 or 52 character limit.

So this is a non issue.

Also see Tony's Table and Field Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/



  #13  
Old August 21st, 2007, 08:21 AM posted to microsoft.public.access.gettingstarted
Arno R[_2_]
external usenet poster
 
Posts: 35
Default Best Practice for tables?


"Steve" schreef in bericht ...

Unbelievable, Tony!!!!!!!


Too bad for you Steve that !!!!!!! is not allowed in the objectnames ....

Arno R
  #14  
Old August 21st, 2007, 12:43 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default Best Practice for tables?

I respectfully disagree with Tony. I prefix my queries with a designator
(usually just the letter q, but sometimes qry).

I do this since I tend to use the query wizards to get me started when I am
constructing my queries and the leading character lets me know when the
interface is displaying a query or a table.

Personal preference. On the other hand, I agree with Tony - I don't see any
reason to prefix tables with TBL or fields with fld. That usually just
means more typing for no real benefit that I see. Again, if it works for
you - go ahead and use it.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Tony Toews [MVP]" wrote in message
...
"Steve" wrote:

QryCountry
QryDispatchMethod
QrySupplier

Would you also advise the OP to not bother with the Qry prefix?


Yes.

Suppose he has Country, DispatchMethod and Supplier tables as well as
Country, DispatchMethod and Supplier queries. When he uses these as
recordsources for forms and reports or in code, how is he suppose to know
if
the reference is to the table or query?


My queries have much more descriptive names than one word. I
frequently run up against the 50 or 52 character limit.

So this is a non issue.

Also see Tony's Table and Field Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/



  #15  
Old August 21st, 2007, 02:44 PM posted to microsoft.public.access.gettingstarted
Arvin Meyer [MVP]
external usenet poster
 
Posts: 4,231
Default Best Practice for tables?

"Gina Whipp" wrote in message
...

If I have to do a DLookUp, I know whether I went to the table or the query
to get my information. I think that best naming convention is the one
that works for you and you will follow thru on.


I agree with that. I use the Leszynski/Reddick naming convention, almost
exactly as it was published. I only do it because other programmers often
work on the same projects and I like to maintain some consistency with a
generally accepted method. If you are the only person working then whatever
consistency you use is really up to you.

I also keep my naming as short as possible, but still able to adequately
identify every object. I do have some very long names, but most are under 15
to 20 characters.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


  #16  
Old August 21st, 2007, 07:17 PM posted to microsoft.public.access.gettingstarted
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default Best Practice for tables?

"Steve" wrote:

50 some character table names and query names and you are advising the OP to
drop 3 characters from his table names???? Not much logic to that.


Ah, one reason is that in the database container window you can just
hit the first character of a group of queries, forms, whatever. Then
you can quickly go to that set of objects. Such as P for PO or I for
inventory.

You need the advise rather than the OP. Shorten your table names and query
names and save a lot of time typing and reduce the probability of typos.


Huh? When I need a object name I click once on the object, wait a
second, click again as though I were going to rename the object and
copy the name. Done. No types.

Unbelievable, Tony!!!!!!!


Clearly you have not a clue.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
  #17  
Old August 21st, 2007, 07:19 PM posted to microsoft.public.access.gettingstarted
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default Best Practice for tables?

"Gina Whipp" wrote:

I use the object naming and then take it a step further...

tblOrders
oOrderID-PK
oDate
oClientID
etc...


With the exception of the tbl I agree with you.

Tony's Table and Field Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
  #18  
Old August 21st, 2007, 07:29 PM posted to microsoft.public.access.gettingstarted
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Best Practice for tables?

Tony...

Just curious... (Always willing to learn something new!)

What about in the case of DLookUp's (just one example), how would you know
where you are pulling the data from? Or do you table and query names never
match?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
"Tony Toews [MVP]" wrote in message
...
"Gina Whipp" wrote:

I use the object naming and then take it a step further...

tblOrders
oOrderID-PK
oDate
oClientID
etc...


With the exception of the tbl I agree with you.

Tony's Table and Field Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/



  #19  
Old August 21st, 2007, 09:19 PM posted to microsoft.public.access.gettingstarted
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default Best Practice for tables?

"Gina Whipp" wrote:

Just curious... (Always willing to learn something new!)

What about in the case of DLookUp's (just one example), how would you know
where you are pulling the data from? Or do you table and query names never
match?


I never use DLookups but code my own using recordsets and queries
usually defined in VBA code.

My table and query names can never match as my query names are quite
lengthy and descriptive and always have spaces. (Well almost always).
For example in a simple system I built for memberships the table names
are

Activity
Emails
GlobalOptions
Member
MemberFamily
MembershipActivity
MembershipEmails
Status

And the query names are

Acknowledgements to be sent via email
Acknowledgements to be sent via snail mail
Action - Acknowledgements
Action - Renewals
Active members
Activity - Renewal
Activity Summary - MembershipActivityDetails
AGM Notices to be sent
AGM Notices to be sent - Actiivity Append
Integrity checker - Renewals but not active
Membership Acknowledgements - Outstanding
Membership Activity History
Membership Renewals - Outstanding
Membership Renewals Outstanding
MembershipDetailReport
MembershipNoneDetailReport
Name Badges to be sent via snail mail
Non members with Email Addresses
Renewal Acknowledgements to be sent via email
Renewal Acknowledgements to be sent via snail mail for Pending R
Renewal Notices to be sent
Renewal Notices to be sent via email
Renewal Notices to be sent via snail mail

The code I used to produce the above is (as adapted from A97 help)

Sub ListQueryNames()

Dim qry As QueryDef, db As Database

Dim dbs As Database
Dim qdf As QueryDef

Set dbs = CurrentDb

For Each qdf In dbs.QueryDefs
Debug.Print " " & qdf.Name
Next qdf

End Sub

Sub ListTableNames()

Dim qry As TableDef, db As Database

Dim dbs As Database
Dim tdf As TableDef

Set dbs = CurrentDb

For Each tdf In dbs.TableDefs
Debug.Print " " & tdf.Name
Next tdf

End Sub


Now I have a much larger system with 160 tables, 1200 queries, 450
forms and 350 reports. But that would be rather long for folks to
look at the names. smile

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
  #20  
Old August 21st, 2007, 09:20 PM posted to microsoft.public.access.gettingstarted
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default Best Practice for tables?

"Steve" wrote:

Unbelievable, Tony!!!!!!!


BTW Steve I don't like that attitude of yours. If you are going to
discuss things in the newsgroups please keep a civil keyboard on your
desk.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:38 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.