Table Relationships; Listbox

A

Allison Kelly

This question probably requires a very simple answer but I am very rusty in
my access/vba and can't think of how to set this up, so any help is greatly
appreciated.

I am setting up a call stats database for our customer service department.
Basically, general characteristics of each call that we receive. Most of the
fields are very general, yes/no, male/female. I have set up combo boxes for
those which are working fine. My problem is that I need a list box set up so
that users can select multiple items (if a participant asked multiple
questions during their call). I know that I am setting up my tables
incorrectly, but I am getting lost from there.

-I think I should have two tables, one main one with my yes and no questions
and then another table (tblReasons) that lists the possible options that a
participant called in, but I am confused about which relationship to use and
how to link it back to the main table. Should I just have the CallID
(primarykey of main table) as the foreign key and the reason as the primary?

-I know that I need to review the rules of defining table rules, and I will
be doing that tonight, but any help is so much appreciated bc I am under some
pressure here.

-Also, with a listbox, I know by using the selected items properties that i
can pull the values out that the user has selected, but all the examples I
have seen so far only print the results, they do not store them anywhere. Is
it possible to store the results in a table so that I can manipulate the data?

Thanks in advance!
 
D

Dirk Goldgar

Allison Kelly said:
This question probably requires a very simple answer but I am very
rusty in my access/vba and can't think of how to set this up, so any
help is greatly appreciated.

I am setting up a call stats database for our customer service
department. Basically, general characteristics of each call that we
receive. Most of the fields are very general, yes/no, male/female. I
have set up combo boxes for those which are working fine. My problem
is that I need a list box set up so that users can select multiple
items (if a participant asked multiple questions during their call).
I know that I am setting up my tables incorrectly, but I am getting
lost from there.

-I think I should have two tables, one main one with my yes and no
questions and then another table (tblReasons) that lists the possible
options that a participant called in, but I am confused about which
relationship to use and how to link it back to the main table. Should
I just have the CallID (primarykey of main table) as the foreign key
and the reason as the primary?

-I know that I need to review the rules of defining table rules, and
I will be doing that tonight, but any help is so much appreciated bc
I am under some pressure here.

-Also, with a listbox, I know by using the selected items properties
that i can pull the values out that the user has selected, but all
the examples I have seen so far only print the results, they do not
store them anywhere. Is it possible to store the results in a table
so that I can manipulate the data?

Thanks in advance!
Click to expand...

I suppose you have tblCalls (pk CallID) and tblReasons (pk ReasonID)?
You need a third table, tblCallsReasons, with a compound primary key
composed of CallID and ReasonID. You probably don't need any other
fields in this table.

You could easily have a subform bound to tblCallsReasons (or a query
that joins tblCallsReasons to tblReasons to pick up the reason
description). However, the only way to maintain the subform without
code would be to pick the ReasonID from a combo box on the subform.
That's not as fast as clicking in a list box.

You could have a single-select list box of reasons on the main form, set
up with a bit of code so that each time the user clicks on a reason in
the list box, a record is added to tblCallsReasons for that ReasonID and
the current CallID, and then the list box would be cleared for another
click and the subform would be requeried to show the record that was
added. That would be a low-code solution.

It is possible to set up a high-code solution with a multi-select list
box and no subform, where the list box shows as selected those reasons
that are in tblCallsReasons for this call, and selecting or unselecting
an item in the list box updates tblCallsReasons. That seems to be what
you're asking to do, but it does require more code. Here's a link to an
example I posted a while ago:


http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&[email protected]&rnum=4
 
A

Allison Kelly

Ok I think I may have fixed the relationships. Maybe.

I have my main table
Primary Key: CallID (autonumber)
Lots of yes/no questions
CallReason (set up as text)

then the Reason table
Primary Key: CallReason
CallID

I have a one to many relationship with the one being the callreason in my
reason table and the many being the callreason in the main table.

Any luck with that?
 
Top