What relationship and fields to create in Results Table

I have already created following tables
1. Sample Info  (Fields: SampleID, Sample Name, TestCode)
2. Sample Results (Fields: ResultsID, SampleID, ???)
I dont know what fields I need to create here.  Each SampleID can have multiple TestCode so accordingly that many number of record needs to be created in Sample Results table so Results can go there for respective tests.  I am using SQL server 2005 back end and Access 2010 Front End.
Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
create three tables with 1 to Many  and  Many to 1 relationship as shown below

Info                         Results                      Test
SampleID  1 - M      SampleID
                               TestID     M --- 1        TestID
                                ResultID (optional)   TestCode

.. just add the other fields on Tables Info and Test
dpawarAuthor Commented:
Can you create different types of relationships in SQL server?  Like one to one, one to many etc?
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Rey Obrero (Capricorn1)Commented:
dpawarAuthor Commented:
How to create different types of relationships in SQL server?
Jeffrey CoachmanMIS LiasonCommented:
<How to create different types of relationships in SQL server?>

See this link:

dpawarAuthor Commented:
Once I create the relationships, when I create a Sample ID, will the Result table automatically have SampleID because of reationship?
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:

If one SampleID can have many "Results", then you must enter/select the associated Sample ID when you create the Result Record.

In the same way that if you create a Customer (Parent) record, an associated "Order" (Child) record is not created automatically.
dpawarAuthor Commented:
Hi!  Good to have you back,
I have attached the relationship Diagram.  How can I add SampleID in Result table? Should I have realtionship of TestCodeID with some other table?  How can I create a record in Result Table when SampleID is created when Samples are logged in?  I am using Access 2010 as front end and SQL server 2005 as back end.

I have attached sample Log In Form as well.
Thanks for your continued help.
Jeffrey CoachmanMIS LiasonCommented:
With all due respect, capricorn1 and I have already addressed your original question.
So in this case, it is fitting that you assign points here, close this question, and post your new issue as a new question.
Sound fair?

What you are asking now is an "Architectural" issue that has more to do with the interface layer than the design layer.
You can examine the "Orders" form in the Northwind sample Database for info on selecting a parent key when creating a Child record.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.