tables not relating properly

[Guest]

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

 

[Jeff Boyce]

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

 

[Jamie Collins]

?!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.

--

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

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

 

[BruceM]

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.

 

[Guest]

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]

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.

 

[BruceM]

A combo box list (Row Source) may be either a Value List
("Excellent","Good","Fair","Poor") or a Table/Query. Assuming that
ReferralType is a field in a referral table (tblReferral), you could create
a query (qryRefType) based on tblReferral, with just the field ReferralType.
Right click the top of the query in design view (above the grid), and set
Unique Values to Yes.
A query uses code known as SQL behind the scenes. You can see a query's SQL
by opening a query and clicking View > SQL. You can also use SQL directly
as a Row Source. Here is an excerpt from this web page:
http://www.mvps.org/access/forms/frm0043.htm
I have adapted it to your situation.

SELECT DISTINCT ReferralType FROM tblReferral UNION Select "(All)" as Bogus
FROM tblReferral ORDER BY ReferralType;

There are no line breaks, although some may appear in your newsreader
window.

This should give you a list of ReferralTypes, with (All) at the top of the
list. The DISTINCT in the code means that even though a ReferralType
appears in many records in tblReferral, it appears only once in the list.

The combo box column count would be 1, and the column width about 1". You
can set the Default Value of the combo box to "(All)" so that (All) appears
even if no choice is made.

You could use a Value List for the combo box Row Source, with "(All)" as one
of the choices, but by using the SQL you can change or add the list of
Referral Types without changing the code.

The Row Source for the combo box from which you select the actual referral
could be something like:

SELECT tblReferral.Referral
FROM tblReferral WHERE
(((IIf([Forms]![frmReferral]![cboReferral]<>"All","",
[Referral] = [Forms]![frmReferral]![cboReferral]))=True))
ORDER BY tblReferral.Referral;

I have tested this code by adapting it from one of my own projects. I
cannot say for sure that it will work in your project since I don't know all
of the details of what you have done.

One of my assumptions is that tblReferral contains fields for Referral (CPS,
etc.) and ReferralType, along with whatever else you need.

For a two-column combo box (with both columns visible), use a two-column Row
Source query. Set the column count to 2 and the column widths to something
like 1";1"
However, I don't think that is what you really need here.

 

[Guest]

Hi, Bruce. I looked at the example of layered combo boxes that you provided
yesterday and understood it in theory but was a little confused about how to
'nest' them -- I will keep thinking/trying/re-reading.

As with what you've provided below, which I am sure is what I need. I'll
print it out, try it out (in my draft db, of course!) and it should work.
You're correct; the tables I've created so far for the referral sources --
which need to be combined into one -- include a field for ReferralType,
ReferralSourceName, and ReferralSourceID. Might be overkill with the
ReferralSourceID, but I don't think so.

I don't want to take up any more of your time, but you've been
extraordinarily helpful ... No one else where I work has any knowledge of
Access, so I'm swimming alone. Enjoy it, though!

Meredith

A combo box list (Row Source) may be either a Value List
("Excellent","Good","Fair","Poor") or a Table/Query. Assuming that
ReferralType is a field in a referral table (tblReferral), you could create
a query (qryRefType) based on tblReferral, with just the field ReferralType.
Right click the top of the query in design view (above the grid), and set
Unique Values to Yes.
A query uses code known as SQL behind the scenes. You can see a query's SQL
by opening a query and clicking View > SQL. You can also use SQL directly
as a Row Source. Here is an excerpt from this web page:
http://www.mvps.org/access/forms/frm0043.htm
I have adapted it to your situation.

SELECT DISTINCT ReferralType FROM tblReferral UNION Select "(All)" as Bogus
FROM tblReferral ORDER BY ReferralType;

There are no line breaks, although some may appear in your newsreader
window.

This should give you a list of ReferralTypes, with (All) at the top of the
list. The DISTINCT in the code means that even though a ReferralType
appears in many records in tblReferral, it appears only once in the list.

The combo box column count would be 1, and the column width about 1". You
can set the Default Value of the combo box to "(All)" so that (All) appears
even if no choice is made.

You could use a Value List for the combo box Row Source, with "(All)" as one
of the choices, but by using the SQL you can change or add the list of
Referral Types without changing the code.

The Row Source for the combo box from which you select the actual referral
could be something like:

SELECT tblReferral.Referral
FROM tblReferral WHERE
(((IIf([Forms]![frmReferral]![cboReferral]<>"All","",
[Referral] = [Forms]![frmReferral]![cboReferral]))=True))
ORDER BY tblReferral.Referral;

I have tested this code by adapting it from one of my own projects. I
cannot say for sure that it will work in your project since I don't know all
of the details of what you have done.

One of my assumptions is that tblReferral contains fields for Referral (CPS,
etc.) and ReferralType, along with whatever else you need.

For a two-column combo box (with both columns visible), use a two-column Row
Source query. Set the column count to 2 and the column widths to something
like 1";1"
However, I don't think that is what you really need here.

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]

I was pretty much on my own too, until I discovered this group. If
ReferralSourceID is an autonumber (or number) field that has no meaning to
the user, you may not need it, but it won't do any harm. I tend to use such
fields for relationships, because they are not subject to change, but agency
names (and other referrals, no doubt) are subject to change. If a field
used in table relationships is changed, the corresponding field in related
tables must also be changed. With a meaningless field such as autonumber
the problem is far less likely to occur. However, data integrity is a
different matter from a unique record. An autonumber can be very useful in
relationships. It can also assure the record is different from all other
records, but the difference is not meaningful. It doesn't stop you from
entering the same information twice, to use one example. More subtly, it
doesn't stop you from using variant spellings for the same referral. You
may want to use few fields such as MailingAddress or Phone or whatever is
available to help assure you don't use variant spellings such as "CPS" and
"C.P.S." for the same referral. Once you have established what those fields
are you can disallow duplicates, or take other steps such as asking the user
to verify that the Referral being entered into the Referral table (by way of
a form, of course) is unique.

Hi, Bruce. I looked at the example of layered combo boxes that you
provided
yesterday and understood it in theory but was a little confused about how
to
'nest' them -- I will keep thinking/trying/re-reading.

As with what you've provided below, which I am sure is what I need. I'll
print it out, try it out (in my draft db, of course!) and it should work.
You're correct; the tables I've created so far for the referral sources --
which need to be combined into one -- include a field for ReferralType,
ReferralSourceName, and ReferralSourceID. Might be overkill with the
ReferralSourceID, but I don't think so.

I don't want to take up any more of your time, but you've been
extraordinarily helpful ... No one else where I work has any knowledge of
Access, so I'm swimming alone. Enjoy it, though!

Meredith

A combo box list (Row Source) may be either a Value List
("Excellent","Good","Fair","Poor") or a Table/Query. Assuming that
ReferralType is a field in a referral table (tblReferral), you could
create
a query (qryRefType) based on tblReferral, with just the field
ReferralType.
Right click the top of the query in design view (above the grid), and set
Unique Values to Yes.
A query uses code known as SQL behind the scenes. You can see a query's
SQL
by opening a query and clicking View > SQL. You can also use SQL
directly
as a Row Source. Here is an excerpt from this web page:
http://www.mvps.org/access/forms/frm0043.htm
I have adapted it to your situation.

SELECT DISTINCT ReferralType FROM tblReferral UNION Select "(All)" as
Bogus
FROM tblReferral ORDER BY ReferralType;

There are no line breaks, although some may appear in your newsreader
window.

This should give you a list of ReferralTypes, with (All) at the top of
the
list. The DISTINCT in the code means that even though a ReferralType
appears in many records in tblReferral, it appears only once in the list.

The combo box column count would be 1, and the column width about 1".
You
can set the Default Value of the combo box to "(All)" so that (All)
appears
even if no choice is made.

You could use a Value List for the combo box Row Source, with "(All)" as
one
of the choices, but by using the SQL you can change or add the list of
Referral Types without changing the code.

The Row Source for the combo box from which you select the actual
referral
could be something like:

SELECT tblReferral.Referral
FROM tblReferral WHERE
(((IIf([Forms]![frmReferral]![cboReferral]<>"All","",
[Referral] = [Forms]![frmReferral]![cboReferral]))=True))
ORDER BY tblReferral.Referral;

I have tested this code by adapting it from one of my own projects. I
cannot say for sure that it will work in your project since I don't know
all
of the details of what you have done.

One of my assumptions is that tblReferral contains fields for Referral
(CPS,
etc.) and ReferralType, along with whatever else you need.

For a two-column combo box (with both columns visible), use a two-column
Row
Source query. Set the column count to 2 and the column widths to
something
like 1";1"
However, I don't think that is what you really need here.

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


:

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.

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


:

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

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


:

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

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.

 

[Guest]

Hi, Bruce -- I'm working to implement your suggested combo boxes w/criteria
but am having some coding problems, apparently -- Can you help with that? If
so, what should I post?? The 2nd combo box is appearing as a parameter query
for the 1st and not picking up the values ...

Thanks,

Meredith

A combo box list (Row Source) may be either a Value List
("Excellent","Good","Fair","Poor") or a Table/Query. Assuming that
ReferralType is a field in a referral table (tblReferral), you could create
a query (qryRefType) based on tblReferral, with just the field ReferralType.
Right click the top of the query in design view (above the grid), and set
Unique Values to Yes.
A query uses code known as SQL behind the scenes. You can see a query's SQL
by opening a query and clicking View > SQL. You can also use SQL directly
as a Row Source. Here is an excerpt from this web page:
http://www.mvps.org/access/forms/frm0043.htm
I have adapted it to your situation.

SELECT DISTINCT ReferralType FROM tblReferral UNION Select "(All)" as Bogus
FROM tblReferral ORDER BY ReferralType;

There are no line breaks, although some may appear in your newsreader
window.

This should give you a list of ReferralTypes, with (All) at the top of the
list. The DISTINCT in the code means that even though a ReferralType
appears in many records in tblReferral, it appears only once in the list.

The combo box column count would be 1, and the column width about 1". You
can set the Default Value of the combo box to "(All)" so that (All) appears
even if no choice is made.

You could use a Value List for the combo box Row Source, with "(All)" as one
of the choices, but by using the SQL you can change or add the list of
Referral Types without changing the code.

The Row Source for the combo box from which you select the actual referral
could be something like:

SELECT tblReferral.Referral
FROM tblReferral WHERE
(((IIf([Forms]![frmReferral]![cboReferral]<>"All","",
[Referral] = [Forms]![frmReferral]![cboReferral]))=True))
ORDER BY tblReferral.Referral;

I have tested this code by adapting it from one of my own projects. I
cannot say for sure that it will work in your project since I don't know all
of the details of what you have done.

One of my assumptions is that tblReferral contains fields for Referral (CPS,
etc.) and ReferralType, along with whatever else you need.

For a two-column combo box (with both columns visible), use a two-column Row
Source query. Set the column count to 2 and the column widths to something
like 1";1"
However, I don't think that is what you really need here.

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]

What is the Row Source for the first combo box (the ReferralType one)? Does
it have any After Update code or other code? I'm not sure where you are in
your Access vocabulary, so at the risk of saying something you already know,
click the combo box to select it, click View > Properties, and Click the
Event tab. If any of the Events has [Event Procedure], click the three dots
to the right of the row to view the code. The code will go something like:

Private Sub cboReferralType_After Update

{Code here}

End Sub


What is the Row Source for the second combo box (the Referral one)? Again,
any code?

In describing the Row Source, remember that I can't see you database, so you
should describe any tables and fields that appear if if is not obvious by
context. For instance, I can assume that Referral is the name of the
Referral, but other things may not be as clear.

Hi, Bruce -- I'm working to implement your suggested combo boxes
w/criteria
but am having some coding problems, apparently -- Can you help with that?
If
so, what should I post?? The 2nd combo box is appearing as a parameter
query
for the 1st and not picking up the values ...

Thanks,

Meredith

A combo box list (Row Source) may be either a Value List
("Excellent","Good","Fair","Poor") or a Table/Query. Assuming that
ReferralType is a field in a referral table (tblReferral), you could
create
a query (qryRefType) based on tblReferral, with just the field
ReferralType.
Right click the top of the query in design view (above the grid), and set
Unique Values to Yes.
A query uses code known as SQL behind the scenes. You can see a query's
SQL
by opening a query and clicking View > SQL. You can also use SQL
directly
as a Row Source. Here is an excerpt from this web page:
http://www.mvps.org/access/forms/frm0043.htm
I have adapted it to your situation.

SELECT DISTINCT ReferralType FROM tblReferral UNION Select "(All)" as
Bogus
FROM tblReferral ORDER BY ReferralType;

There are no line breaks, although some may appear in your newsreader
window.

This should give you a list of ReferralTypes, with (All) at the top of
the
list. The DISTINCT in the code means that even though a ReferralType
appears in many records in tblReferral, it appears only once in the list.

The combo box column count would be 1, and the column width about 1".
You
can set the Default Value of the combo box to "(All)" so that (All)
appears
even if no choice is made.

You could use a Value List for the combo box Row Source, with "(All)" as
one
of the choices, but by using the SQL you can change or add the list of
Referral Types without changing the code.

The Row Source for the combo box from which you select the actual
referral
could be something like:

SELECT tblReferral.Referral
FROM tblReferral WHERE
(((IIf([Forms]![frmReferral]![cboReferral]<>"All","",
[Referral] = [Forms]![frmReferral]![cboReferral]))=True))
ORDER BY tblReferral.Referral;

I have tested this code by adapting it from one of my own projects. I
cannot say for sure that it will work in your project since I don't know
all
of the details of what you have done.

One of my assumptions is that tblReferral contains fields for Referral
(CPS,
etc.) and ReferralType, along with whatever else you need.

For a two-column combo box (with both columns visible), use a two-column
Row
Source query. Set the column count to 2 and the column widths to
something
like 1";1"
However, I don't think that is what you really need here.

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


:

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.

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


:

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

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


:

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

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.

 

[Guest]

Hi, Bruce -- Here's what I have. I think, at present, the only thing not
working right is the All feature -- I've probably scavenged/guessed enough to
screw up the original code which might have worked exactly as it was. The
difference seemed to be that I had 2 tables I was drawing from and the
Referral one didn't have the Referral Type as a text name, which was what I
wanted to show up in the 1st combo box. So, I was trying to pull from both
and I think that's what got screwed up. If this doesn't work, as I'm doing
it, I can simply add a field to Table #2 and get everything in one table --
probably what I should have done in the 1st place?

2 tables: Resource ID Table (Resource ID, Resource Type) and
ReferralSourcesAll (Resource ID, Referral Source Label)

What is the Row Source for the first combo box (the ReferralType one)?
SELECT DISTINCT [Resource Type] FROM [Resource ID Table] UNION SELECT
"(All)" as Bogus FROM [Resource ID Table]
ORDER BY [Resource Type];



What is the Row Source for the second combo box (the Referral one)? Again,
any code?

SELECT [Resource ID Table].[Resource ID], ReferralSourcesAll.[Referral
Source Label], [Resource ID Table].[Resource Type]
FROM [Resource ID Table] INNER JOIN ReferralSourcesAll ON [Resource ID
Table].[Resource ID] = ReferralSourcesAll.[Resource ID]
WHERE ((([Resource ID Table].[Resource Type])=[Forms]![Intake
Worksheet2]![ReferralType]));



Thanks!!

Meredith

What is the Row Source for the first combo box (the ReferralType one)? Does
it have any After Update code or other code? I'm not sure where you are in
your Access vocabulary, so at the risk of saying something you already know,
click the combo box to select it, click View > Properties, and Click the
Event tab. If any of the Events has [Event Procedure], click the three dots
to the right of the row to view the code. The code will go something like:

Private Sub cboReferralType_After Update

{Code here}

End Sub


What is the Row Source for the second combo box (the Referral one)? Again,
any code?

In describing the Row Source, remember that I can't see you database, so you
should describe any tables and fields that appear if if is not obvious by
context. For instance, I can assume that Referral is the name of the
Referral, but other things may not be as clear.

Hi, Bruce -- I'm working to implement your suggested combo boxes
w/criteria
but am having some coding problems, apparently -- Can you help with that?
If
so, what should I post?? The 2nd combo box is appearing as a parameter
query
for the 1st and not picking up the values ...

Thanks,

Meredith

A combo box list (Row Source) may be either a Value List
("Excellent","Good","Fair","Poor") or a Table/Query. Assuming that
ReferralType is a field in a referral table (tblReferral), you could
create
a query (qryRefType) based on tblReferral, with just the field
ReferralType.
Right click the top of the query in design view (above the grid), and set
Unique Values to Yes.
A query uses code known as SQL behind the scenes. You can see a query's
SQL
by opening a query and clicking View > SQL. You can also use SQL
directly
as a Row Source. Here is an excerpt from this web page:
http://www.mvps.org/access/forms/frm0043.htm
I have adapted it to your situation.

SELECT DISTINCT ReferralType FROM tblReferral UNION Select "(All)" as
Bogus
FROM tblReferral ORDER BY ReferralType;

There are no line breaks, although some may appear in your newsreader
window.

This should give you a list of ReferralTypes, with (All) at the top of
the
list. The DISTINCT in the code means that even though a ReferralType
appears in many records in tblReferral, it appears only once in the list.

The combo box column count would be 1, and the column width about 1".
You
can set the Default Value of the combo box to "(All)" so that (All)
appears
even if no choice is made.

You could use a Value List for the combo box Row Source, with "(All)" as
one
of the choices, but by using the SQL you can change or add the list of
Referral Types without changing the code.

The Row Source for the combo box from which you select the actual
referral
could be something like:

SELECT tblReferral.Referral
FROM tblReferral WHERE
(((IIf([Forms]![frmReferral]![cboReferral]<>"All","",
[Referral] = [Forms]![frmReferral]![cboReferral]))=True))
ORDER BY tblReferral.Referral;

I have tested this code by adapting it from one of my own projects. I
cannot say for sure that it will work in your project since I don't know
all
of the details of what you have done.

One of my assumptions is that tblReferral contains fields for Referral
(CPS,
etc.) and ReferralType, along with whatever else you need.

For a two-column combo box (with both columns visible), use a two-column
Row
Source query. Set the column count to 2 and the column widths to
something
like 1";1"
However, I don't think that is what you really need here.

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


:

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.

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


:

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

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


:

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,

 

[BruceM]

Hi, Bruce -- Here's what I have. I think, at present, the only thing not
working right is the All feature -- I've probably scavenged/guessed enough
to
screw up the original code which might have worked exactly as it was. The
difference seemed to be that I had 2 tables I was drawing from and the
Referral one didn't have the Referral Type as a text name, which was what
I
wanted to show up in the 1st combo box. So, I was trying to pull from both
and I think that's what got screwed up. If this doesn't work, as I'm doing
it, I can simply add a field to Table #2 and get everything in one
table --
probably what I should have done in the 1st place?

2 tables: Resource ID Table (Resource ID, Resource Type) and
ReferralSourcesAll (Resource ID, Referral Source Label)

What is the Row Source for the first combo box (the ReferralType one)?
SELECT DISTINCT [Resource Type] FROM [Resource ID Table] UNION SELECT
"(All)" as Bogus FROM [Resource ID Table]
ORDER BY [Resource Type];

This should work, assuming the fields and table names are correct. To test,
create a new query in design view. Don't select any tables, and click View

SQL. Enter the code:

SELECT DISTINCT [Resource Type]
FROM [Resource ID Table]
UNION
SELECT "(All)" as Bogus
FROM [Resource ID Table]
ORDER BY [Resource Type];

The line breaks are just to make it easier to read and follow. Switch to
datasheet view. Do you see the list? If not, create a query in design
view. Add Resource ID Table, click OK, and add just the ResourceType field.
Switch to datasheet view. If it is OK, switch to SQL view and note exactly
what you see there.

This combo box is unbound, as I see it.

I can't quite make out the purpose of ReferralSourcesAll. I woud imagine
Referral data something like this:

tblReferralSourcesAll
RefSourceID (autonumber primary key)
ResourceType (Agency, etc.)
Resource (Name of referral)
Phone, Address, etc.

RefSourceID ResourceType Resource
1 Agency CPS
2 General Dr. Welby
3 General Dr. Jekyll

I have left out Phone, etc., as they don't matter for this, although you may
need them.

The second combo box (I will call it cboSource; note the name, for I will
refer to it again soon) would have as its Row Source:

SELECT tblReferralSourcesAll.Resource
FROM tblReferralSourcesAll
WHERE (((IIf([Forms]![IntakeWorksheet2]![ResourceType]<>"All","",
[Resource] = [Forms]![IntakeWorksheet2]![ResourceType]))=True))
ORDER BY tblReferralSourcesAll.Resource;

This assumes that you are storing the value Agency, etc. from [Resource ID
Table]. The way you could put that value into tblReferralSourcesAll is to
make a form based on tblReferralSourcesAll, with a combo box bound to the
ResourceType field (that is, ResourceType is its ControlSource, which means
that values entered into the combo box are stored in the ResourceType
field). That combo box, with a column count of 1 and a column width of,
say, 1", would have as its Row Source the same Row Source code as described
for your first combo box. Select the ResourceType, and it will be inserted
into the field.

Back to your first combo box. In its After Update event you would have
Me.cboSource.Requery. To add this code, open the form's property sheet:
Click the combo box to select it, click View > Properties, and click the
Event tab. Click After Update, click the three dots on the right, and
insert Me.cboSource.Requery between Private Sub and End Sub. What this does
is to requery the Row Source for cboSource. That is to say, it runs the Row
Source code, with the selected value as the parameter. If you selected
Agency, then [Forms]![IntakeWorksheet2]![ReferralType] contains the value
"Agency". The Row Source looks for all Resource records that have "Agency"
as the ResourceType.

You will probably want to put Me.cboSource.Requery into the form's Current
event, so that when you arrive at a record the Resource text box will have
the correct list.

I hope this gets you closer to where you need to be.

What is the Row Source for the second combo box (the Referral one)?
Again,
any code?

SELECT [Resource ID Table].[Resource ID], ReferralSourcesAll.[Referral
Source Label], [Resource ID Table].[Resource Type]
FROM [Resource ID Table] INNER JOIN ReferralSourcesAll ON [Resource ID
Table].[Resource ID] = ReferralSourcesAll.[Resource ID]
WHERE ((([Resource ID Table].[Resource Type])=[Forms]![Intake
Worksheet2]![ReferralType]));



Thanks!!

Meredith

What is the Row Source for the first combo box (the ReferralType one)?
Does
it have any After Update code or other code? I'm not sure where you are
in
your Access vocabulary, so at the risk of saying something you already
know,
click the combo box to select it, click View > Properties, and Click the
Event tab. If any of the Events has [Event Procedure], click the three
dots
to the right of the row to view the code. The code will go something
like:

Private Sub cboReferralType_After Update

{Code here}

End Sub


What is the Row Source for the second combo box (the Referral one)?
Again,
any code?

In describing the Row Source, remember that I can't see you database, so
you
should describe any tables and fields that appear if if is not obvious by
context. For instance, I can assume that Referral is the name of the
Referral, but other things may not be as clear.

Hi, Bruce -- I'm working to implement your suggested combo boxes
w/criteria
but am having some coding problems, apparently -- Can you help with
that?
If
so, what should I post?? The 2nd combo box is appearing as a parameter
query
for the 1st and not picking up the values ...

Thanks,

Meredith


:

A combo box list (Row Source) may be either a Value List
("Excellent","Good","Fair","Poor") or a Table/Query. Assuming that
ReferralType is a field in a referral table (tblReferral), you could
create
a query (qryRefType) based on tblReferral, with just the field
ReferralType.
Right click the top of the query in design view (above the grid), and
set
Unique Values to Yes.
A query uses code known as SQL behind the scenes. You can see a
query's
SQL
by opening a query and clicking View > SQL. You can also use SQL
directly
as a Row Source. Here is an excerpt from this web page:
http://www.mvps.org/access/forms/frm0043.htm
I have adapted it to your situation.

SELECT DISTINCT ReferralType FROM tblReferral UNION Select "(All)" as
Bogus
FROM tblReferral ORDER BY ReferralType;

There are no line breaks, although some may appear in your newsreader
window.

This should give you a list of ReferralTypes, with (All) at the top of
the
list. The DISTINCT in the code means that even though a ReferralType
appears in many records in tblReferral, it appears only once in the
list.

The combo box column count would be 1, and the column width about 1".
You
can set the Default Value of the combo box to "(All)" so that (All)
appears
even if no choice is made.

You could use a Value List for the combo box Row Source, with "(All)"
as
one
of the choices, but by using the SQL you can change or add the list of
Referral Types without changing the code.

The Row Source for the combo box from which you select the actual
referral
could be something like:

SELECT tblReferral.Referral
FROM tblReferral WHERE
(((IIf([Forms]![frmReferral]![cboReferral]<>"All","",
[Referral] = [Forms]![frmReferral]![cboReferral]))=True))
ORDER BY tblReferral.Referral;

I have tested this code by adapting it from one of my own projects. I
cannot say for sure that it will work in your project since I don't
know
all
of the details of what you have done.

One of my assumptions is that tblReferral contains fields for Referral
(CPS,
etc.) and ReferralType, along with whatever else you need.

For a two-column combo box (with both columns visible), use a
two-column
Row
Source query. Set the column count to 2 and the column widths to
something
like 1";1"
However, I don't think that is what you really need here.

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


:

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.

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


:

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

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


:

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,

 

[Guest]

Thanks, Bruce. Let me see what I've got and I'll get back to you, probably
tomorrow. The first combo box does work -- the list shows up and I can select
an item from it. I think the easiest way to fix what's wrong, for starters,
is to do what I should have done in the beginning and combine all the fields
into one table. Not sure why I had 2 tables anyway. I'll work from that
angle...

Meredith

Hi, Bruce -- Here's what I have. I think, at present, the only thing not
working right is the All feature -- I've probably scavenged/guessed enough
to
screw up the original code which might have worked exactly as it was. The
difference seemed to be that I had 2 tables I was drawing from and the
Referral one didn't have the Referral Type as a text name, which was what
I
wanted to show up in the 1st combo box. So, I was trying to pull from both
and I think that's what got screwed up. If this doesn't work, as I'm doing
it, I can simply add a field to Table #2 and get everything in one
table --
probably what I should have done in the 1st place?

2 tables: Resource ID Table (Resource ID, Resource Type) and
ReferralSourcesAll (Resource ID, Referral Source Label)

What is the Row Source for the first combo box (the ReferralType one)?
SELECT DISTINCT [Resource Type] FROM [Resource ID Table] UNION SELECT
"(All)" as Bogus FROM [Resource ID Table]
ORDER BY [Resource Type];

This should work, assuming the fields and table names are correct. To test,
create a new query in design view. Don't select any tables, and click View

SQL. Enter the code:

SELECT DISTINCT [Resource Type]
FROM [Resource ID Table]
UNION
SELECT "(All)" as Bogus
FROM [Resource ID Table]
ORDER BY [Resource Type];

The line breaks are just to make it easier to read and follow. Switch to
datasheet view. Do you see the list? If not, create a query in design
view. Add Resource ID Table, click OK, and add just the ResourceType field.
Switch to datasheet view. If it is OK, switch to SQL view and note exactly
what you see there.

This combo box is unbound, as I see it.

I can't quite make out the purpose of ReferralSourcesAll. I woud imagine
Referral data something like this:

tblReferralSourcesAll
RefSourceID (autonumber primary key)
ResourceType (Agency, etc.)
Resource (Name of referral)
Phone, Address, etc.

RefSourceID ResourceType Resource
1 Agency CPS
2 General Dr. Welby
3 General Dr. Jekyll

I have left out Phone, etc., as they don't matter for this, although you may
need them.

The second combo box (I will call it cboSource; note the name, for I will
refer to it again soon) would have as its Row Source:

SELECT tblReferralSourcesAll.Resource
FROM tblReferralSourcesAll
WHERE (((IIf([Forms]![IntakeWorksheet2]![ResourceType]<>"All","",
[Resource] = [Forms]![IntakeWorksheet2]![ResourceType]))=True))
ORDER BY tblReferralSourcesAll.Resource;

This assumes that you are storing the value Agency, etc. from [Resource ID
Table]. The way you could put that value into tblReferralSourcesAll is to
make a form based on tblReferralSourcesAll, with a combo box bound to the
ResourceType field (that is, ResourceType is its ControlSource, which means
that values entered into the combo box are stored in the ResourceType
field). That combo box, with a column count of 1 and a column width of,
say, 1", would have as its Row Source the same Row Source code as described
for your first combo box. Select the ResourceType, and it will be inserted
into the field.

Back to your first combo box. In its After Update event you would have
Me.cboSource.Requery. To add this code, open the form's property sheet:
Click the combo box to select it, click View > Properties, and click the
Event tab. Click After Update, click the three dots on the right, and
insert Me.cboSource.Requery between Private Sub and End Sub. What this does
is to requery the Row Source for cboSource. That is to say, it runs the Row
Source code, with the selected value as the parameter. If you selected
Agency, then [Forms]![IntakeWorksheet2]![ReferralType] contains the value
"Agency". The Row Source looks for all Resource records that have "Agency"
as the ResourceType.

You will probably want to put Me.cboSource.Requery into the form's Current
event, so that when you arrive at a record the Resource text box will have
the correct list.

I hope this gets you closer to where you need to be.

What is the Row Source for the second combo box (the Referral one)?
Again,
any code?

SELECT [Resource ID Table].[Resource ID], ReferralSourcesAll.[Referral
Source Label], [Resource ID Table].[Resource Type]
FROM [Resource ID Table] INNER JOIN ReferralSourcesAll ON [Resource ID
Table].[Resource ID] = ReferralSourcesAll.[Resource ID]
WHERE ((([Resource ID Table].[Resource Type])=[Forms]![Intake
Worksheet2]![ReferralType]));



Thanks!!

Meredith

What is the Row Source for the first combo box (the ReferralType one)?
Does
it have any After Update code or other code? I'm not sure where you are
in
your Access vocabulary, so at the risk of saying something you already
know,
click the combo box to select it, click View > Properties, and Click the
Event tab. If any of the Events has [Event Procedure], click the three
dots
to the right of the row to view the code. The code will go something
like:

Private Sub cboReferralType_After Update

{Code here}

End Sub


What is the Row Source for the second combo box (the Referral one)?
Again,
any code?

In describing the Row Source, remember that I can't see you database, so
you
should describe any tables and fields that appear if if is not obvious by
context. For instance, I can assume that Referral is the name of the
Referral, but other things may not be as clear.

Hi, Bruce -- I'm working to implement your suggested combo boxes
w/criteria
but am having some coding problems, apparently -- Can you help with
that?
If
so, what should I post?? The 2nd combo box is appearing as a parameter
query
for the 1st and not picking up the values ...

Thanks,

Meredith


:

A combo box list (Row Source) may be either a Value List
("Excellent","Good","Fair","Poor") or a Table/Query. Assuming that
ReferralType is a field in a referral table (tblReferral), you could
create
a query (qryRefType) based on tblReferral, with just the field
ReferralType.
Right click the top of the query in design view (above the grid), and
set
Unique Values to Yes.
A query uses code known as SQL behind the scenes. You can see a
query's
SQL
by opening a query and clicking View > SQL. You can also use SQL
directly
as a Row Source. Here is an excerpt from this web page:
http://www.mvps.org/access/forms/frm0043.htm
I have adapted it to your situation.

SELECT DISTINCT ReferralType FROM tblReferral UNION Select "(All)" as
Bogus
FROM tblReferral ORDER BY ReferralType;

There are no line breaks, although some may appear in your newsreader
window.

This should give you a list of ReferralTypes, with (All) at the top of
the
list. The DISTINCT in the code means that even though a ReferralType
appears in many records in tblReferral, it appears only once in the
list.

The combo box column count would be 1, and the column width about 1".
You
can set the Default Value of the combo box to "(All)" so that (All)
appears
even if no choice is made.

You could use a Value List for the combo box Row Source, with "(All)"
as
one
of the choices, but by using the SQL you can change or add the list of
Referral Types without changing the code.

The Row Source for the combo box from which you select the actual
referral
could be something like:

SELECT tblReferral.Referral
FROM tblReferral WHERE
(((IIf([Forms]![frmReferral]![cboReferral]<>"All","",
[Referral] = [Forms]![frmReferral]![cboReferral]))=True))
ORDER BY tblReferral.Referral;

I have tested this code by adapting it from one of my own projects. I
cannot say for sure that it will work in your project since I don't
know
all
of the details of what you have done.

One of my assumptions is that tblReferral contains fields for Referral
(CPS,
etc.) and ReferralType, along with whatever else you need.

For a two-column combo box (with both columns visible), use a
two-column
Row
Source query. Set the column count to 2 and the column widths to
something
like 1";1"
However, I don't think that is what you really need here.

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


:

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.

 

[BruceM]

Good idea using the one table. A lookup table is fine for holding a list of
values (such as referral type, or on a larger scale, cities or states) from
which the user chooses. The chosen value is stored in the main table (or it
may be linked, but with a single field there is probably little to be gained
by doing that). In your case, a list of referral types can be held in a
ReferralType table, but that value ends up being stored in individual
records in the ReferralSources table, just as city or state typically is
stored with the rest of a person's contact information.

Thanks, Bruce. Let me see what I've got and I'll get back to you, probably
tomorrow. The first combo box does work -- the list shows up and I can
select
an item from it. I think the easiest way to fix what's wrong, for
starters,
is to do what I should have done in the beginning and combine all the
fields
into one table. Not sure why I had 2 tables anyway. I'll work from that
angle...

Meredith

Hi, Bruce -- Here's what I have. I think, at present, the only thing
not
working right is the All feature -- I've probably scavenged/guessed
enough
to
screw up the original code which might have worked exactly as it was.
The
difference seemed to be that I had 2 tables I was drawing from and the
Referral one didn't have the Referral Type as a text name, which was
what
I
wanted to show up in the 1st combo box. So, I was trying to pull from
both
and I think that's what got screwed up. If this doesn't work, as I'm
doing
it, I can simply add a field to Table #2 and get everything in one
table --
probably what I should have done in the 1st place?

2 tables: Resource ID Table (Resource ID, Resource Type) and
ReferralSourcesAll (Resource ID, Referral Source Label)

What is the Row Source for the first combo box (the ReferralType one)?
SELECT DISTINCT [Resource Type] FROM [Resource ID Table] UNION SELECT
"(All)" as Bogus FROM [Resource ID Table]
ORDER BY [Resource Type];

This should work, assuming the fields and table names are correct. To
test,
create a new query in design view. Don't select any tables, and click
View

SQL. Enter the code:

SELECT DISTINCT [Resource Type]
FROM [Resource ID Table]
UNION
SELECT "(All)" as Bogus
FROM [Resource ID Table]
ORDER BY [Resource Type];

The line breaks are just to make it easier to read and follow. Switch to
datasheet view. Do you see the list? If not, create a query in design
view. Add Resource ID Table, click OK, and add just the ResourceType
field.
Switch to datasheet view. If it is OK, switch to SQL view and note
exactly
what you see there.

This combo box is unbound, as I see it.

I can't quite make out the purpose of ReferralSourcesAll. I woud imagine
Referral data something like this:

tblReferralSourcesAll
RefSourceID (autonumber primary key)
ResourceType (Agency, etc.)
Resource (Name of referral)
Phone, Address, etc.

RefSourceID ResourceType Resource
1 Agency CPS
2 General Dr. Welby
3 General Dr. Jekyll

I have left out Phone, etc., as they don't matter for this, although you
may
need them.

The second combo box (I will call it cboSource; note the name, for I will
refer to it again soon) would have as its Row Source:

SELECT tblReferralSourcesAll.Resource
FROM tblReferralSourcesAll
WHERE (((IIf([Forms]![IntakeWorksheet2]![ResourceType]<>"All","",
[Resource] = [Forms]![IntakeWorksheet2]![ResourceType]))=True))
ORDER BY tblReferralSourcesAll.Resource;

This assumes that you are storing the value Agency, etc. from [Resource
ID
Table]. The way you could put that value into tblReferralSourcesAll is
to
make a form based on tblReferralSourcesAll, with a combo box bound to the
ResourceType field (that is, ResourceType is its ControlSource, which
means
that values entered into the combo box are stored in the ResourceType
field). That combo box, with a column count of 1 and a column width of,
say, 1", would have as its Row Source the same Row Source code as
described
for your first combo box. Select the ResourceType, and it will be
inserted
into the field.

Back to your first combo box. In its After Update event you would have
Me.cboSource.Requery. To add this code, open the form's property sheet:
Click the combo box to select it, click View > Properties, and click the
Event tab. Click After Update, click the three dots on the right, and
insert Me.cboSource.Requery between Private Sub and End Sub. What this
does
is to requery the Row Source for cboSource. That is to say, it runs the
Row
Source code, with the selected value as the parameter. If you selected
Agency, then [Forms]![IntakeWorksheet2]![ReferralType] contains the value
"Agency". The Row Source looks for all Resource records that have
"Agency"
as the ResourceType.

You will probably want to put Me.cboSource.Requery into the form's
Current
event, so that when you arrive at a record the Resource text box will
have
the correct list.

I hope this gets you closer to where you need to be.

What is the Row Source for the second combo box (the Referral one)?
Again,
any code?

SELECT [Resource ID Table].[Resource ID], ReferralSourcesAll.[Referral
Source Label], [Resource ID Table].[Resource Type]
FROM [Resource ID Table] INNER JOIN ReferralSourcesAll ON [Resource ID
Table].[Resource ID] = ReferralSourcesAll.[Resource ID]
WHERE ((([Resource ID Table].[Resource Type])=[Forms]![Intake
Worksheet2]![ReferralType]));



Thanks!!

Meredith


:

What is the Row Source for the first combo box (the ReferralType one)?
Does
it have any After Update code or other code? I'm not sure where you
are
in
your Access vocabulary, so at the risk of saying something you already
know,
click the combo box to select it, click View > Properties, and Click
the
Event tab. If any of the Events has [Event Procedure], click the
three
dots
to the right of the row to view the code. The code will go something
like:

Private Sub cboReferralType_After Update

{Code here}

End Sub


What is the Row Source for the second combo box (the Referral one)?
Again,
any code?

In describing the Row Source, remember that I can't see you database,
so
you
should describe any tables and fields that appear if if is not obvious
by
context. For instance, I can assume that Referral is the name of the
Referral, but other things may not be as clear.

Hi, Bruce -- I'm working to implement your suggested combo boxes
w/criteria
but am having some coding problems, apparently -- Can you help with
that?
If
so, what should I post?? The 2nd combo box is appearing as a
parameter
query
for the 1st and not picking up the values ...

Thanks,

Meredith


:

A combo box list (Row Source) may be either a Value List
("Excellent","Good","Fair","Poor") or a Table/Query. Assuming that
ReferralType is a field in a referral table (tblReferral), you
could
create
a query (qryRefType) based on tblReferral, with just the field
ReferralType.
Right click the top of the query in design view (above the grid),
and
set
Unique Values to Yes.
A query uses code known as SQL behind the scenes. You can see a
query's
SQL
by opening a query and clicking View > SQL. You can also use SQL
directly
as a Row Source. Here is an excerpt from this web page:
http://www.mvps.org/access/forms/frm0043.htm
I have adapted it to your situation.

SELECT DISTINCT ReferralType FROM tblReferral UNION Select "(All)"
as
Bogus
FROM tblReferral ORDER BY ReferralType;

There are no line breaks, although some may appear in your
newsreader
window.

This should give you a list of ReferralTypes, with (All) at the top
of
the
list. The DISTINCT in the code means that even though a
ReferralType
appears in many records in tblReferral, it appears only once in the
list.

The combo box column count would be 1, and the column width about
1".
You
can set the Default Value of the combo box to "(All)" so that (All)
appears
even if no choice is made.

You could use a Value List for the combo box Row Source, with
"(All)"
as
one
of the choices, but by using the SQL you can change or add the list
of
Referral Types without changing the code.

The Row Source for the combo box from which you select the actual
referral
could be something like:

SELECT tblReferral.Referral
FROM tblReferral WHERE
(((IIf([Forms]![frmReferral]![cboReferral]<>"All","",
[Referral] = [Forms]![frmReferral]![cboReferral]))=True))
ORDER BY tblReferral.Referral;

I have tested this code by adapting it from one of my own projects.
I
cannot say for sure that it will work in your project since I don't
know
all
of the details of what you have done.

One of my assumptions is that tblReferral contains fields for
Referral
(CPS,
etc.) and ReferralType, along with whatever else you need.

For a two-column combo box (with both columns visible), use a
two-column
Row
Source query. Set the column count to 2 and the column widths to
something
like 1";1"
However, I don't think that is what you really need here.

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


:

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.