substring queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, all

I am designing a database and what I want is a "role" field that has a code in it made up of different single letter codes. For example, A for artist, S for staff and so on. These are not mutually exclusive codes and order doesn't matter, so it is possible, for example, to be both an artist and a staff member and have a code SA == AS. At the moment, we have half a dozen or such codes, but this is likely to increase

Creating such a field is easy. What I want to be able to do is construct queries on substrings. For example, a query on all persons such that Role = A or a query on Role = A and Role = B. It would also be useful to negate this, i.e, all those whose Role = A and Role != B

Thanks in advance for the help

Glenn
 
Glenn

You are describing a many-to-many relationship (a person could have none or
one or many "roles", and a given "role" could belong to none, one, or many
persons). While you certainly can do that with a single field in Access,
you will certainly be creating headaches for yourself down the road, when
you want to query the data.

To get better use out of the relational database (Access), and to make your
life easier in the long run, create a resolver/relation/join table to handle
the many-to-many relationship. That table could have as few as two fields:
PersonID and RoleID (note that this will require you to create a lookup
table of Roles).

On a related note, by using a single letter as a "code", rather than an
arbitrary, meaningless ID value (take a look at Autonumber data types, for
example), you will create additional headaches for yourself and the users of
your system. For the moment, you only have a half dozen codes, but as you
point out, there could be others. What code would you use, and how would
you/your users remember, if you get another role, say "Specialist", and "S"
(staff) is already being used?
 
Thanks, Jeff, for the quick response.

It seems to me that what you are describing looks like this

Table: Peopl
P.ID //internally assigned number, primary ke


Table: Role
R.ID //autonumbe
R.RoleNam


Table: RoleResolv
P.ID //foreign ke
R.I


And then a query would take the form of all P.ID in RoleResolve s.t. R.ID = n, where n is the autonumber corresponding to the RoleName (artist, staff, etc.) that we are interested in

Does this seem right to you

Thanks

Glen

P.S. In answer to your question below about Specialist, one could always use "s" instead of "S", though this doesn't scale beyond two roles with the same first letter. Conversely, you can start using second letters of roles, numbers and so on. There are 26*2 + 10 = 62 possible codes, way more than we need. On the third hand, I agree this is clunky and hard to read, which is why I'm asking about the tables above

----- Jeff Boyce wrote: ----

Glen

You are describing a many-to-many relationship (a person could have none o
one or many "roles", and a given "role" could belong to none, one, or man
persons). While you certainly can do that with a single field in Access
you will certainly be creating headaches for yourself down the road, whe
you want to query the data

To get better use out of the relational database (Access), and to make you
life easier in the long run, create a resolver/relation/join table to handl
the many-to-many relationship. That table could have as few as two fields
PersonID and RoleID (note that this will require you to create a looku
table of Roles)

On a related note, by using a single letter as a "code", rather than a
arbitrary, meaningless ID value (take a look at Autonumber data types, fo
example), you will create additional headaches for yourself and the users o
your system. For the moment, you only have a half dozen codes, but as yo
point out, there could be others. What code would you use, and how woul
you/your users remember, if you get another role, say "Specialist", and "S
(staff) is already being used
 
Back
Top