Help with Junction Tables and Subforms

Thank you for clarifying it. I didn't mean to appear so thick. The Service
Types and Service SubTypes, as I had listed them, are meant to be the data,
and I knew that's what you meant.

Thanks again. I'll give it a go (I'm leaving it on the computer at the
office, as that one is A2K and mine at home is A97 and some things don't
make the jump exactly the same) tomorrow.

Another "thank you" to John Vinson. I appreciate your willingness to help
the clueless!

rpw wrote in message
...
Hi Maureen,

You know when you are creating the table and you're entering the field
names
and the field types? That's design mode. The table structure I posted is
the 'design mode' (aka the table structure).

What you have posted is the 'data' that goes into the structure. It seems
that you are wanting to make each "type" as a separate field and you
shouldn't do that. The field is "ServiceTypeName" - the data that goes
into
the field (on separate rows/records) are the names of each service type.

So, after you construct the table and save it, you can open the table and
enter/view the data. The first table (tblServiceTypes) should have two
columns/fields and six rows/records and would look something like this
when
you open the table:

ServiceTypeID ServiceTypeName
1 General
2 In Patient
3 Out Patient
4 Community Outreach
5 Imaging
6 Patient Family

The second table should look something like this when it's open:

SubTypeID ServiceTypeID SubTypeName
1 1 General Medical and
Surgical Care
2 1 Emergency Department
3 3 Intermediate nursing care

I'm hoping this helps you understand it better.

Post back if you have more questions! (and thanks John for helping out!)

"Maureen Smith" wrote:

I am not quite understanding what I'd need to do. This is my
understanding
of the table structure you suggest.

tblServType
General
Inpatient
Outpatient
CommunityOutreach
Imaging
PatientFamily

tblServSubType
General Medical and Surgical Care
Emergency Department
Pediatric Medical and Surgical Care
General intensive care
Cardiac intensive care
Obstetrics
Orthopedics
Ultrasound
CT Scanner
MRI
Diagnostic radioisotope facility
Elderly/disabled skilled nursing care
Elderly/disabled acute long-term care
Hospice
Infection isolation
Birthing room
Psychiatric emergency services
Psychiatric hospitalization
Intermediate nursing care
Pain management
Palliative care
Breast cancer screening/mammograms
Certified trauma center
Geriatric services
Home health services
Kidney dialysis
Physical rehabilitation
Child/adolescent psychiatric services
Psychiatric outpatient care
Sleep center
Smoking cessation
Wound management
Chemotherapy
Urgent care
Complementary/alternative medicine
Assistance with government services
Translation services
Patient support groups
Elderly/handicapped transportation
Ambulance
Ombudsman
Health screenings
Health fairs
Meals on Wheels

Quite a list of subservices. How would I relate them? I would bet that
there
will be more subservices added in the future.

rpw wrote in message
...
You have many different "Services" - have you considered to have a
Services
Type table (that would hold: General, Inpatient, Outpatient, Outreach,
Imaging, etc.) and a 1:M to a Service Sub-Type table - or is each
service
type so different that they each need their own table? From what I
see in
the General Services it's only names and no other details.

Assuming that it is only types and sub-types that you are needing,
this is
how I'd set it up:

tblServiceTypes
ServiceTypeID - PK
ServiceTypeName

tblServiceSubTypes
SubTypeID - PK
ServiceTypeID - FK
SubTypeName

tblFacilityServices
FacilityID { combined
SubTypeID { PK
LocalSubTypeName
Description

The main form would have a tabs control. On one of the tabs would be
a
'Services' sub-form. This sub-form would have a listbox displaying
all of
the services the facility offers. The columns of the listbox would
show
ServiceTypeName, SubTypeName, LocalSubTypeName, and Description. You
could
have other controls for adding or modifying the items in the list.

Is this enough to get you going or do you want more details?


"Maureen Smith" wrote:

Thank you again!

You are correct that the GenServiceName is the same as in the ref
table;
I
thought I needed it in tblGeneralServices so that the service names
would be
readable, as opposed to having just the ID number appear.

I thought about doing the query for the subform, but as I have
General
Services (with about eight or nine different service types),
Inpatient
Services (ditto), Outpatient Services (ditto), Community Outreach
Services
(again), Imaging Services (about five or six), and PatientFamily
Services
(again, five or six items) - it seemed too much to try to use a
query to
make up one form (or one subform). If I'm wrong about this, by all
means
tell me how to accomplish it.

This whole database is probably a work in progress. It doesn't
exist,
yet.
I'm thinking that there may be more tables added in future
(physicians
and
their specialties come to mind pretty quickly; equipment that the
foundation
I work for has donated to the facilities is also something that will
need to
be included). Right now what they have have are several different
Excel
files with contact info, a few tables in Word with technical staff
info,
and
address lists in Outlook or OE. I'm tryng to put together something
that
won't be difficult for someone to use when information needs to be
modified,
but that will be searchable so that the desired information is
obtained.
Perhaps the additional telephone/fax numbers won't be amiss. For the
most
part, these are very rural healthcare facilities, so there won't be
a
plethora of fax numbers.

rpw wrote in message
...
Hi,
The only thing I noticed that might be amiss was that
GenServiceName
is
not
needed in tblGeneralServices because it's already in
reftblGenServices
(unless they're not the same info?).

That's right about assigning the record source. However, it
doesn't
necessarily have to be a table - it can be a query also. Maybe
your
sub-form
for services is a query based on both services tables and filtered
by
the
form's current facilityID. To build a query, click the elipsis
(....)
to
the
right of the record source field.

Looks like you're on the right track (at least with these tables).
You
mentioned in your first post something about patient services and
a
service
table?

As you're continuing to develop this, here's a thought for you:
Some
facilities may have more than one phone or one fax number. Maybe
you
want
to
have a table of facility phone numbers and a short list (or table)
of
phone
types (main business, fax, after-hours, admin.'s personal, etc.).

Post back if you have more questions/tables for analysis/etc.

rpw

"Maureen Smith" wrote:

Thanks for your comments. Here's the main table (Facility) and
one
of
the
services tables, with its associated reference table. I didn't
want
to
throw
everything at you!

tblFacility
FacilityID (PK)
facilityName
FacilityTypeID
FacAddress1
FacAddress2
City
State
ZIP
Telephone
Fax
County
LongTermCare (a yes/no)
Status (rural or frontier)
MUA (a yes/no)
HPSA (full, part, no)
MHHPSA (yes/no)
DHHPSA (yes/no)
RuralIndex (a number)
WebSite (if the faciity has one)
Email (ditto)
Comments (a memo field containing community demographic info)
FulltimeRNS
ParttimeRNs
FulltimeLPNs
ParttimeLPNs
FulltimePersonnel
ParttimePersonnel

tblGeneralServices
FacilityID (these two are combined
GenServiceID to be the PK)
GenServiceName

reftblGenServices
GenServiceID (PK)
GenServiceName

Again, each facility can offer more than one general service.

To assign the record source, that's the right-click on the
"square"
at
the
left corner of the design area, select Properties and give the
name
of
the
table that is the source for the data - correct?

I guess when you used the word "structure" then listed the data, I
misunderstood the question - how to "relate" the two tables. I'm thinking
that you already know how to open the relationship window and drag the ID
field from one table to another and define the relationship. You want to
know how to get "General" to relate to "General Medical and Surgical Care"?
Am I right, or did I miss the point again?

"Maureen Smith" wrote:

Thank you for clarifying it. I didn't mean to appear so thick. The Service
Types and Service SubTypes, as I had listed them, are meant to be the data,
and I knew that's what you meant.

Thanks again. I'll give it a go (I'm leaving it on the computer at the
office, as that one is A2K and mine at home is A97 and some things don't
make the jump exactly the same) tomorrow.

Another "thank you" to John Vinson. I appreciate your willingness to help
the clueless!

rpw wrote in message
...
Hi Maureen,

You know when you are creating the table and you're entering the field
names
and the field types? That's design mode. The table structure I posted is
the 'design mode' (aka the table structure).

What you have posted is the 'data' that goes into the structure. It seems
that you are wanting to make each "type" as a separate field and you
shouldn't do that. The field is "ServiceTypeName" - the data that goes
into
the field (on separate rows/records) are the names of each service type.

So, after you construct the table and save it, you can open the table and
enter/view the data. The first table (tblServiceTypes) should have two
columns/fields and six rows/records and would look something like this
when
you open the table:

ServiceTypeID ServiceTypeName
1 General
2 In Patient
3 Out Patient
4 Community Outreach
5 Imaging
6 Patient Family

The second table should look something like this when it's open:

SubTypeID ServiceTypeID SubTypeName
1 1 General Medical and
Surgical Care
2 1 Emergency Department
3 3 Intermediate nursing care

I'm hoping this helps you understand it better.

Post back if you have more questions! (and thanks John for helping out!)

"Maureen Smith" wrote:

I am not quite understanding what I'd need to do. This is my
understanding
of the table structure you suggest.

tblServType
General
Inpatient
Outpatient
CommunityOutreach
Imaging
PatientFamily

tblServSubType
General Medical and Surgical Care
Emergency Department
Pediatric Medical and Surgical Care
General intensive care
Cardiac intensive care
Obstetrics
Orthopedics
Ultrasound
CT Scanner
MRI
Diagnostic radioisotope facility
Elderly/disabled skilled nursing care
Elderly/disabled acute long-term care
Hospice
Infection isolation
Birthing room
Psychiatric emergency services
Psychiatric hospitalization
Intermediate nursing care
Pain management
Palliative care
Breast cancer screening/mammograms
Certified trauma center
Geriatric services
Home health services
Kidney dialysis
Physical rehabilitation
Child/adolescent psychiatric services
Psychiatric outpatient care
Sleep center
Smoking cessation
Wound management
Chemotherapy
Urgent care
Complementary/alternative medicine
Assistance with government services
Translation services
Patient support groups
Elderly/handicapped transportation
Ambulance
Ombudsman
Health screenings
Health fairs
Meals on Wheels

Quite a list of subservices. How would I relate them? I would bet that
there
will be more subservices added in the future.

rpw wrote in message
...
You have many different "Services" - have you considered to have a
Services
Type table (that would hold: General, Inpatient, Outpatient, Outreach,
Imaging, etc.) and a 1:M to a Service Sub-Type table - or is each
service
type so different that they each need their own table? From what I
see in
the General Services it's only names and no other details.

Assuming that it is only types and sub-types that you are needing,
this is
how I'd set it up:

tblServiceTypes
ServiceTypeID - PK
ServiceTypeName

tblServiceSubTypes
SubTypeID - PK
ServiceTypeID - FK
SubTypeName

tblFacilityServices
FacilityID { combined
SubTypeID { PK
LocalSubTypeName
Description

The main form would have a tabs control. On one of the tabs would be
a
'Services' sub-form. This sub-form would have a listbox displaying
all of
the services the facility offers. The columns of the listbox would
show
ServiceTypeName, SubTypeName, LocalSubTypeName, and Description. You
could
have other controls for adding or modifying the items in the list.

Is this enough to get you going or do you want more details?


"Maureen Smith" wrote:

Thank you again!

You are correct that the GenServiceName is the same as in the ref
table;
I
thought I needed it in tblGeneralServices so that the service names
would be
readable, as opposed to having just the ID number appear.

I thought about doing the query for the subform, but as I have
General
Services (with about eight or nine different service types),
Inpatient
Services (ditto), Outpatient Services (ditto), Community Outreach
Services
(again), Imaging Services (about five or six), and PatientFamily
Services
(again, five or six items) - it seemed too much to try to use a
query to
make up one form (or one subform). If I'm wrong about this, by all
means
tell me how to accomplish it.

This whole database is probably a work in progress. It doesn't
exist,
yet.
I'm thinking that there may be more tables added in future
(physicians
and
their specialties come to mind pretty quickly; equipment that the
foundation
I work for has donated to the facilities is also something that will
need to
be included). Right now what they have have are several different
Excel
files with contact info, a few tables in Word with technical staff
info,
and
address lists in Outlook or OE. I'm tryng to put together something
that
won't be difficult for someone to use when information needs to be
modified,
but that will be searchable so that the desired information is
obtained.
Perhaps the additional telephone/fax numbers won't be amiss. For the
most
part, these are very rural healthcare facilities, so there won't be
a
plethora of fax numbers.

rpw wrote in message
...
Hi,
The only thing I noticed that might be amiss was that
GenServiceName
is
not
needed in tblGeneralServices because it's already in
reftblGenServices
(unless they're not the same info?).

That's right about assigning the record source. However, it
doesn't
necessarily have to be a table - it can be a query also. Maybe
your
sub-form
for services is a query based on both services tables and filtered
by
the
form's current facilityID. To build a query, click the elipsis
(....)
to
the
right of the record source field.

Looks like you're on the right track (at least with these tables).
You
mentioned in your first post something about patient services and
a
service
table?

As you're continuing to develop this, here's a thought for you:
Some
facilities may have more than one phone or one fax number. Maybe
you
want
to
have a table of facility phone numbers and a short list (or table)
of
phone
types (main business, fax, after-hours, admin.'s personal, etc.).

Post back if you have more questions/tables for analysis/etc.

rpw

"Maureen Smith" wrote:

Thanks for your comments. Here's the main table (Facility) and
one
of
the
services tables, with its associated reference table. I didn't
want
to
throw
everything at you!

tblFacility
FacilityID (PK)
facilityName
FacilityTypeID
FacAddress1
FacAddress2
City
State
ZIP
Telephone
Fax
County
LongTermCare (a yes/no)
Status (rural or frontier)
MUA (a yes/no)
HPSA (full, part, no)
MHHPSA (yes/no)
DHHPSA (yes/no)
RuralIndex (a number)
WebSite (if the faciity has one)
Email (ditto)
Comments (a memo field containing community demographic info)
FulltimeRNS
ParttimeRNs
FulltimeLPNs
ParttimeLPNs
FulltimePersonnel
ParttimePersonnel

tblGeneralServices
FacilityID (these two are combined
GenServiceID to be the PK)
GenServiceName

reftblGenServices
GenServiceID (PK)
GenServiceName

Again, each facility can offer more than one general service.

To assign the record source, that's the right-click on the
"square"
at
the
left corner of the design area, select Properties and give the
name
of
the
table that is the source for the data - correct?