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.
Thanks.
DP
dpawarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dpawarAuthor Commented:
Can you create different types of relationships in SQL server?  Like one to one, one to many etc?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

See this link:
http://www.eggheadcafe.com/community/aspnet/13/10047823/create-relationship.aspx

0
dpawarAuthor Commented:
Once I create the relationships, when I create a Sample ID, will the Result table automatically have SampleID because of reationship?
0
Jeffrey CoachmanMIS LiasonCommented:
No.

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.
0
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.
DP
Relationship.jpg
LogIn.jpg
0
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.
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c6661372-8dbe-422b-8676-c632d66c529c

;-)

JeffCoachman
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.