Relationship Problem

  • Thread starter Thread starter JNariss
  • Start date Start date
J

JNariss

Hello:

I can't figure out why my tables are not connecting right and I know
its a relationship problem. So I thought someone could look at this and
tell me what I did wrong:

Tables:

Request
Request ID (Primary Key)
Name
Address
Etc.

Problem
Problem ID (Primary Key)
Problem Description
Problem Date
Etc.

Test Results
Request ID (Primary Key)
Problem ID (Primary Key)


Okay so I made those tables. And what happens is a user goes in and
fills out the Request Form. A few people go and look at that Request
Form and then go in to add Problems (which will be added into the
Problem Table). So each Request will have multiple problems. Which is
why I created the Junction Table of Test Results using the two other
tables primary keys.

I created one-to-many Relationships from the Request and Problem Tables
to the Test Results table.

When I make a form using the Request ID from the Request Table and All
field from the Problem table it seems as if I can then enter multiple
problems for a request. But.......I can't figure out how to run a
report for each individual Request ID to list the problems entered for
THAT specific Request ID #.

Can anyone help me out here?

Thanks,
Justine
 
In the query grid, add all three tables into the query.
Make sure you have joins from Request to TestResults and from Problem to
TestResults
Select the fields you want returned.

SQL for the report would look something like

SELECT Request.[Name], [Problem].[Problem Description]
FROM (Request INNER JOIN [Test Results]
ON Request.[Request ID] = [Test Results].[Request ID])
INNER JOIN Problem
On [Test Results].[Problem ID] =Problem.[Problem ID]
WHERE Request.[Request ID] = 12345
 
I'm not too sure about SQL I am using Access. However I still can't get
it to work. I have connected the Request Table.Request ID to Test
Results.Request ID and connected Problems.Problem ID to Test
Results.Problem ID creating one-to-many realationships. The only two
fields in the Test Results table are the 2 ID fields with data type as
Number and both set to be the Primary Keys.

Is this correct so far?

-Justine
 
It seems to be correct to this point.

The problem may be what data you actually have in the tables.

Look at the tables directly and check the following:
Are the numbers in the Test Results.RequestID matching numbers in the
Request table?
Are the numbers in the Test Results.ProblemID matching numbers in the
Problems table?

A little check. You can only have one Primary key in a table, although it
can consist of more than one field. I assume that is what you mean by the 2
id fields being the primary "keys". Also, is either one of the two set as
an autonumber field? If so, that is wrong in this setup.

What are the field types of Test Results.RequestID and Request.RequestID;
and of Test Results.ProblemID and Problems.ProblemID?
 
Back
Top