 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / May 2005Six one to one related tables: question
My project has six table that are related "one to one" What is the proper way to combine them so I can use them for my form. Thanks Jake It depends what you want to use from each table - one approach migh be to use a query, an alternative would be to use subforms Further information regarding your query would help us to give better answer Thank you for your help Here is the list of items in my Big table... CaseNum (primary_Key) Deceased_First_Name Deceased_Middle_Name Deceased_Last_Name Sex SS Birthdate Birth_City Birth_State Spouse Marriage_Date Marriage_Place Marriage_Status (Married, Divorced, Seperated, Never Married) Address (Street Address) City CityType (City, Township, Village) County State Zip Father_Name Mother_Name Occupation Industry Military (Yes or No) Branch_of_Service War Date_Entered Date_Seperated Highest_Rank VA_Marker Embelm VA_Marker_Setter VA_Benefits Tribe (y/n) Tribe_Type (name of tribe) Hispanic (y/n) Hispanic_Type (name of hispanic culture) Race Education College NOK_First_Name NOK_Middle_Name NOK_Last_Name NOK_Address NOK_MailAddress NOK_City NOK_State NOK_Zip NOK_Phone NOK_Relationship NOK_Email Cemetery Cemetery_City Cemetery_County Cemetery_State Cemetery_Section Cemetery_Lot Cemetery_Grave Interment_Type Intermen_Date Last_Date Crematory Crematory_Address Crematory_City Crematory_State Crematory_Zip Death_Date Death_Time Death_Date_Est Death_Date_Same Plus_24 FH_Date_Notified FH_Hour_Notified Institution (name of hospital or nursing home) Insitution_Address Institution_Lic Death_City Death_City_CVT (city, village, township) Death_County Death_State Death_Zip Patient_Status (inpatient, outpatient, er, etc) Hospice Death_Mannor (natural, suicide, accident etc.) Pronouning_Dr Pronouncing_Type Medical_Cert (person signing death certificate) Medical_Addr Medical_City Medical_State Medical_Zip Medical_Lic Signing_Type C1 C1Time Causes of death... (yes this could be made into seperate table) C2 Smoke (y/n) Pregnant (y/n) Autospy (y/n) Coroner_Notified Cooner_Name Coroner_Addr Coroner_City Coroner_State Coroner_Zip Coroner_County Coroner_Reason Cremation_County Cremation_Coroner Cremation_Coroner_Title Injury_Place Injury_Date Injury_Hour Injury_Work Injury_Addr Injury_City Injury_County Injury_State Injury_Describe1 Injury_Describe2 FH FH_Address FuneralDirector FD_License FH_Phone Service_Package Service_Package_Price Vault_Name Vault_Cost Casket_Name Casket_Cost Certified_Copies CC_Cost Memorial_Card Memorial_Card_Verse Service_Type Service_Time Service_Date Service_Place Service_City Service_State Visitataton_Day Visitation_Hours Visitation_Place Frat_Service Minister Organist Luncheon Lunch_Organizer Lunch_Plates well, you need to define your entities, which will move some groups of fields into their own tables. those tables will be the "parent" tables in a 1:n relationship with your tblVitals (data that describes the deceased person). examples: tblCoroners (all the fields that describe a coroner) tblCemeteries (fields describing the cemetery) tblCrematories (ditto previous) tblInstitutions (the placed where the person died, i'm guessing) tblMedicals (fields describing the person who signed the death certificate; i'm assuming that is the purpose of the fields prefixed with "Medical_") i realize that the above tables may include records that have a relationship with only one record in tblVitals - but a lot of your business is probably local, and many of those parent records *will* be associated with multiple records in tblVitals. some other groups of fields should be subclassed into their own tables (because they will not apply to all deceased persons), which will then correctly have 1:1 relationships with tblVitals. examples: tblMarriages tblMilitaryService tblLuncheons tblInjuries and a couple groups of data *could* have a n:1 relationship with tblVitals, so should be in their own separate "child" tables. example: tblNextOfKin tblVisitations also, since you have so many tables that include city, state, zip (and some with county also), you might want to consider investing in a national zipcode data table. (you can get that data from the USPS, of course; and there are also companies that sell the data, with varying levels of additional data, such as http://www.zipcodedownload.com/) you could use the zip code table as a supporting table, enabling you to save only a single foreign key into your other tables - rather than city, county, state, zip code data fields in each table. i also wonder if you need a way to accommodate foreign cities/countries info for those persons who were born and/or lived and/or died outside the US. the above suggestions aren't meant to be exhaustive, and may not always be on target (if i've made incorrect assumptions). but hopefully they'll give you some ideas on how to approach your data, and issues you may need to consider. hth > Thank you for your help > Here is the list of items in my Big table... [quoted text clipped - 148 lines] > Lunch_Organizer > Lunch_Plates >some other groups of fields should be subclassed into their own tables >(because they will not apply to all deceased persons), which will then [quoted text clipped - 4 lines] >tblLuncheons >tblInjuries Excellent advice, Tina. I'd go even further: people these days very often have multiple marriages; someone could have served in more than one military branch; there might be multiple funeral luncheons (see the suggestion about multiple marriages... :-{( ); someone could have suffered multiple injuries. Again, in practice these might USUALLY be one to one - but all it takes is one exception to cause major unnecessary headaches! Jake, listen to Tina. She's giving you a much better design. You may consider your table to be normalised: it emphatically is NOT. John W. Vinson[MVP] thanks, John. you're right, of course (no surprise there!) <g and bow> i was hoping that one or more of the "table kings" (you and Lynn pop into mind immediately) was watching this thread; i knew y'all wouldn't let me go too far wrong! <bg> i wondered about the etiquette of listing prior spouses; the military branches should have been obvious (duh); ditto the injuries; and i have to plead ignorance about the funeral luncheons - never heard of them. hopefully between the two of us, we've gotten Jake pointed down the right path! :) > >some other groups of fields should be subclassed into their own tables > >(because they will not apply to all deceased persons), which will then [quoted text clipped - 17 lines] > > John W. Vinson[MVP] >hopefully between the two of us, we've gotten Jake pointed down the right >path! :) I hope so - I'm just standing on the sidelines cheering while you're doing the real work! <g> John W. Vinson[MVP] <touchdown! crowd goes wild> ;) > >hopefully between the two of us, we've gotten Jake pointed down the right > >path! :) [quoted text clipped - 3 lines] > > John W. Vinson[MVP] Thank you very much Tina and Hi John - your help is getting me closer to have a kickbutt database. Thanks again. Jake you're welcome, Jake! :) > Thank you very much Tina and Hi John - your help is getting me closer > to have a kickbutt database. Thanks again. > Jake |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.