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?
9 2444 NeoPa 32,556
Expert Mod 16PB
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?
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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.
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.
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 32,556
Expert Mod 16PB
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 32,556
Expert Mod 16PB
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Hitan |
last post by:
Is there a tool/component that is similar to the
query/view designer in .net that can be used on a form so
that at runtime a user can develop their own queries
using this tool?
Cheers
|
by: sheree |
last post by:
I have 3 tables (amoung a few others) in a small access database. The
tables are as follows:
==
AEReport
--------
AEID (PK)
RptCatelog
GCRCID
PatientID
EvntDate
|
by: Matt |
last post by:
Hi all, Thank you for taking the time.
I have a database with 45 tables on it. 44 tables are linked to a main
table through a one to one relationship.
My question is, is there no way i can...
|
by: d.p. |
last post by:
Hi all,
I'm using MS Access 2003.
Bare with me on this description....here's the situation: Imagine insurance,
and working out premiums for different insured properties. The rates for
calculating...
|
by: leavandor |
last post by:
I am trying to design a query that works with a relationship between a
Table and a Query. I am comparing a value in the table with a computed
value inside the query.
The reason for this is that...
|
by: PL |
last post by:
Hi,
I have 2 simple tables, 'Accesses' and 'Partners'.
Partners :
- Id, primary numeric key
- Name of the partner
- other informational fields
Accesses :
- Id, primary numeric key
|
by: jasonlakewhitney |
last post by:
I have an MDB on my PC with 3 linked tables to a SQL database. I only have
permissions on the SQL db to insert and delete records.
The linked tables are LnktblA, LnktblB, LnktblC and the main MDB...
|
by: jpr |
last post by:
Hello,
My database has 5 tables.
WHen I add data to one table, it runs an append query that copies three
records to other 4 tables.
The main table is MASTER. The data I copy are: ID, SSN and...
|
by: J-P-W |
last post by:
Hi, in the past I've spent ages writing VB routines to get around this
problem, is there a simple way?
I have three tables for a membership system:
tblMembership - MembershipNumber; Names etc...
|
by: jsacrey |
last post by:
Hey everybody, got a secnario for ya that I need a bit of help with.
Access 97 using linked tables from an SQL Server 2000 machine.
I've created a simple query using two tables joined by one...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |