Query Problem Help!

[Golfinray]

I have 4 tables, School Districts, Projects, Special Projects, Completed
Projects.
255 items in Districts. 1568 Projects. 219 Special Projects, 299 Completed
Projects.
District ID# is my primary key in all tables.
School Districts and Projects works great.
I want my Special Projects to only be listed by the district and school that
the special project is in. When I add special Projects table it lists a
special projects by every project (all 1568 of them) I only want it to list
by the school that there is a special projects in.
Same problem with Completed projects. I want it to list only once beside the
school it belongs to.
Also, if there is a school listed several times (like there were 3 or 4
Projects at that school, If there is only one Completed Projects at that
school, I only want it listed once.
HELP!!!! Thanks, for any info!!!

 

[Jerry Whittle]

Well first and foremost, the real problem is the three different project
tables. You probably should only have one with the appropriate fields to
denote that a project is completed or something like a CompletedDate field.

I'm having trouble seeing how the District ID could be the PK for all the
tables. How do you join the tables together. In fact, show us the SQL
statements for the various queries. Open the query in design view. Next go
to View, SQL View and copy and past it here.

 

[KARL DEWEY]

I recommend have the following tables -- District, School, Projects.
The Project table to have fields identifying type of project and one to mark
as completed.
Each of these tables then have a relationship of one-to-many to the next
level.
Use a query as source for form/subform to display the District/School or
School/Projects.
The query can have criteria to select current or completed or all projects
for schools.

 

[Golfinray]

SELECT [Districts 2006 2007].DISTRICT, [06-07 Project Table].[School Name],
[06-07 Project Table].[Project Description], Transitional.[Project
Description], AFIRP.[Project Description]
FROM (([Districts 2006 2007] INNER JOIN [06-07 Project Table] ON [Districts
2006 2007].[LEA new] = [06-07 Project Table].[District LEA]) INNER JOIN
Transitional ON [06-07 Project Table].[District LEA] = Transitional.LEA)
INNER JOIN AFIRP ON Transitional.LEA = AFIRP.LEA
ORDER BY [Districts 2006 2007].DISTRICT;

 

[Jerry Whittle]

Is either [Districts 2006 2007].[LEA new] or
[06-07 Project Table].[District LEA]) a primary key field? Same goes with
Transitional.LEA and AFIRP.LEA? Below you said that the District ID# is the
primary key in all tables.

If at least one side of the join isn't a primary key, there's a very good
chance to have unwanted records returned.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

SELECT [Districts 2006 2007].DISTRICT, [06-07 Project Table].[School Name],
[06-07 Project Table].[Project Description], Transitional.[Project
Description], AFIRP.[Project Description]
FROM (([Districts 2006 2007] INNER JOIN [06-07 Project Table] ON [Districts
2006 2007].[LEA new] = [06-07 Project Table].[District LEA]) INNER JOIN
Transitional ON [06-07 Project Table].[District LEA] = Transitional.LEA)
INNER JOIN AFIRP ON Transitional.LEA = AFIRP.LEA
ORDER BY [Districts 2006 2007].DISTRICT;

Well first and foremost, the real problem is the three different project
tables. You probably should only have one with the appropriate fields to
denote that a project is completed or something like a CompletedDate field.

I'm having trouble seeing how the District ID could be the PK for all the
tables. How do you join the tables together. In fact, show us the SQL
statements for the various queries. Open the query in design view. Next go
to View, SQL View and copy and past it here.