Perform Multi-table Query??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

using Access 2003; building a tracking database to track projects through a
long process.
Inheritied and Imported 11 tables from Excel.

Want to query the following tables: the USS and TWPS tables have many more
fields, but these are the important ones here.

tblReviewers tblUSS tblTWP
--------------- ------------ ------------
Initials (PK) USSid (PK) TWPid(PK)
Fname initials initials
Lname

Relationships have been established.

GOAL: Return BOTH the USS's and TWP's based on a particular reviewer.

When I build the query in Design View, the 2 relationship lines appear as
one (tblReviewer) to many. This is what I expect.
In the tblReviewers CRITERIA, I ask for the reviewer's initials.

The results returned are not correct.
I have experimented with the JOIN TYPEs but still cannot get the expected
results.
As a check, I did build 2 separate queries and the results returned are
correct.

Any help is appreciated!
 
See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)



Matt K. said:
using Access 2003; building a tracking database to track projects through
a
long process.
Inheritied and Imported 11 tables from Excel.

Want to query the following tables: the USS and TWPS tables have many more
fields, but these are the important ones here.

tblReviewers tblUSS tblTWP
--------------- ------------ ------------
Initials (PK) USSid (PK) TWPid(PK)
Fname initials initials
Lname

Relationships have been established.

GOAL: Return BOTH the USS's and TWP's based on a particular reviewer.

When I build the query in Design View, the 2 relationship lines appear as
one (tblReviewer) to many. This is what I expect.
In the tblReviewers CRITERIA, I ask for the reviewer's initials.
Click to expand...
In effect, you are creating a Many-to-Many link between tblUSS and tblTWP.
This is why the returned Recordset is not correct.



The results returned are not correct.
I have experimented with the JOIN TYPEs but still cannot get the expected
results.
As a check, I did build 2 separate queries and the results returned are
correct.

Any help is appreciated!
Click to expand...
From your description, I think you need a Union Query combining the 2
"separate" Queries.

In addition, you may be able to do all this in one Union Query rather than
using 2 "separate" Queries as the sources for the Union Query.

Check Access Help on Union Queries.
 
The problem is that it is not logical to build a query containing two
independent 1-many relationships. The result is a partial Cartesian
product. tblUSS has a relationship to tblReviewers and tblTWP has a
relationship to tblReviewers but tblUSS and tblTWP have no relationship with
each other. By that I mean that tblUSS and tblTWP are independent. A row
in one doesn't have a related row in the other. Just because all three
tables contain a common field does not indicate that it makes sense to join
them in a single query.

If you want to produce output from all three tables, you need to create a
report with two subreports - one for tblUSS and the other for tblTWP.
 
Thank you for the comments...
This is my first go around with a relational database...definitely a
learning process. I have spent at least a week giving thought to the
organization of data into tables, came up with something, and now testing the
design. I was mistaken about how relationships work---as evidenced by the
preceeding post. Sounds like UNION QURIES are basically a work around for a
not-so-good database design. I am rethinking my design!
THANKS AGIAN TO BOTH FOR RESPONDING.
 
A union query isn't the solution to your problem. Union are useful for
stacking one recordset behind another. That means that the columns have to
be in the same order and contain the same type of data. You can't union
unlike recordsets.

I don't know what USS and TWP are but having separate tables for them is
probably correct.

Why do you have to build a single recordset that contains data from the
three tables?
 
Back
Top