Re: tables not relating properly

Tech-Archive recommends: Fix windows errors by optimizing your registry

Meredith, I need to leave for the day, and will not have time to give an
adequate answer to your question before tomorrow, but this link may give you
some ideas how to go about using one combo box to filter another:
http://www.mvps.org/access/forms/frm0028.htm

If nobody else jumps in before tomorrow I will try to provide some more
details then.


"MeredithS" <MeredithS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:59809DF5-8AF5-401E-904D-77F03FC269FE@xxxxxxxxxxxxxxxx
Thank you, Bruce. This seems like a reasonable approach. I haven't created
a
combo box that would allow choices by a particular field? Not sure what
that
is, even ... I may take the simple way out and just put the whole list
into
one box as you also suggested. I'd still include the categories in the
underyling table for future reporting/marketing purposes, of course.

If you have time, could you point me in the right direction for creating a
multi-option combo box? Maybe I do know and I don't realize I know. Ha!

Meredith


"BruceM" wrote:

One possibility is to have a Referral table with a field for
ReferralType.
In ReferralType you could limit the choice to General, Agency, or
Specific.
The user could select one of those categories from a combo box, which
would
limit the list to just the Referrals in that category. The combo box
could
have another category for All. If this approach sounds useful, here is
some
information about adding (All) as a choice in the combo box. If the user
knows the category, they can have a filtered list; if not, they choose
(All)
and end up with the full list.
There are techniques for managing truly unwieldy lists consisting of
thousands of records, but for just a few hundred you should do OK just by
setting the combo box Auto Expand property to Yes. If the user types
"M"
they will see the beginning of the entries that start with "M". Typing
"Me"
may bring them to "Melinda", and then it is a simple matter to scroll
down
to "Meredith". Or they can keep typing until they see the entry they are
seeking.
The real trick here, it seems to me, is data integrity in the
ReferralType
list. You need to guard against the duplicate entries "Child Protective
Services", "CPS", "C.P.S.", "Child Protective Svcs.", and so forth. You
may
need to enter more information into the referral table than you think may
be
needed, and to check for duplication in the address or phone number
fields,
or an ID number, or probably some combination of fields in order to
assure
there is a single entry for Child Protective Services. A monthly review
of
the full list will help keep things in order. A few hundred choices
won't
be a problem, but alternate names or categories for the same entity could
be.

"MeredithS" <MeredithS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EB220F12-5207-4783-9F67-65D062B51E20@xxxxxxxxxxxxxxxx
I understand what you're saying about the importance of the early design
and
setting relationships, which is why I haven't gone ahead and created
the
tables or the form until I understand the underpinnings. But, part of
designing the database has to take into account its ultimate purpose,
so
I'm
also thinking about that -- and I don't think cascading boxes will work
because they require the user to know which category an item belongs to
upfront. That still leaves me with the dilemma of how to make it easy
on
the
user to put in/choose a referral source and also design the tables with
an
eye toward being able to query them intelligently later --

Thanks,

Meredith


"Jeff Boyce" wrote:

Meredith

So, from an ease-of-use standpoint, a user could select CPS and never
know
what category and subcategory CPS belongs to. That makes sense. But
what
about the reverse situation, when the way to limit the possible
"agencies"
is driven by a choice of categories.

One approach might be to have a pair of "cascading combo boxes", where
the
user picks a category ("Agency") first, which then limits the choices
in
a
second combobox to only agencies (e.g., CPS, American Cancer Society,
...).

With Access, to get the best use of the relationally-oriented features
and
functions, you really need to get the data nailed down first. After
you
have "entities" and "relationships" designed, then you can figure out
how
to
use queries, forms, and reports to interact with the users.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"MeredithS" <MeredithS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3C74F8E3-421F-4DCB-8EAB-47AFC670F2E7@xxxxxxxxxxxxxxxx
Thanks, Jeff -- I'm still thinking about this, but what I hear you
suggesting
is similar to what I'm wanting to do. It's logical and easy to do as
you
suggest, but what I'm uncertain of is how to 'replicate' this
structure
in
an
input/intake form. The intake person will hear a specific source
name,
e.g.,
Child Protective Services, and as you said, they don't need to know
that
CPS
is in a particular category or sub-category. But, there are probably
200+
such specific sources, and it seems unwieldy to me to put them all
into
a
drop-down box on the intake form...

Is that what you're saying, or am I missing something?

Many thanks for your help, Jeff,

Meredith


"Jeff Boyce" wrote:

Meredith

Let me see if I can paraphrase your data (which seems to only be
focused
on
the referral aspects at the moment):

* You have Patients
* Patients are Referred
* A Patient Referral comes from a single Source (e.g., John Doe,
Child
Protective Services, ...)
* A Source can be categorized (e.g., General, Agency, Legal,
...)
* A Source Category can be sub-categorized (e.g., Friends,
Internet,
...)

If this is a fair paraphrasing, I'll suggest that you need tables
along
the
lines of the "*"s above. Using a separate table for each referral
source
(type), and using (essentially) equivalent fields in each of those
tables
will cause both you and Access considerable headaches. Using
separate
"duplicate" table structures and embedding data in the table names
is
what
you'd need to do if you were using a spread***, but Access is a
relational
database. You won't get the best use of Access' features and
functions
if
you try to feed it '*** data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"MeredithS" <MeredithS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FADA0925-105C-46DB-B319-10EA5375D911@xxxxxxxxxxxxxxxx
Thanks for your comments -- you're right; I didn't post the table
descriptions very clearly. The goal is a form for Admissions in
my
hospital;
they need to post the referral source amond other things at new
patient
intake, and I wanted to divide that into 3 large categories for
simplicity
and to avoid the intake person having to deal with a 200+ entry
list.
So,
I
set up:

Tbl 1 General Referrals (approx 30; these are 'types' such as
Family/Friends, Internet, etc.)
Resource ID (category as in: Hospital, Legal, etc) All are
Resource
ID
#1
Referral Source ID (unique)
Referral Source Name

Tbl 2 Agencies (approx. 50; these are specific entities such as
Baptist
Children's Home)
Resource ID -- all are Resource ID #2
Referral Source ID
Agency Name

Tbl 3 Specific Resources (approx. 50; these are grouped by types
as
in
5
legal entities, 7 hospitals/ERs, 10 MHMRs, etc. -- mixed Resource
ID
#s)
Resource ID
Referral Source ID
Specific Resource Name

Tbl 4 Resource ID Table (this is intended to be the junction
table,
linking
the others)
Resource ID
Resource Category Name

If there's a better strategy than what I've thought up, please
suggest
it --
I appreciate your help.

Meredith


"Jeff Boyce" wrote:

Meredith

?!Are you saying that you have tables named after your
Resources?
Like a
Meredith table and a Jeff table and a William table?

That sounds more like a spread*** than a well-normalized
relational
database.

Before you proceed, take a look at "normalization" -- Access
isn't
a
spread***.

Consider posting back with a description of your table structure
more
like:

tblPerson
PersonID
FirstName
LastName

tblClub
ClubID
ClubName

trelMembership
PersonID
ClubID
DateOfMembership

(this is just a made up example to show a way you can post your
table
structure via email.)

Regards

Jeff Boyce
Microsoft Office/Access MVP


"MeredithS" <MeredithS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:5F1414DE-3C1B-421B-9587-DC83E525F3F7@xxxxxxxxxxxxxxxx
3 tables of referral sources and one 'join' table with an ID
field
common
to
all 3 (Resource ID). In Table 1, all items are Resource ID #1;
in
Table
2,
all items are Resource ID #3; in Table 3, the items are all
other
Resource
ID
#s but ot #1 or #3. I've set the relationships to be: Table 4
(the
Resource
ID join table) has a one-to-many relationship with ??? all the
tables
or
only
Table 3. I'm trying to join them all in a query so I can group
them
into a
form, basically in the 3 categories represented by the
tables...This
is
probably unclear, but I'm unable to figure out what I'm doing
wrong.
I first thought that Table 4 related to Tables 1 and 2 as a
one-to-one,
but
I can't seem to set that up in the Relationships window and
now
I'm
very
confused. I bet I'm totally off base here -- I've relied to
date
on
trial-and-error in my relationship setting and reading about
it
isn't
helping. Suggestion(s)?
Many thanks --
Meredith















.

Quantcast