A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help with Junction Tables and Subforms



 
 
Thread Tools Display Modes
  #1  
Old September 19th, 2004, 03:11 AM
Maureen Smith
external usenet poster
 
Posts: n/a
Default Help with Junction Tables and Subforms

I work for a not-for-profit healthcare organization. I am fairly new at
using Access and I'm trying to set up a database of other facilities in the
region, using Access 2000. I need some direction and advice.

The main table contains facility information: address and telephone numbers,
web site address, number of full and part time personnel. It also contains
some yes/no choices, and a site ID (which is there to serve as the primary
key). There are tables for administrator names and contact info, director(s)
of nursing names and contact info. There are tables for Inpatient Services
(which each facility offers), Outpatient Services, Community Outreach
Services, and I'm sure you get the drill. Within each of those services
tables is a list of specific offerings, which each facility may or may not
offer. I have reference tables for the different services, within each
Service catagory, and I have (I think) junction tables for the data that
will, hopefully, accumulate. Within the junction tables are fields
containing FacilityID and ServiceID (each service being differently named,
of course). Each facility offers different service areas (inpatient,
outpatient, etc.) and different types of services within those service
areas.

I've been reading help screens and Googling groups, but I'm not
understanding how to accomplish what I need to do. I want to set up a form -
and I imagine subforms - to input this data. I wanted to use the Tab Control
for data input, because that appeared to be the way to go. A tab for
"general facility info," a tab for the different service types, a tab for
the administrator/director of nursing info.

When I am in Form design view, I don't have a "Fields List" icon on my
toolbar. I don't seem to be able to get the fields from the subtables to
join the party. I'm not sure that I have my relationships right. I have the
reference tables linked by ServiceID as the "one" side to the Patient
Services tables, which are linked as the "one" side by FacilityID to the
main table. Are my "ones" and "manys" backward?

Any help will be much appreciated.


  #2  
Old September 19th, 2004, 03:51 PM
rpw
external usenet poster
 
Posts: n/a
Default

You've got quite a bit going on here - where to start?

The "Fields List" will not appear until you've assigned a record source to
the form.

When you are using the Tab control on the form, if each tab will display
information from different record sources, you will need to build the
sub-forms first and then place it on the tab page of the main form.

If you'd like the newsgroup people to offer assistance on your table
structure & relationships, then post a listing of the tables in the following
format:

tblFacility
FacilityID PK
FacilityName
FacAddress
'other fields that describe facility...

tblFacilityServices
FacilityID FK { these two fields are
ServicesID FK { combined to be the PK

In the above example, it's easy for the readers here to determine which is
(should be) the 'one' and which is the 'many'.

Regards,


"Maureen Smith" wrote:

I work for a not-for-profit healthcare organization. I am fairly new at
using Access and I'm trying to set up a database of other facilities in the
region, using Access 2000. I need some direction and advice.

The main table contains facility information: address and telephone numbers,
web site address, number of full and part time personnel. It also contains
some yes/no choices, and a site ID (which is there to serve as the primary
key). There are tables for administrator names and contact info, director(s)
of nursing names and contact info. There are tables for Inpatient Services
(which each facility offers), Outpatient Services, Community Outreach
Services, and I'm sure you get the drill. Within each of those services
tables is a list of specific offerings, which each facility may or may not
offer. I have reference tables for the different services, within each
Service catagory, and I have (I think) junction tables for the data that
will, hopefully, accumulate. Within the junction tables are fields
containing FacilityID and ServiceID (each service being differently named,
of course). Each facility offers different service areas (inpatient,
outpatient, etc.) and different types of services within those service
areas.

I've been reading help screens and Googling groups, but I'm not
understanding how to accomplish what I need to do. I want to set up a form -
and I imagine subforms - to input this data. I wanted to use the Tab Control
for data input, because that appeared to be the way to go. A tab for
"general facility info," a tab for the different service types, a tab for
the administrator/director of nursing info.

When I am in Form design view, I don't have a "Fields List" icon on my
toolbar. I don't seem to be able to get the fields from the subtables to
join the party. I'm not sure that I have my relationships right. I have the
reference tables linked by ServiceID as the "one" side to the Patient
Services tables, which are linked as the "one" side by FacilityID to the
main table. Are my "ones" and "manys" backward?

Any help will be much appreciated.



  #3  
Old September 21st, 2004, 02:10 AM
Maureen Smith
external usenet poster
 
Posts: n/a
Default

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?

rpw wrote in message
...
You've got quite a bit going on here - where to start?

The "Fields List" will not appear until you've assigned a record source to
the form.

When you are using the Tab control on the form, if each tab will display
information from different record sources, you will need to build the
sub-forms first and then place it on the tab page of the main form.

If you'd like the newsgroup people to offer assistance on your table
structure & relationships, then post a listing of the tables in the

following
format:

tblFacility
FacilityID PK
FacilityName
FacAddress
'other fields that describe facility...

tblFacilityServices
FacilityID FK { these two fields are
ServicesID FK { combined to be the PK

In the above example, it's easy for the readers here to determine which is
(should be) the 'one' and which is the 'many'.




  #4  
Old September 21st, 2004, 03:39 AM
rpw
external usenet poster
 
Posts: n/a
Default

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?


  #5  
Old September 21st, 2004, 04:59 AM
Maureen Smith
external usenet poster
 
Posts: n/a
Default

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?




  #6  
Old September 21st, 2004, 07:04 AM
John Vinson
external usenet poster
 
Posts: n/a
Default

On Mon, 20 Sep 2004 21:59:23 -0600, "Maureen Smith"
wrote:

I thought I needed it in tblGeneralServices so that the service names would be
readable, as opposed to having just the ID number appear.


This is a VERY common mistake.

Part of it comes from the reasonable assumption that you'll be looking
at tables in order to see your data. BUT YOU WON'T. Tables are for
data *storage* - not for data presentation! If you base your Form on
the table, you can put combo boxes on the Form bound to the (numeric!)
ID number field, but showing the readable text; the user sees
"Maintenance", the computer sees 3, and they're both happy.

For a Report, you'ld use a Query joining the tables on the ID field.
Again, you'ld pick the service names from tblGeneralServices in the
Query - there is NO need to store the name redundantly.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
  #7  
Old September 21st, 2004, 04:37 PM
rpw
external usenet poster
 
Posts: n/a
Default

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?





  #8  
Old September 22nd, 2004, 01:08 AM
Maureen Smith
external usenet poster
 
Posts: n/a
Default

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?






  #9  
Old September 22nd, 2004, 02:48 AM
John Vinson
external usenet poster
 
Posts: n/a
Default

On Tue, 21 Sep 2004 18:08:37 -0600, "Maureen Smith"
wrote:

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


Turn your understanding "sideways".

tblServType would have ONE field - ServType.

tblServSubType would have two fields - ServType and ServSubType.

Rather than putting service into fieldnames you would store them as
*values in the table*. Reread rpw's suggestion in this light and see
if it makes more sense.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
  #10  
Old September 22nd, 2004, 05:51 AM
rpw
external usenet poster
 
Posts: n/a
Default

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?







 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
updating multiple tables Sunny Using Forms 2 July 30th, 2004 06:08 PM
Data entry forms and/or subforms Hugh Crean Using Forms 2 July 8th, 2004 05:56 PM
Adding recs from subforms TracyG General Discussion 2 June 14th, 2004 06:51 PM
Requery of Subforms Joe Using Forms 2 June 10th, 2004 09:09 PM


All times are GMT +1. The time now is 02:58 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.