Software

Ask something
Question Locked

Table Relationships Access 2007

0 helpful
6 replies

by grhbiz

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a database that my employer has assigned to me to create. I have three pieces of information that end users need to be able to: A. search by B. have autopopulated in forms C. One is unique: pipe number only one number is assigned to each piece of pipe. Coil number is assigned to many pipe numbers: coil number One is assigned to many coil and pipe numbers: heat I have 10 lab tests that are performed on each of those items(coil, pipe and heat). I have set up the lab tests as individual tables and each must have coil, pipe and heat in the table. I need to have coil, pipe and heat associated with one another so that when the end user searches by or enters one of the three the other two fields will populate or remain empty (if not in database). I am stumped, I cannot figure out how to join the tables to auto populate and do I put coil/pipe/heat in one table? They are related to one another. Pipe is the only unique number in the database. I am working under a strict time deadline. HELP! I am using Access 2007 and I am not an advanced Access user.

6 replies

by robo_dev

So 'coil' is some sort of thing like a coil of wire, 'pipe' is a sample of pipe, and 'heat' is a heater of some sort??

And the lab test results are your data for each item?

In general, I would create a table for coil, a table for pipe, and a table for heat. I would assign a unique value for each test, and use that as the key for each table.

Alternately, a table of test results, with the columns being coil/pipe/heat, and the record being the test number (assuming you test all three at the same time).

You then would create a view which would be a stored query to get the data back out.

by grhbiz

Sorry for the confusing post. First, we are a sprial pipe mill. We receive in thousands of coils of steel. Each coil has a particular alpa-numeric code. We perform 10 lab tests on each coil of steel. We then take the steel coil and flatten it to produce spiral pipe (which also has an alpha-numeric code. The pipe is then tested in certain sequences (each chosen by the particular customer) such as, every 5th pipe is tested to assure quality. (The same 10 tests that are performed on the steel coils.) Each one of these tests is then called a heat. Each heat also has an alpha numeric code. Each item (coil, pipe, and heat) has the same 10 lab tests performed on them. Each pipe is a unique number, but there can be many pipe associated with coil and heat. But the data base is based on heat numbers. Each lab test can be performed on a particular pipe/heat/coil numerous times. I have set up individual tables for each test. But I am confused as to how to set up the pipe/coil/heat numbers. The coil number stays with each pipe and the heat number also stays with each pipe. I would like to have the coil/pipe/heat numbers auto populate in the test tables(forms) for the end user. Then if one item (coil/pipe/heat) is missing from the test, the end user would know. Also, I would like for the end user to be able to enter (coi, pipe or heat) and have the associated information populate the fields in the record. I do not know how to structure this. I have tried to creat one table with coil, pipe and heat in it as they are uniquely associated with one another. I do not see how I can keep the coil, pipe and heat numbers together if I create a separate table for each. I also cannot resolve how I place the coil, pipe, and heat numbers in each individual lab test. Do I place the fields in the table (I have an existing spreadsheet that precedes this database and it is set up (each row) with pipe number, coil, number and heat number; then each lab test follows on the same row. But the records for each lab test are different. Did this clarify the confusion for you?

by robo_dev

thinking out loud:

forgive me if this sounds too preachy, but at a high level, the first thing we need to do is establish the tables, decide what will be their primary keys, and make sure the whole relationship makes sense, correct?

The first issue is to address the one-to-many relationship between coils and pipes. Effectively the 'Coil table' would be the master table of the database, since there is a one-to-many relationship bewtween coils and pipes, the coil table is the only way to keep track of that.

I would start with some sort of a Coil table which allows you to track what pipes belong to what coil. The key would be the coil alpha code. Attributes of the table would be the heat numbers, the pipe numbers.

Then a pipes table, to track what heat numbers (tests) were done to what pipe.

Finally a 'heat table' with attributes such as type (pipe or coil) heat number, date, customer, test result, etc.

It does not matter is the lab test records are different, as long as there is a place to enter the data for each test. Unless it is totally impossible, it would be much simpler to have a 'one size fits all' test result table.

Thinking more out loud, it would be helpful if your numbering system were such that pipe numbers are subsets of coil numbers. So that the pipes produced from coil 1234 would be numbered as 1234-1 1234-2, etc. This would make queries and sorting much easier....

Not sure if this is at all helpful or not....

by grhbiz

Yes it does sound like a sump the db expert exam question! Thanks for the table structure suggestion, I am going to try it.
Although, our pipe/coil/heat numbering system is already in use and also being used in a manufacturing pipe tracking software....I have to use what is already in place. If I make the coil number the pk in the first table....what would the pk be in the pipe table and the heat table? I am also going to try and make the lab tests one table (I can always put the individual lab tests in separate forms). In the lab test table what would you suggest as the primary key?

by robo_dev

so the only thing that would work, I think, would be the pipe tracking number.

Also the heat number would have to be the primary key for that table.

The primary key would be the link between the tables.

http://databases.about.com/cs/administration/g/primarykey.htm

I would see if you can export or link data from your pipe tracking software if you can...if it uses a standard database, that would save you tons of time and also ensure data integrity.

This discussion is closed to new replies.