Numbers in table field names

[dcc15 via AccessMonster.com]

Quick question for a newcommer
I think I heard or read somewhere that I should not use numbers in table
field names, is that true?
Thanks

 

[Jeff Boyce]

I don't recall any Access restriction on using digits in field names...

However, if you are considering using xxxx1, xxxx2, xxxx3, xxxx4, there is a
very good chance that you are attempting to commit spreadsheet on Access.
"Repeating field names" (with #s) is what you'd have to do in a spreadsheet,
but you won't get the best use of Access' relationally-oriented features and
functions if you insist on feeding it 'sheet data.

Provide a bit more specific description for a bit more specific
suggestion... Examples help.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Ken Snell \(MVP\)]

You can use numbers without problem, but it's recommended that you not start
a field name with a number.

 

[Pat Hartman]

I tend to go with the most restrictive "rules" on all user defined names -
tables, columns, queries, variables, etc.
1. May contain ONLY a-z, A-Z, 0-9, and the underscore _
2. Must start with a letter (upper or lower case)
3. Must be limited to 30 characters (I don't violate this rule with table
and column names but may with other names).

A large number of the apps I create get upsized to SQL Server, DB2, or
Oracle and this avoids issues with table and field names - plus personally,
I hate having to enclose mal-formed names in square brackets. I rarely use
underscores so mostly I just use letters, starting each word or word part
with a capital. For example - CustName, BrokerID, SourceCD, Addr1 (for the
first line of an address - this isn't a violation of first normal form - it
is a convenience for creating mailing address so the user can determine what
goes on each line).

 

[Klatuu]

I like your rules, Pat. I use very similar rules with some minor exceptions.
All field names are upper case and must include at least one underscore. I
use numbers only when necessary, for example your addr1 example. (I would use
ADDR_1).
Seems silly, I know, but the reason is that it is obvious the name is a
table field.
--
Dave Hargis, Microsoft Access MVP

I tend to go with the most restrictive "rules" on all user defined names -
tables, columns, queries, variables, etc.
1. May contain ONLY a-z, A-Z, 0-9, and the underscore _
2. Must start with a letter (upper or lower case)
3. Must be limited to 30 characters (I don't violate this rule with table
and column names but may with other names).

A large number of the apps I create get upsized to SQL Server, DB2, or
Oracle and this avoids issues with table and field names - plus personally,
I hate having to enclose mal-formed names in square brackets. I rarely use
underscores so mostly I just use letters, starting each word or word part
with a capital. For example - CustName, BrokerID, SourceCD, Addr1 (for the
first line of an address - this isn't a violation of first normal form - it
is a convenience for creating mailing address so the user can determine what
goes on each line).

 

[Pat Hartman]

Seeing names in all upper case reminds me of my mainframe days When you
get right down to it, I prefer camel case because it is more natural for
touch typing. I can never figure out an efficient way to touch type names
with all caps.

I like your rules, Pat. I use very similar rules with some minor
exceptions.
All field names are upper case and must include at least one underscore.
I
use numbers only when necessary, for example your addr1 example. (I would
use
ADDR_1).
Seems silly, I know, but the reason is that it is obvious the name is a
table field.

 

[Klatuu]

I thought I was the last remaining mainframe coder

I understand, but it is just a way know what I am looking at.
As to typing in all caps, If you look on the left side of your keyboard,
just above the shift key and just below the tab key, there is a key labeld
Caps Lock. I find it very useful. But I still have not found the Any key

 

[Pat Hartman]

Cute but that key isn't in my touch typing repertoire nor is the
underscore. I preferred COBOL (I didn't use the you know what key to type
that) because it used the dash which was lower case. In fact, typing in
COBOL (there I did it again), you didn't need to use the shift key at all.

 

[Klatuu]

Yes, even on the keypunch machines it was all lower case.
I did not even have a terminal for my first two years.
It was a COBOL coding sheet, pencils, a pencil sharpener, a box of erasers
and a wisk broom. And the IMB flowchart template (which I still have).

Good ole COBOL. The War and Peace of programming languages. Fifteen pages
before you get to the first executable statment.

 

[Tony Toews [MVP]]

I like your rules, Pat. I use very similar rules with some minor exceptions.
All field names are upper case and must include at least one underscore.

I would never use all caps as it takes a lot more room on the forms
and reports when in design view. Let's take this paragraph as an
example.

I WOULD NEVER USE ALL CAPS AS IT TAKES A LOT MORE ROOM ON THE FORMS
AND REPORTS WHEN IN DESIGN VIEW. LET'S TAKE THIS PARAGRAPH AS AN
EXAMPLE.

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/

 

[Tony Toews [MVP]]

I thought I was the last remaining mainframe coder

RPG II & III, OCL and CL expert here in the '80s. IBM S/34, S/36,
S/38 and AS/400.

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/

 

[Klatuu]

Regardless of what the System 3x - AS400 cult leaders say, AS400 is not a
mainframe. It is the last remaining mini computer.

 

[Tony Toews [MVP]]

Regardless of what the System 3x - AS400 cult leaders say, AS400 is not a
mainframe. It is the last remaining mini computer.

Oh, absolutely. I never liked working on mainframes what little I saw
of them.

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/

 

[Klatuu]

I certainly understand.
I started on mainframes, then went to a variety of minis, then to pc based
networks.
Most of the people who worked with AS400 really liked them.

 

[Tony Toews [MVP]]

I certainly understand.
I started on mainframes, then went to a variety of minis, then to pc based
networks.
Most of the people who worked with AS400 really liked them.

I did too but then I saw Access and figured Access would be a lot more
fun. I have a cousin who has been working on the AS/400 for about
ten or fifteen years now. He was just telling me that despite his
having taken courses on other technologies he get pigeon holed as an
AS/400 person and he can't get a job in another technology. He is
feeling quite frustrated.

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/

 

[Klatuu]

That happens.
I went through DbaseIII to Foxbase then FoxPro for a long time before I got
into Access. It happened because a client had an old DOS Foxpro app he
wanted converted and updated in Access.

 

[David W. Fenton]

I would never use all caps as it takes a lot more room on the
forms and reports when in design view. Let's take this paragraph
as an example.

I WOULD NEVER USE ALL CAPS AS IT TAKES A LOT MORE ROOM ON THE
FORMS AND REPORTS WHEN IN DESIGN VIEW. LET'S TAKE THIS PARAGRAPH
AS AN EXAMPLE.

I agree with your point, but your example doesn't work -- both
paragraphs take up exactly the same amount of space in my newsreader
because, of course, I'm using a fixed-width font (as is proper for
Usenet posts, since there is no formatting).

 

[dcc15 via AccessMonster.com]

Wow, that stired up some opions.
I am creating a corrective action DB and wanted to be able to asign up to 3
(I guess) CA's per incident, so I wanted to make sure I was not causing
myself potiential problems (future code writing) by simply naming these
fields Ca1 & CaAssnTo1, ...2, ...3. Sounds like this is ok though.
Thanks for all the feed-back

[quoted text clipped - 7 lines]

FORMS AND REPORTS WHEN IN DESIGN VIEW. LET'S TAKE THIS PARAGRAPH
AS AN EXAMPLE.

I agree with your point, but your example doesn't work -- both
paragraphs take up exactly the same amount of space in my newsreader
because, of course, I'm using a fixed-width font (as is proper for
Usenet posts, since there is no formatting).

 

[Pat Hartman]

Actually it's not ok. This is exactly what we were talking about (at least
before we started reminiscing). Whenever you have more than one of
something, you have many and when you have many, you should use a separate
table so you can manage the 1-many relationship properly. The problem with
limiting a set (aside from the fact that first normal form prohibits
repeating groups) is that if you allow too many cases, you waste space and
if you allow too few, you can potentially cause a lot of rework to expand
the set. A more subtle issue, you won't discover until you start to write
code and queries and that is that you'll have to deal with three fields
rather than one. Three is not a terrible number to code around but we see
many posters who end up with dozens and are very unhappy with Access because
it doesn't work like Excel.

Wow, that stired up some opions.
I am creating a corrective action DB and wanted to be able to asign up to
3
(I guess) CA's per incident, so I wanted to make sure I was not causing
myself potiential problems (future code writing) by simply naming these
fields Ca1 & CaAssnTo1, ...2, ...3. Sounds like this is ok though.
Thanks for all the feed-back

I like your rules, Pat. I use very similar rules with some minor
exceptions. All field names are upper case and must include at

[quoted text clipped - 7 lines]

FORMS AND REPORTS WHEN IN DESIGN VIEW. LET'S TAKE THIS PARAGRAPH
AS AN EXAMPLE.

I agree with your point, but your example doesn't work -- both
paragraphs take up exactly the same amount of space in my newsreader
because, of course, I'm using a fixed-width font (as is proper for
Usenet posts, since there is no formatting).

 

[Tony Toews [MVP]]

Wow, that stired up some opions.

I am creating a corrective action DB and wanted to be able to asign up to 3
(I guess) CA's per incident, so I wanted to make sure I was not causing
myself potiential problems (future code writing) by simply naming these
fields Ca1 & CaAssnTo1, ...2, ...3. Sounds like this is ok though.

I'm with Pat. Don't do that. Create another child table and allow
however many corrective actions are required. What if four or five
are required.

Users will swear to you "We only ever, ever do one or two. Never
more than two." And after a month "Oh well, yes, that's a special
case though. Never happen again."

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/