Query relationships

[tomric]

I have three tables that contain data on testing results from the parts we make. Table #1 contains 5 fields used to indentfy each bar. Table #2 contains electrical test data on 14 different tests. Table #3 contains data on 9 different dielectric tests. I need to make a report that contains data from each of these tables. Table #1 has the primary key for each bar #. The problem is I can't use the bar number as the primary key for any of the other tables because each bar can be broken into 42 to 64 different parts. In those other tables each record has the bar number as one field and the part number as the next field, then the rest of the test data. When I write a query on the three tables I get multiple results for each part in the bar. The query is pulling numbers from table #1, then table #2, then #3. It will then pull the same parts number from table #1 and #2 then change to the second part in table #3. I can't write a union query because the amount of fields from each table are different.
Table #1: General Bar Data: Bar # (primary kay), design #, build #, fire date, furnace #
Table #2: Electrical Data; bar #, part #, datetested, phydefect, freq, level, lpri, lsec, rpri, rsec, lkg, Q
Table #3: Dielectric data; bar #, part #, datetested, vhypot, ihypot, thypot, irvol, irres, irtime.

Any suggestions on this relationship problem?

[NeoPa]

Are you saying that Table #2 may have multiple records (14) for each Bar # and Part #? IE. there is no unique key for the table?

[tomric]

Table #2 and table #3 both have multiple records for each bar number. Each bar can have up to 64 parts associated with it, and each part goes through the 10 tests from table #2, and the 6 tests from table #3.

[NeoPa]

So there is no unique key for the records in these tables. That is not a good design (See Normalisation and Table structures for more on this). This is however, not fundamental to your problem.

Essentially your problem is not so much about relationships, as it is about the layout of your report. This is hard to help with until you decide how you want it to be displayed. This is where it gets tricky of course. As you look harder at this you will find that due to the structure you have, it is very difficult to find a structure that makes sense for your data. not just how it can be implemented in Access, but fundamentally with logic at all.

You do need to decide this first though. Until this is done we don't really have a question to mull over.

[tomric]

I konw the table structrues arn't the best, but with numours parts per bar in the testing phase I can't use the bar # as a primary key. I don't know what else to use sine the data data is imported the way from the testing equipment. On the report I need the Bar#, date fired, furnace #, date tested, part#, 5 test results from table #2, and 3 test results from table #3. The reuslts need to be correlated to each part number.

[NeoPa]

Is there not a [Test #] for each different test? This could be added to the [Bar #] and the [Part #] to form a compound key comprising all three fields?

That's still not the most important issue for the progress of this question though.

[tomric]

When the data comes in all I get for indentification is the bar# and part#. That's the problem, I have no way to relate the tables on a one to one relationship. When I do querys on each table I can get the results I need, but I can't seem to find any way to combine the querys to get a cohesive data set.

[patjones]

I think what NeoPa is pointing out here is probably the way to go - adding a Test# column to tables #2 and #3 - and then using the combination Bar# / Part# / Test# you'll have a unique compound key. If the number of bars/parts/tests in tables #2 and #3 is the same, you'd be able to establish a one-to-one relationship - and then a one-to-many relationship going from table #1 to the table #2/#3 combination.

Something I see many people do is use an auto-number for a key column when they don't know what else to do. In this situation, it might be tempting to do so for Test#, should you decide to put a Test# in the tables, but I personally like to advise against auto-numbers. In my opinion it is usually a good thing to tie data in key columns to something real and verifiable, as much as possible. Perhaps you can come up with some of simple, standardized and agreed upon means of numbering these tests.

Pat

[NeoPa]

Tom, may I suggest that we separate out (and ignore for the moment) the indexing/normalisation issue from the more fundamental question of exactly how you want the data to be presented here. Refer back to my earlier post (#4) for a fuller explanation of this. We are still in the situation where we need you to explain, very clearly and precisely, what you want. I don't see how we can help you further without this.

[NeoPa]

I'll share this as Tom hasn't and all participants deserve to know. Tom (The OP) has PMed me to apologise for not responding in the thread but he has reorganised to get around the problem.