By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,339 Members | 1,973 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,339 IT Pros & Developers. It's quick & easy.

Codes and masking

P: n/a
Hi Newsgroup
Does anyone here care to share their thoughts/experiences with using
masking? I am storing categories of books which libraries stock, so that I
might have codes:

015-000-000 = Languages
015-001-000 = European languages
015-001-001 = English
015-001-002 = German
015-001-003 = French
....
etc
This lets me use criteria such as LIKE "015-001-???" when I want to find
libraries which have books on any European language.

Typically, I use non-meaningful autonumber ID columns for a primary key so I
might have:

tblLibraries: LibID, LibName, LibAddress, etc
tblSubjects: SubID, SubName, SubMask
tblLibrarySubjects: LibID, SubID (the junction table)

However, if I simply store the SubID in the junction table, I will always
need to have a query using a join from the subjects table if I need LIKE
"015-001-???". I could use this mask as the primary key in the subjects
table and save myself a join. Another benefit might be that by enforcing
cascade updates, re-classifying a subject would be easier - eg I could
change its place in the hierarchy. What do you think? Also, should the
junction table have its own PK if the 2 primary keys from the other tables
already form a unique index?

Thanks for any thoughts
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
For the lookup table of languages, there is no need to use an AutoNumber,
because this key field uniquely describes the record. A Text primary key is
fine.

I would suggest that you omit the dashes from the key and store only the
numbers, because:
- the dashes are not part of the data, and
- Access 2000 and later handle the dashes inconsistently.
You can use an Input mask to display the field with dashes.

Alternatively, use 3 separte Number fields so the data is atomic and may be
more efficient. A multi-field key works fine, though this might get
cumbersome if you then needed to use it also into further generations of
grandchild relations.

BTW, it's a matter of style, but I often use a 24-char text field as the
primary key for lookup fields such as categories. Not only is the natural
key simpler, but you can leave the bound key visible, which circumvents the
problems associated with filtering the RowSource of combos in continuous
forms.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Stefan Kowalski" <a@b.com> wrote in message
news:ct**********@hercules.btinternet.com...
Hi Newsgroup
Does anyone here care to share their thoughts/experiences with using
masking? I am storing categories of books which libraries stock, so that
I might have codes:

015-000-000 = Languages
015-001-000 = European languages
015-001-001 = English
015-001-002 = German
015-001-003 = French
...
etc
This lets me use criteria such as LIKE "015-001-???" when I want to find
libraries which have books on any European language.

Typically, I use non-meaningful autonumber ID columns for a primary key so
I might have:

tblLibraries: LibID, LibName, LibAddress, etc
tblSubjects: SubID, SubName, SubMask
tblLibrarySubjects: LibID, SubID (the junction table)

However, if I simply store the SubID in the junction table, I will always
need to have a query using a join from the subjects table if I need LIKE
"015-001-???". I could use this mask as the primary key in the subjects
table and save myself a join. Another benefit might be that by enforcing
cascade updates, re-classifying a subject would be easier - eg I could
change its place in the hierarchy. What do you think? Also, should the
junction table have its own PK if the 2 primary keys from the other tables
already form a unique index?

Thanks for any thoughts

Nov 13 '05 #2

P: n/a
Allen Browne wrote:
- Access 2000 and later handle the dashes inconsistently.


Hi Allen...could you elaborate on this? I usually don't care when using
dashes in SocialSecurity number, maybe phone numbers since I am not
worried about diskspace. Besides, both are text fields. But if there
is a problem, I wouldn't want to make my life work harder.
Nov 13 '05 #3

P: n/a
See:
ACC2000: Query Returns no Records with an Indexed Field That Contains
Dashes
at:
http://support.microsoft.com/default...b;en-us;271661

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Salad" <oi*@vinegar.com> wrote in message
news:3D****************@newsread3.news.pas.earthli nk.net...
Allen Browne wrote:
- Access 2000 and later handle the dashes inconsistently.


Hi Allen...could you elaborate on this? I usually don't care when using
dashes in SocialSecurity number, maybe phone numbers since I am not
worried about diskspace. Besides, both are text fields. But if there is
a problem, I wouldn't want to make my life work harder.

Nov 13 '05 #4

P: n/a
Allen Browne wrote:
See:
ACC2000: Query Returns no Records with an Indexed Field That Contains
Dashes
at:
http://support.microsoft.com/default...b;en-us;271661

Thanks. Something to keep in mind.
Nov 13 '05 #5

P: n/a

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...
See:
ACC2000: Query Returns no Records with an Indexed Field That Contains
Dashes
at:
http://support.microsoft.com/default...b;en-us;271661

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Salad" <oi*@vinegar.com> wrote in message
news:3D****************@newsread3.news.pas.earthli nk.net...
Allen Browne wrote:
- Access 2000 and later handle the dashes inconsistently.


Hi Allen...could you elaborate on this? I usually don't care when using
dashes in SocialSecurity number, maybe phone numbers since I am not
worried about diskspace. Besides, both are text fields. But if there is
a problem, I wouldn't want to make my life work harder.



Hi Allen
I'm replying under this post because your original answer is not showing via
this newsreader, although I did read it via Google. Just to say thanks and
that I will implement your recommendations.


Nov 13 '05 #6

P: n/a
[posted and mailed]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:41***********************@per-qv1-newsreader-01.iinet.net.au
:
See:
ACC2000: Query Returns no Records with an Indexed Field
That Contains
Dashes
at:
http://support.microsoft.com/default...d=kb;en-us;271
661


--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.