tables not relating properly

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

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 spreadsheet than a well-normalized relational
database.

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

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" wrote in message
...
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

On 24 Aug, 00:13, "Jeff Boyce" wrote:
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

?!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 spreadsheet than a well-normalized relational
database.

Giving the OP the benefit of the doubt, I'd say it sounds like
subclassing to me, so it may indeed be normalized. Consider the
following description of table structure (3NF at least, I think) based
on your example:

CREATE TABLE Persons (
FirstName VARCHAR(35) NOT NULL,
LastName VARCHAR(35) NOT NULL,
UNIQUE (FirstName, LastName)
)
;
CREATE TABLE Jeffs (
FirstName VARCHAR(35) NOT NULL,
LastName VARCHAR(35) NOT NULL,
UNIQUE (FirstName, LastName),
FOREIGN KEY (FirstName, LastName)
REFERENCES Persons (FirstName, LastName),
CHECK (FirstName = 'Jeff')
)
;
CREATE TABLE Merediths (
FirstName VARCHAR(35) NOT NULL,
LastName VARCHAR(35) NOT NULL,
UNIQUE (FirstName, LastName),
FOREIGN KEY (FirstName, LastName)
REFERENCES Persons (FirstName, LastName),
CHECK (FirstName = 'Meredith')
)
;
CREATE TABLE Williams (
FirstName VARCHAR(35) NOT NULL,
LastName VARCHAR(35) NOT NULL,
UNIQUE (FirstName, LastName),
FOREIGN KEY (FirstName, LastName)
REFERENCES Persons (FirstName, LastName),
CHECK (FirstName = 'William')
)
;

Jamie.

--

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 spreadsheet than a well-normalized relational
database.

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

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" wrote in message
...
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

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 spreadsheet, but Access is a relational
database. You won't get the best use of Access' features and functions if
you try to feed it 'sheet data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"MeredithS" wrote in message
...
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 spreadsheet than a well-normalized relational
database.

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

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" wrote in message
...
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

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 spreadsheet, but Access is a relational
database. You won't get the best use of Access' features and functions if
you try to feed it 'sheet data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"MeredithS" wrote in message
...
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 spreadsheet than a well-normalized relational
database.

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

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" wrote in message
...
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

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" wrote in message
...
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 spreadsheet, but Access is a
relational
database. You won't get the best use of Access' features and functions
if
you try to feed it 'sheet data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"MeredithS" wrote in message
...
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 spreadsheet than a well-normalized relational
database.

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

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" wrote in message
...
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

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" wrote in message
...
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 spreadsheet, but Access is a
relational
database. You won't get the best use of Access' features and functions
if
you try to feed it 'sheet data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"MeredithS" wrote in message
...
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 spreadsheet than a well-normalized relational
database.

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

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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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 spreadsheet, but Access is a
relational
database. You won't get the best use of Access' features and
functions
if
you try to feed it 'sheet data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"MeredithS" wrote in message
...
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 spreadsheet than a well-normalized
relational
database.

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

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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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 spreadsheet, but Access is a
relational
database. You won't get the best use of Access' features and
functions
if
you try to feed it 'sheet data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"MeredithS" wrote in message
...
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 spreadsheet than a well-normalized
relational
database.

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

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" wrote in message
...
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