473,326 Members | 2,076 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Query relationships

31
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?
Apr 15 '10 #1
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?
Apr 15 '10 #2
tomric
31
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.
Apr 15 '10 #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.
Apr 15 '10 #4
tomric
31
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.
Apr 15 '10 #5
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.
Apr 15 '10 #6
tomric
31
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.
Apr 16 '10 #7
patjones
931 Expert 512MB
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
Apr 16 '10 #8
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.
Apr 20 '10 #9
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.
May 3 '10 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

2
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
4
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
8
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...
4
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...
0
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...
2
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
1
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...
1
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...
7
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...
6
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
1
isladogs
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...
0
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...
1
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)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....
0
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
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.