Query Problem - Relationship?

[Golfinray]

I have 3 tables, 06-07 projects, 07-09 projects, project construction. All
three I set School District Name as the primary key. When I go to
relationships, I pop up the three tables and hook them up with School
District Name, no problem. When I build a query with two or three of the
tables, no matter what I do it acts like I don't have good relationships set
because I get 10,000 - 100,000 records which is 9500 - 95,500 more than I
need the query to produce and there will be 30-50 copies of the same record
of each all the way through. It acts like my relationship is not ok but I am
almost sure it is. Help! I have been fighting this thing for a couple of days.

 

[mscertified]

What SQL does your query produce?

-Dorian

 

[Golfinray]

SELECT [06-07 Construction Tracking A].[Project ID#], [07-09 Project
Tracking Area A].[Final Project ID#], [06-07 Construction Tracking
A].[District Name], [06-07 Construction Tracking A].[Project Description],
[07-09 Project Tracking Area A].[Project Description], [06-07 Project
Tracking Area A].[Project Description], *
FROM [06-07 Project Tracking Area A] INNER JOIN ([06-07 Construction
Tracking A] INNER JOIN [07-09 Project Tracking Area A] ON [06-07
Construction Tracking A].[District Name] = [07-09 Project Tracking Area
A].[District Name]) ON [06-07 Project Tracking Area A].[District Name] =
[06-07 Construction Tracking A].[District Name];

 

[rolaaus]

As I was reading your original post, I was almost certain what the problem
is, since you posted the SQl it confirms it.

It appears you are trying to join the Projects in 06-07 with the projects in
07-09. I'm not sure what the relationship of Project Construction table is
with these other 2 tables, but the problem you are getting is because for
every record you have in 06-07 Projects it is matching 07-09 projects based
on the School Disctrict Name.

Here is what you are getting when you join based on these 2 tables.

06-07 Projects (Table)
Project 06-01, Company A
Project 06-02, Company A

07-09 Projects (Table)
Project 071, Company A
Project 072, Company A

In your Joined query, your results will end up being
Projects 06-01, Company A, Project 071
Projects 06-01, Company A, Project 072
Projects 06-02, Company A, Project 071
Projects 06-02, Company A, Project 072

You may not be displaying the fields that are showing you this affect, but
basically every record in 1 table goes out and grabs every record in the 2nd
table that has a matching company name.

Drop the join between 06-07 Projects and 07-09 Projects.

I am kind of wondering why you have 2 seperate Projects table in the first
place. If it is because there are too many records in 06-07 to make the
application efficient, then upgrading to SQL (today, there are free options
with "Express" versions out there, and you can still develop the UI in
Access, or in VB Express), might be in order. If it is for archival
purposes, then there shouldn't be a recurring need to access it on a regular
basis.

SELECT [06-07 Construction Tracking A].[Project ID#], [07-09 Project
Tracking Area A].[Final Project ID#], [06-07 Construction Tracking
A].[District Name], [06-07 Construction Tracking A].[Project Description],
[07-09 Project Tracking Area A].[Project Description], [06-07 Project
Tracking Area A].[Project Description], *
FROM [06-07 Project Tracking Area A] INNER JOIN ([06-07 Construction
Tracking A] INNER JOIN [07-09 Project Tracking Area A] ON [06-07
Construction Tracking A].[District Name] = [07-09 Project Tracking Area
A].[District Name]) ON [06-07 Project Tracking Area A].[District Name] =
[06-07 Construction Tracking A].[District Name];

What SQL does your query produce?

-Dorian