HELP: Access Relationship Question

[hoihtah]

I have a rather simple question on MS ACCESS.

i have

tables
Rep (repID, name, stateID)
State (stateID, state)

i would like to set up a relationship where each rep can have more than one stateID assigned to them.

for example...

repID : 007
name : James Bond
stateID : NY, CA, MA, LA

stateID would have all 50 states in number
state would have all 50 states' names.

thanx for help in advance.

 

[hoihtah]

basically, i'm asking if i can put multiple values in a single field.

thanx...

it's kinda urgent...

 

[EagleKeeper]

Best bet is to create a third table that contains RepID and StateID.

Remove the StateID from the Rep table.

Now you can create multiple relationships that show the Reps for each state or what staes a rep can cover.

 

[hoihtah]

Originally posted by: EagleKeeper
Best bet is to create a third table that contains RepID and StateID.

Remove the StateID from the Rep table.

Now you can create multiple relationships that show the Reps for each state or what staes a rep can cover.

thanx for your reply.

let me see if i'm understanding you correctly.

Set up the tables as such.

rep [repID, name]
(007, James Bond)

state [stateID, state]
(1, NY)
(2, CA)
(3, MA)
(4, LA)

repstate [repstateID, repID, stateID]
(1,007,1)
(2,007,2)
(3,007,3)
(4,007,4)

is that what you mean?

perhaps, i'm a bit slow understanding this,
but this would still seem to have the same problem.
or perhaps a greater problem when it becomes more than just state.

say i have rep specialty skills table
skill [skillID, skill]
(1, knife)
(2, pistol)
(3, tank)

 

[hoihtah]

basically, what i'm trying to do is to have these tables listed as above...

and have each rep be able to multi-select their state and skills.

is it possible in access to multi-select and fit it into a field?
so i won't have to list out all the possibilities of combinations in another table?

 

[manly]

EagleKeeper suggested the correct solution. I believe the 3rd table is commonly called a relationship table. You should read up on database normalization to understand the underlying theory. To produce the tuples you want, you just need to do a join.

In relational databases, an attribute cannot store multiple values.

If you really wanted to, you could store a String such as 'CA,MA,FL' and manually parse that attribute but it's considered very bad form.

 

[hoihtah]

thanx for that info.

is there a particular site that you'd recommend for this info on database normalization?

 

[hoihtah]

i've just read o'reilly book on access chp4
and i think i have a good understanding of what normalization is.

i didn't know what it was called, but setting up tables to better organize the database is something that i understand.

having said that,
i'm still struggling to see how this can be implemented into my table set.

i'm leaning on not using multiple values to a field method... as i'll have to parse it afterwards.

but now what?

i can set up the tables eaglekeeper mentioned...
but how would the relationships work?

thanx you guys for helping me out.

 

[hoihtah]

in case others are interested...
here's what i found in google.

so... subform, it'll have to be.

------------------------------------------------------------------------

You real problem here is that you are being given code to let you select
options from a list box, and then send the results to a single field. This
is a bad idea, and when you finally get a working solution, you will simply
have been given enough rope you hang your self with.

I am sure that you know that this is a *VERY BAD DESIGN*. In addition, while
some of the code might help you move selected things from a list box to the
text box, when you go *BACK* to that record, the list box will NOT have
those values from the text box selected.

In other words, to really make a nice user interface, you would now need to
grab the values from the text box, parse out each individual entry, and then
send the results back into the list box. Those selected entries *should*
show in the list box. Since you are using code to move in one direction, you
will also need to move back in the other direction.

Anything that does not work as above is going to cause sheer and utter
confusion on the user part. What happens when they go back, and see entries
in the listbox are not now selected. This means they are likely to select
them again. When they select them again, and you run your code the existing
entries in the text box will be lost. You could *always* add selected items
from the list box to your text box field, but then if you keep the existing
entries it will be very easy to wind up with duplicates in you text box.

We are of course assuming that your list box is UN-bound. Since you are
allowing more than one entry to be selected, then the list box must be
un-bound.

You would be much better to create a small sub form, and placed a combo that
allows the user to select a value from a drop down list. They would have to
move down to the next line to add another selection. This is better, since
the user can then add/remove entries to the list in a much easier fashion.

You also eliminate any problems when the users navigate from record to
record. If you use your list box idea, then as mentioned moving from record
to record will requite code to update this list box as you move from record
to record. This is due to the fact that the list box is un-bound, and will
not change from record to record. This will be confusing to the user.

Thus, unless you also have code to move the data from the field to the list
box, then you will confuse your user. In addition, you can see that quite a
bit of code here is required to do what you want. In addition, this code is
fairly advanced.

The addition of a sub form, and table to hold multiple values will also make
searching, and any kind of reporting a million ties easier. Especially any
kind of counting or reporting. You can use the standard access tools to
report on this kind of data. With all your data "stuffed" into a single
field, you now cannot use the standard access reporting tools to work on
this data.

So, by splitting each value from a text box into another relational table,
you solve all of the above problems. Not only that, you don't need code, and
your data will be far more normalized, and thus much more flexible from a
design and maintenance point of view.
-------------------------------------------------------------

 

[EagleKeeper]

Sorry it took so long, time zone allows me to wake up later that the rest of you😛

Originally posted by: hoihtah

Originally posted by: EagleKeeper
Best bet is to create a third table that contains RepID and StateID.

Remove the StateID from the Rep table.

Now you can create multiple relationships that show the Reps for each state or what staes a rep can cover.

thanx for your reply.

let me see if i'm understanding you correctly.

Set up the tables as such.

rep [repID, name]
(007, James Bond)

state [stateID, state]
(1, NY)
(2, CA)
(3, MA)
(4, LA)

repstate [repstateID, repID, stateID]
(1,007,1)
(2,007,2)
(3,007,3)
(4,007,4)

is that what you mean?

perhaps, i'm a bit slow understanding this,
but this would still seem to have the same problem.
or perhaps a greater problem when it becomes more than just state.

say i have rep specialty skills table
skill [skillID, skill]
(1, knife)
(2, pistol)
(3, tank)

Your repStateID table does not need the initial index.
The fields should only be repID and stateID.
The only reason to keep an index is for counting purposes. Uniqueness can be done by the compbination of the two fields if needed.

To address the rep specialty skills table, the same theory applies.
For each unique combination that you wish to track, create a table that will contain only those combination of fields.
This table would have the repIDand skillID as fields. This will allow you to find out what skills a rep has, or what reps have a given skill.