Queries to calculate and compare across random table values

[Tango]

First let me start by saying thank you in advance for all the outstanding information and support this forum has offered. This is my first post and this problem has had me tied in knots for months.

Essentially I need to build a report off a query or set of queries to calculate and compare two tables by certain fields. There are many columns in each table but the ones I am concerned with are;

Tables 1 (conducted)
City
State
College
County
Inspection type
user
date conducted

Table 2 (requirements)
City
State
College
County
Inspection type
Quantity required monthly
Quanitity required quarterly

I need to add up how many (inspection type) have been completed for each combination of city,state,college, and county. Then compare that total againts how many were required from table 2 with the same combination of city,state, college, and county.

The problem is that the table 1 values might be different than the table 2 values.

i.e. the requirement might be city 1, state 1, college 7, county null / the table 1 (conducted) records might have several records but they are listed against city 1, state 1, college 7, county 2.

If I ask the query to tell me how many un-fulfilled requirements last month against city 1, state 1, college 7 I need it to tell me WITHOUT concidering county because the combo box on the filter form for county was left blank.


------------

This is the SQL view for what i have so far but it doesnt count correctly. It thinks that if conducted lists a county and the requirement didnt list a county then it doesnt count/match.

SELECT [RILS Builder Listing].Squadron, [RILS Builder Listing].Flight, [RILS Builder Listing].Section AS Sections, [RILS Builder Listing].Requirement, [RILS Builder Listing].[Inspection Type], Sum((nz([CountOfInspection Type]))) AS [Sum], [Inspection Type Listing].[Description of Inspection Type], [Inspection Type Listing].AQL
FROM ([RILS Builder Listing] LEFT JOIN [RILS count] ON ([RILS Builder Listing].Squadron = [RILS count].Squadron) AND ([RILS Builder Listing].Flight = [RILS count].Flight) AND ([RILS Builder Listing].[Inspection Type] = [RILS count].[Inspection Type])) INNER JOIN [Inspection Type Listing] ON [RILS Builder Listing].[Inspection Type] = [Inspection Type Listing].[Inspection Type]
GROUP BY [RILS Builder Listing].Squadron, [RILS Builder Listing].Flight, [RILS Builder Listing].Section, [RILS Builder Listing].Requirement, [RILS Builder Listing].[Inspection Type], [Inspection Type Listing].[Description of Inspection Type], [Inspection Type Listing].AQL
HAVING ((([RILS Builder Listing].Squadron) Like [Forms]![start page]![squadron] & "*") AND (([RILS Builder Listing].Flight) Like [Forms]![start page]![flight] & "*") AND (([RILS Builder Listing].Section) Like [Forms]![start page]![section] & "*") AND (([RILS Builder Listing].Requirement)>=1) AND ((Sum((nz([CountOfInspection Type]))))<[requirement])) OR ((([RILS Builder Listing].Squadron) Like [Forms]![start page]![squadron] & "*") AND (([RILS Builder Listing].Flight) Like [Forms]![start page]![flight] & "*") AND (([RILS Builder Listing].Section) Is Null) AND (([RILS Builder Listing].Requirement)>=1) AND ((Sum((nz([CountOfInspection Type]))))<[requirement])) OR ((([RILS Builder Listing].Squadron) Like [Forms]![start page]![squadron] & "*") AND (([RILS Builder Listing].Flight) Is Null) AND (([RILS Builder Listing].Section) Is Null) AND (([RILS Builder Listing].Requirement)>=1) AND ((Sum((nz([CountOfInspection Type]))))<[requirement]));

 

[spikepl]

You could perhaps start off by normalizing the data, since a given college presumably is the samme college, irrespective of in which table it appears.

 

[Tango]

Correct, a college will have the same name and format regardless of what table and I can get it to understand and seperate the colleges just fine.

 

[spikepl]

So a given college is in a given county/state/city, and that information belongs with the college, and not duplicated in other tables. That's what I mean by normalization. Each of your two tables would then hold the CollegeID rather than the specific info and the college details would then be looked up in tblColleges, as required.

 

[Tango]

Correct.

Table 1 and 2 both store college name etc but those are populated through drop downs on a form that in turn draws valid values from a 3rd table.


Think of table 1 and 2 as a list of historical records. The valid values for the city, state, college, etc are all in a standardized table .

 

[Tango]

table 2 (requirements) stores what you are required to do this month. for example you are required to visit salt lake city in Utah 6 times this month.
In addition to that you are required to visit salt lake city, utah, Weber state university 4 times this month.

Table 1 stores what you actually did. So your historical records might say you did in fact visit Weber state university in salt lake city utah 5 times. But you did not visit salt lake city utah on any other trips.

So the query would be able to tell me that I met and exceeded my table 2 requirement to visit weber state but I still need to visit salt lake city utah one more time to meet my requirement of 6 visits.