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  

Relationships between tables



 
 
Thread Tools Display Modes
  #1  
Old June 10th, 2004, 08:10 PM
Mel
external usenet poster
 
Posts: n/a
Default Relationships between tables

I need help! I have a DB with 5 tables (Program, Director,Alt contact,Private Practice, hospital,and Paytype) These are the relationships between them.
There can be one program associated with one director, and program names can be very similar. There can only be on hospital associated with one program. There can be many Private practices associated to one director. and there can be many alt contacts associated with one director.

I need to pull reports that show, what director is accoicated with which program,, and all of that directors private practice numbers, and the hospital that his/her program is associated with. Can someone please give me some insite as to how to set up the relationships between each of these fields so that when I do data entry, i will be able to see the correct infomation. For instance. I have a program i need to connect to a direector, this infomation is already in,, but i get a new private practice number for that director, i need to be able to associate an existing director to a new private practice,, and then pull the above mentioned report. Help please,, and if i havent made myself clear,, or you need further information to answer my question please email me
  #2  
Old June 11th, 2004, 01:35 AM
Dave
external usenet poster
 
Posts: n/a
Default Relationships between tables

Mel:

Perhaps you can get by with only 3 tables (I am ignoring the PayType table
you mentioned but did not discuss)

If the relationship between program and director and bewteen hospital and
program is truely one-to-one, then don't these tables really describe the
same entity? If so, then you can combine the fields from all three into a
single table. Typically you only create a one-to-one relationship among
tables when 1) there are security issues (i.e, some fields are sensitive) or
2) for performance improvement (to move large, infrequently queried fields
off by themselves in a separate table).

Thus, you would have your "director" table (which now contains "hospital"
and "program" attributes) and two subsidiary or child tables; "Alt contact"
and "Private Practice." These are referred to as child tables because each
is the "many" side of a one-to-many relationship with the "Director" table.
Director is the parent because it is the "one" side of the relationship and
a parent can have many children. Both of the child tables will have a field
called "directorid" or something similary that relates (or ties) them to
their parent.

So, to get the all ofthe information you are seeking for a particular
director, you might write the following query:

SELECT *
FROM director d LEFT JOIN altcontact a ON d.directorid=a.directorid
LEFT JOIN privatepractice ON d.directorid=p.directorid
WHERE directorid = 10

This will return all of the private practice and al contact records for
director #10. The LEFT JOINs ensure you get director #10's director
information even if he has no private practice or alt contact records.
Otherwise you won't get anything back if there are no private practice or
alt contact records for director #10.

Dave

"Mel" wrote in message
...
I need help! I have a DB with 5 tables (Program, Director,Alt

contact,Private Practice, hospital,and Paytype) These are the relationships
between them.
There can be one program associated with one director, and program names

can be very similar. There can only be on hospital associated with one
program. There can be many Private practices associated to one director. and
there can be many alt contacts associated with one director.

I need to pull reports that show, what director is accoicated with which

program,, and all of that directors private practice numbers, and the
hospital that his/her program is associated with. Can someone please give me
some insite as to how to set up the relationships between each of these
fields so that when I do data entry, i will be able to see the correct
infomation. For instance. I have a program i need to connect to a direector,
this infomation is already in,, but i get a new private practice number for
that director, i need to be able to associate an existing director to a new
private practice,, and then pull the above mentioned report. Help please,,
and if i havent made myself clear,, or you need further information to
answer my question please email me


  #3  
Old June 11th, 2004, 04:00 PM
marolla13
external usenet poster
 
Posts: n/a
Default Relationships between tables

Dave,

Thank you this is very helpful,, I will probably set it up this way. I have one other question for you then,, in yous SQL you pulled a report on one director,, is there any way to pull all the same information on all directors that meet a specific criteria,, say all in a specific state and have all of their PP or Alt contact come up?

Thanks again

"Dave" wrote:

Mel:

Perhaps you can get by with only 3 tables (I am ignoring the PayType table
you mentioned but did not discuss)

If the relationship between program and director and bewteen hospital and
program is truely one-to-one, then don't these tables really describe the
same entity? If so, then you can combine the fields from all three into a
single table. Typically you only create a one-to-one relationship among
tables when 1) there are security issues (i.e, some fields are sensitive) or
2) for performance improvement (to move large, infrequently queried fields
off by themselves in a separate table).

Thus, you would have your "director" table (which now contains "hospital"
and "program" attributes) and two subsidiary or child tables; "Alt contact"
and "Private Practice." These are referred to as child tables because each
is the "many" side of a one-to-many relationship with the "Director" table.
Director is the parent because it is the "one" side of the relationship and
a parent can have many children. Both of the child tables will have a field
called "directorid" or something similary that relates (or ties) them to
their parent.

So, to get the all ofthe information you are seeking for a particular
director, you might write the following query:

SELECT *
FROM director d LEFT JOIN altcontact a ON d.directorid=a.directorid
LEFT JOIN privatepractice ON d.directorid=p.directorid
WHERE directorid = 10

This will return all of the private practice and al contact records for
director #10. The LEFT JOINs ensure you get director #10's director
information even if he has no private practice or alt contact records.
Otherwise you won't get anything back if there are no private practice or
alt contact records for director #10.

Dave

"Mel" wrote in message
...
I need help! I have a DB with 5 tables (Program, Director,Alt

contact,Private Practice, hospital,and Paytype) These are the relationships
between them.
There can be one program associated with one director, and program names

can be very similar. There can only be on hospital associated with one
program. There can be many Private practices associated to one director. and
there can be many alt contacts associated with one director.

I need to pull reports that show, what director is accoicated with which

program,, and all of that directors private practice numbers, and the
hospital that his/her program is associated with. Can someone please give me
some insite as to how to set up the relationships between each of these
fields so that when I do data entry, i will be able to see the correct
infomation. For instance. I have a program i need to connect to a direector,
this infomation is already in,, but i get a new private practice number for
that director, i need to be able to associate an existing director to a new
private practice,, and then pull the above mentioned report. Help please,,
and if i havent made myself clear,, or you need further information to
answer my question please email me



 




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


All times are GMT +1. The time now is 05:54 AM.


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