Access 2000-Filtering for specific records using SQL

[shortone]

Sorry this is so long! One of the directors in our office asked me to help him with the problem below, but I'm stumped as to how to get the specific information he is seeking. I'm submitting his question to me as written - if you need clarification, please let me know. Thanks in advance!

Here is Scott's message:

Basically, the structure of the Db is:
tblDistricts table: contains info on all districts, district code is key link for other tables
tblProjects: contains info on all projects for all districts
other tables are supporting info. all linked within the Query to the district code/number fields in the various tables.

I want to produce a query that shows all districts in 7 of the 21 counties. Then, where districts have projects, the information on the projects (costs, count, etc) is presented in sum total form. Each of these districts should be reflected whether they have projects or not.

The WHERE clause is intended to filter out the projects that have costs entered but which were subsequently voided thereby giving me summary information on all a district's current projects. I have a report, which rolls this together with the other district info to yield an overall current picture of each district under my review.

The Query produces the result of ALL districts, and summarizes projects for those that have them without the WHERE clause. However, the dollars include voided project dollars so the number is not current.

When I add the WHERE clause - to filter our voided projects, the query limits the results to only those

districts with projects instead of the entire set of districts for the seven counties.

(From Shortone): Here is his SQL statement as he sent it to me:

SELECT DISTINCTROW tblDistricts.[County Code], tblDistricts.[District Code], tblDistricts.District, tblDistricts.[District Type], tblDistricts.[Fund Percent], Sum(tblProjects.[Total Project Cost]) AS [Sum Of Total Project Cost], Sum(tblProjects.PEC) AS [Sum Of PEC], Sum(tblProjects.FEC) AS [Sum Of FEC], Sum(tblProjects.[State Share]) AS [Sum Of State Share], First(tblProjects.Void) AS [First Of Void], First(tblProjects.ReferendumFail) AS [First Of ReferendumFail], Sum(tblProjects.UHElemUsed) AS [Sum Of UHElemUsed], Sum(tblProjects.UHMidUsed) AS [Sum Of UHMidUsed], Sum(tblProjects.UHHighUsed) AS [Sum Of UHHighUsed], Sum(tblProjects.NewSF) AS [Sum Of NewSF], tblCounties.County, tblDistrictInfo.Grades, tblDistrictInfo.UnhousedElem, tblDistrictInfo.UnhousedMid, tblDistrictInfo.UnhousedHigh, tblDistrictInfo.TotalEnrollment, tblDistrictInfo.Amendments, [tblLRFPData-import].FDL, [tblLRFPData-import].DFDL, [tblLRFPData-import].Incomplete, Count(*) AS [Count Of tblProjects]
FROM (tblCounties INNER JOIN ([tblLRFPData-import] INNER JOIN (tblDistricts INNER JOIN tblDistrictInfo ON tblDistricts.[District Code] = tblDistrictInfo.[District Code]) ON [tblLRFPData-import].[District Number] = tblDistricts.[District Code]) ON tblCounties.[County Code] = tblDistricts.[County Code]) LEFT JOIN tblProjects ON tblDistricts.[District Code] = tblProjects.[District Code]
WHERE (((tblProjects.[Void])=No) AND ((tblProjects.[ReferendumFail])=No))
GROUP BY tblDistricts.[County Code], tblDistricts.[District Code], tblDistricts.District, tblDistricts.[District Type], tblDistricts.[Fund Percent], tblCounties.County, tblDistrictInfo.Grades, tblDistrictInfo.UnhousedElem, tblDistrictInfo.UnhousedMid, tblDistrictInfo.UnhousedHigh, tblDistrictInfo.TotalEnrollment, tblDistrictInfo.Amendments, [tblLRFPData-import].FDL, [tblLRFPData-import].DFDL, [tblLRFPData-import].Incomplete
HAVING (((tblDistricts.[County Code])=19 Or (tblDistricts.[County Code])=21 Or (tblDistricts.[County Code])=23 Or (tblDistricts.[County Code])=25 Or (tblDistricts.[County Code])=29 Or (tblDistricts.[County Code])=35 Or (tblDistricts.[County Code])=39));

 

[rac2]

Maybe put the conditions in the JOIN instead of in a WHERE clause. Here is the pertinent code as it is now

 . . .
LEFT JOIN tblProjects ON tblDistricts.[District Code] = tblProjects.[District Code]
WHERE (((tblProjects.[Void])=No) AND ((tblProjects.[ReferendumFail])=No))
 . . .

Try this

 . . .
LEFT JOIN tblProjects ON  (  tblDistricts.[District Code] = tblProjects.[District Code]
AND (((tblProjects.[Void])=No) AND ((tblProjects.[ReferendumFail])=No))  )
 . . .

Or try JOINing a subquery like this

 . . .
LEFT JOIN (  SELECT * FROM tblProjects 
            WHERE (((tblProjects.[Void])=No) AND ((tblProjects.[ReferendumFail])=No))  ) projectsOfInterest

ON tblDistricts.[District Code] = projectsOfInterest.[District Code]
 . . .

This last approach will require using the subquery alias projectsOfInterest instead of the table name where you refer to columns in tblProjects.

Just some ideas.

 

[shortone]

Thanks so much! Will definitely give it a try! Appreciate your quick response (and your patience reading such a long post!