Test reports and standards

[Seth]

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

 

[Tim Ferguson]

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.

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

 

[Seth]

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

 

[Seth]

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

 

[Tim Ferguson]

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

 

[Tim Ferguson]

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