Codes and masking

[Stefan Kowalski]

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

[Allen Browne]

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" <[e-mail]> 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

[Salad]

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.

[Allen Browne]

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.

[Salad]

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.

[Stefan Kowalski]

"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.

[Bob Quintal]

[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.