 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / July 2006many to many relationship
hi all. I need some information regarding relationships. tables are tblMONTH(monthId,month,...) tblSITE(SiteId,Site,....) tblParameter(Parameterid,parameter,....) here in each month we do samples for different parameters in different sites. so each month will have many sites and each site can be in many months. Similarly each sites have many parameters and each parameter can be tested in many sites. So my problem is Do I need to make 'function' table like tblMonthSite(MSid,monthid,siteid,...) for tblsite and tblMonth and tblSiteParameter(PSid,siteid,parameterid,....) for tblSIte and tblParameter as all three are many to many relationship. or can I make tblMonthSiteParameter(MSPid,monthid,siteid,parameterid,...) is this correct. thanks anil hi all the resullt look like this where january is month,SlAra001,.... are site codes and remianing BOd,pH etc are parameters and x means this parameters in these sites in january. Similar is for other months. Is this 3rd normal form or greater than that.i am bit confused Month SiteCode Parameter January BOD pH SS E.Coli Total P Total N SAR EC SLARA001 x x x x x x x SOARA002 x x x x SOARA004 x x x SOARA005 x x x SOARA008 x x x TTARA001 xx xx xx xx TTARA002 xx xx xx xx GOARA011 x x GOARA012 x x thanks anil >hi all >the resullt look like this [quoted text clipped - 18 lines] >thanks >anil Sorry, that isn't even in second normal form: you have data (paramters) as fieldnames, a classic example of "comitting spreadsheet". I'd change the name of the Month field (it's a reserved word) and even recommend using a Date/Time datatype, MeasurementDate, so you can sort chronologically (month names sort alphabetically of course); in addition, there'll be ANOTHER January, in just a few more months. Try: Sites SiteCode <primary key> <other info about the site> Parameters ParmeterName <e.g. BOD, pH, SS, ...> <info about the paramter, if any> Measurements MeasurementID Autonumber Primary Key MeasurementDate <e.g. #1/1/2006#> Parameter <link to Parameters> Site <link to Sites> Measurement <the value of BOD or whatever the parameter might be> John W. Vinson[MVP] Thanks john for reply I agree with this.With site and parameter it is ok. May be I could not explain properly last time. Actually I need in output format (MEASUREMENT AS MT) as-- MTID - MTDate - Site - Parameter 1 - 1/1/2006(Jan) - ARA001- BOD 2 - 1/1/2006(Jan) - ARA001 -pH 3 - 1/1/2006(Jan) - ARA001- EC 4 - 1/1/2006(Jan) - ARA002 -BOD --------------------------------------- Actually it is schedule which is monthly basis and states that for each month(say January) we have to sample Sites for different parameters. Thus we have different sites in different months and different parameters in different sites in each month. e.g in jan we test ara001 for 5 parameters and feb we test ara001 for 3 parameters and in march we test ara001 for 8 parameters and so on. similarly for Ara002 3 parameters in jan,5 in feb and so on. we don't have any value simply tell that we need to sample for these parameters.This schedule will be fixed for whole year. So this thing is confusing me how to break above table. hope I could expalin this time properly thanks anil One more point as per I have understood all three tables site,month/mt and parameter have many to many relationship with each other. as many months can have many site and many parameters. like jan can have ara001 and bod,ph,ec etc. and similarly ara001 can be in jan ,feb ...... bod ,ph can be in jan ,feb,..... and ara001,ara002 ...... Hope this expalins better. thanks anil |
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.