 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / July 2005Test reports and standards
I am trying to create relationships between tables to do the following but Access dose not create the type of relationship I require. I need to get my table design correct so that Access knows what sort of relationship should be created. I’ve spent hours trying to work this out so any suggestions will be appreciated. Each test report can relate to many standards Each standard can have many issue dates Each issue date can have many amendments For each test report I will need to record the standard name, the issue date and the amendment number. Example of a standard name: IEC60335-1:2004 A1 (Standard name : Issue date : Amendment) So , when the user is completing the form they will select a standard, then select an issue date, and finally select an amendment number. A test report will not necessarily be to the latest issue of the standard. Following is my current table structure: tblTestRprts fldTestRprtID (+ other fields) tblTestRprtStds fldTestRprtStdID fldTestRprtStdRefNum fldStdDate fldStdAmd tblStdRefNum fldStdRefNumID fldStdRefNum fldStdTitle fldStdType fldStdDate tblStdDates fldStdDateID fldStdDate fldStdDateAmd tblStdAmds fldStdAmdID fldAmdNum fldStdAmdDate Thanks for any help, Seth > Each test report can relate to many standards > [quoted text clipped - 4 lines] > For each test report I will need to record the standard name, the > issue date and the amendment number. I think this is going to be one of those Artificial Keys vb Natural Keys debates... still here's my pennyworth. As an aside, I cannot cope with unneccesary hungarian in database objects, so I am doing a bit of renaming too: TestReports (*ReportCode, etc) Standards (*ReportCode, *StandardName, WhetherMet, etc) Foreign Key ReportCode references TestReports Issues (*ReportCode, *StandardName, *IssueDate, ReceivedBy, etc) Foreign Key (ReportCode, StandardName) references Standards Amendments(*ReportCode, *StandardName, *IssueDate, *AmendNum, FileName, FolderPath, etc) Foreign Key (ReportCode, StandardName, IssueDate) ref Issues Note: you don't need a FK constraint between, for example, Amendments.ReportCode and TestReports.ReportCode -- you already can't have an amendment that belongs to a nonexistent testreport because of the other relationships. For the GUI, which I think you were really asking about, you can look up Google Groups on this group for cascading combo boxes, but it's fairly easy with a simple design like this. Hope that helps Tim F Thanks for your help Tim. I take it that '*' indicates a primary key? Also, how do I make a foreign key with multiple fields? 'Foreign Key (ReportCode, StandardName) references Standards' Is it simpley a matter of draging and dopping in the relationships window? Cheers, Seth > > Each test report can relate to many standards > > [quoted text clipped - 34 lines] > > Tim F > I take it that '*' indicates a primary key? Yes that's right. > Also, how do I make a foreign key with multiple fields? 'Foreign Key > (ReportCode, StandardName) references Standards' Is it simpley a > matter of draging and dopping in the relationships window? Yes again: just ctrl-click all the relevant fields in the FK table and drag them toward the PK table. More below... Tim F Please correct me if I’m wrong but with this table design it is not possible to have a standard in the database if it is not referenced by a test report? ie. every standard must have a at least one related test report. If I want standards in the database which are not references by a test report do I have to brake the link between TestReports and the other tables: The problem with this is that if I correct an entry in Standards, Issues, or Amendments it is not updated in Standards ------------------------------ TestReports (*ReportCode, etc) Standards (*ReportCode, *StandardName, *IssueDate, *AmendNum) Foreign key ReportCode references TestReports ------------------------------ ------------------------------ StandardName (*StandardName, WhetherMet, etc) Issues (*StandardName, *IssueDate, ReceivedBy, etc) Foreign Key (StandardName) references StandardName Amendments(*StandardName, *IssueDate, *AmendNum, FileName, FolderPath, etc) Foreign Key (StandardName, IssueDate) ref Issues --------------------------------- Regards, Seth > > Each test report can relate to many standards > > [quoted text clipped - 34 lines] > > Tim F > Please correct me if I'm wrong but with this table design it is not > possible to have a standard in the database if it is not referenced by > a test report? ie. every standard must have a at least one related > test report. Okay: I made some assumptions in your original post. When you said ]] Each test report can relate to many standards I assumed that every standard belongs to a testreport; and etc down through the heirarchy. If this ain't so, then the model will have to be different, because you can't have a NULL in a primary key. > If I want standards in the database which are not references by a test > report do I have to brake the link between TestReports and the other > tables: Not exactly, but it means that the PK of the Standards cannot include the ReportCode if some standards don't have a reportcode to include. Take a look at something like: TestReports (*ReportCode, etc) Standards(*StandardName, ReportCode(can be Null), etc) Foreign Key (ReportCode) references TestReports Issues (*StandardName, *IssueDate, ReceivedBy, etc) Foreign Key (StandardName) references Standards The design of Issues implies that all issues belong to a standard, but that standard may not have a report, etc. > Amendments(*StandardName, *IssueDate, *AmendNum, FileName, > FolderPath, etc) > Foreign Key (StandardName, IssueDate) ref Issues Like you said :-) One Gotcha is that Access unhelpfully inserts a DefaultValue of zero in the table design window for all numeric values. If ReportCode is numeric then remember to delete this or change it to Null (same thing), since Standards.Reportcode is non-Required, and since a zero value is practically always illegal as a foreign key (even autonumbers start at one and work upwards). Hope that helps Tim F |
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.