Help with database structure

jaf893

Registered User.
Local time
Today, 02:48
Joined
Nov 4, 2004
Messages
19
I am creating a database for my degree project but I can't work out how to structure it properly. Basically I am taking measurements on a number of different transistors and I need to be able to store the test results. There are two parts to the database but I don't know how to link them properly.

Part 1:
Information on test conditions and transistor type. For example:
Type: N, Width: 20, Length: 5 etc

Part 2:
The actual test results:
Code:
VG ID ID2
8.5 2.0 1.0
8.4 1.9 1.1
8.3 1.6 1.2
8.2 1.5 1.4
What is the best way to link the test information to the test results?

I tried using primary keys but unless I have a different primary key for every line in the test results I can't get it to work. There are 160 lines in every set of test results and so thats probably not very practical.

Thanks in advance,

JAF
jaf893@gmail.com
 
You need a key for your test conditions (an autonumber field will do). Then you need a foreign key in your test results.
You can then link all your test results to the test conditions.
i.e.
TblConditions
PK Test_Condition
1 Condition1
2 Condition2

TblResults
FK Test_Result
1 Result1
1 Result2
2 Result3
1 Result4
2 Result5

Hope that makes sense.
 
You need to hold the primary key from the transistor table as a foreign key in the results table. You should have a primary key in the results table that will be different for each result, but that's not what performs the link.

So, if the pk for the transistor you are testing is 206, each test result will hold 206 as the transistor reference.
 
I now have the two tables each with autonumbers for their primary keys but how do I "hold the primary key from the transistor table as a foreign key in the results table"?

eg which menu do I go to etc.

Thanks again

JAF
jaf893@gmail.com
 
What you have is a parent-child table relationship and a one-to-many relationship. (Same relationship, actually.)

In the help files, look up relationships and one-to-many as topics. On forms, you have a parent/child form setup here.

Basically, you have a table of Conditions, each of which should be uniquely identified by an autonumber field. You have a table of Observations, and it really doesn't matter whether that table is autonumbered or not. (It can be and doesn't really hurt for small databases.)

I'm going to create a fake table structure for you that doesn't really look like what you described. I'm just showing principles.

tblConditions
fldCondID, autonumber, prime key of tblConditions
fldTypeCode, text
fldSize, ...
fldLength, ...
etc.

tblObservations
(fldObsID, autonumber, prime key of tblObservations) - this is optional, not bad to have
fldCondID, LONG, foreign key to tblConditions
fldVG, ...
fldID1, ...
fldID2, ...
etc.

Now, an observation corresponds to a test condition. So the condition ID code is stored in every observation corresponding to that condition.

You need a RELATIONSHIP defined in which fldCondID of tblCondition is the ONE side of a ONE-to-MANY with fldCondID of tblObservation. (Note that it is conventional and convenient but not mandatory that the key names are the same.)

Once this relationship is set up, you can build multi-table queries that will AUTOMATICALLY reflect the relationship in a JOIN statement that uses the identified fields.

When you want to display the results in a report, you can build a query that contains the conditions and observations, then make the report "break" on conditions (sorted in any order you wish). The detail section of the report will be the observations. The other stuff can be in the "break" header (actually called the SECTION header.) You can have several layers of breaks on fields that occur in the condition table. Like, break on size, break on width, etc. etc. - nested in the order of your sort. (I.e. if you sort on size first, width second, your breaks must be on size first, width second.)

To see this in a form, you need a parent/child form. Build the child form first. Could be as simple as a datasheet view of your observations. It is NOT required to actually show the foreign key in the child view. You can build this kind of form with a form wizard.

Now build the parent form with the data from the conditions table. Leave a big space on the form for the observation table to become a sub-form. In the properties of the sub-form, there are two properties for the parent field and child field that link the two forms. This is, of course, the CondID used earlier. Be sure to spell it right.

Look in the help files for these topics
parent/child forms; relationships; one-to-many relationships; report sections; report detail sections; report headers and footers; foreign keys

Now, the next issue is that autonumber on the observations table. I might keep it and might not. If these scientific measurements are to be reported in a formal manner, I might very well keep them because of the idea that each scientific observation should be uniquely identified even if a group of observations occur for the same conditions. I.e. your external rules for the experiment might condone a unique identifying code number for each separate observation. If so, I would keep that ObsID even though from the Access point of view, it is a useless field that you would almost never use for anything.

Note that autonumbers will always increase, but not necessarily contiguously, even for a single-user database. Therefore, do NOT assume that consecutive observations (or consecutive condition entries) will have contiguous numbers. The autonumbers on both tables are unique within the table but cannot otherwise be guaranteed to have any specific pattern.
 
Thanks for all your help. I've just got one more question I've been talking to my supervisor and he is keen for me to seperate the test conditions out into a heirarchical structure made of three tables. It makes sense because it will mean that the same data won't need to be typed in several times.

Basically there will now be three tables: Prototype -> Device -> Conditions. There can be a number of different devices within one prototype and a number of different tests within each prototype.

How should I structure the database?

Thanks,

JAF
jaf893@gmail.com

ps I have attached a pdf with a diagram of the database. I'm just not sure how to link it all together in access.
 

Attachments

Users who are viewing this thread

Back
Top Bottom