I wrote a union query, below.
It runs fine on my local copy of the database (on my c: drive).
It does NOT run on the network version of the same database (with tables linked to SQL server) - it looks like it's producing results - I can see a screenful of the output, but only to completely freeze Access.
Either part of the union query runs fine by itself, I tested.
your baffled student,
l
SELECT
FeatureRatings.Feature, FeatureRatings.Rating, IIf([featureratings]![rating]="a",0,IIf(([featureratings]![rating]="u" Or [featureratings]![rating]="u/s"),1,Null)) AS Rating01, Trim(Trim(ConditionTable.Type) & " " & Trim(ConditionTable.Problem) & " " & Trim(ConditionTable.[Location/Comments]) & IIf((IsNumeric([ConditionTable!Number])) And (Val([ConditionTable!Number])>1)," (" & ConditionTable![number] & ")",Null) & IIf(ConditionTable![Extent]>0," (" & Round(ConditionTable![Extent]*100,0) & "%).",Null)) AS [Comment/Hazard], InspectionMain.Comments
FROM (((ALLSITES INNER JOIN InspectionMain ON ALLSITES.[Prop ID] = InspectionMain.[Prop ID]) INNER JOIN FeatureRatings ON InspectionMain.[Inspection ID] = FeatureRatings.[Inspection ID]) LEFT JOIN tempLPName_Dist ON (ALLSITES.District = tempLPName_Dist.District1) AND (ALLSITES.Boro = tempLPName_Dist.Boro1) AND (ALLSITES.PropNum = tempLPName_Dist.PropNum1) AND (ALLSITES.Category = tempLPName_Dist.Category1)) LEFT JOIN ConditionTable ON FeatureRatings.[Feature #] = ConditionTable.[Feature #]
WHERE (((InspectionMain.Date) Between #7/1/2005# And Date()-1) AND ((InspectionMain.Round)<>0) AND ((ALLSITES.Category)="Greenstreet" Or (ALLSITES.Category)="large park" Or (ALLSITES.Category)="Small Park") AND ((FeatureRatings.Rating)="a" Or (FeatureRatings.Rating)="u" Or (FeatureRatings.Rating)="u/s") AND ((InspectionMain.[Mock Inspection?])=0) AND ((InspectionMain.InspectionType)="pip") AND (([ConditionTable].[type] & [ConditionTable].[problem] & [ConditionTable].[Location/Comments]) Is Not Null))
UNION ALL SELECT
FeatureRatings.Feature, FeatureRatings.Rating, IIf([featureratings]![rating]="a",0,IIf(([featureratings]![rating]="u" Or [featureratings]![rating]="u/s"),1,Null)) AS Rating01, Trim(Trim([hazard]) & " " & Trim(HAZARDTABLE![Location/Comments]) & IIf((IsNumeric(HAZARDTABLE![Number])) And (Val(HAZARDTABLE![Number])>1)," (" & HAZARDTABLE![number] & ")",Null) & IIf(HAZARDTABLE![Extent] Is Not Null," (" & HAZARDTABLE![Extent] & ")",Null) & ". P-" & Trim(HAZARDTABLE![priority]) & ".") AS [Comment/Hazard], InspectionMain.Comments
FROM (((ALLSITES INNER JOIN InspectionMain ON ALLSITES.[Prop ID] = InspectionMain.[Prop ID]) INNER JOIN FeatureRatings ON InspectionMain.[Inspection ID] = FeatureRatings.[Inspection ID]) LEFT JOIN tempLPName_Dist ON (ALLSITES.Category = tempLPName_Dist.Category1) AND (ALLSITES.PropNum = tempLPName_Dist.PropNum1) AND (ALLSITES.Boro = tempLPName_Dist.Boro1) AND (ALLSITES.District = tempLPName_Dist.District1)) LEFT JOIN HazardTable ON FeatureRatings.[Feature #] = HazardTable.[Feature #]
WHERE (((InspectionMain.Date) Between #7/1/2005# And Date()-1) AND ((InspectionMain.Round)<>0) AND ((ALLSITES.Category)="Greenstreet" Or (ALLSITES.Category)="large park" Or (ALLSITES.Category)="Small Park") AND ((FeatureRatings.Rating)="a" Or (FeatureRatings.Rating)="u" Or (FeatureRatings.Rating)="u/s") AND ((InspectionMain.[Mock Inspection?])=0) AND ((InspectionMain.InspectionType)="pip") AND ((HAZARDTABLE.[hazard] & [HAZARDTABLE].[location/comments]) Is Not Null));
It runs fine on my local copy of the database (on my c: drive).
It does NOT run on the network version of the same database (with tables linked to SQL server) - it looks like it's producing results - I can see a screenful of the output, but only to completely freeze Access.
Either part of the union query runs fine by itself, I tested.
your baffled student,
l
SELECT
FeatureRatings.Feature, FeatureRatings.Rating, IIf([featureratings]![rating]="a",0,IIf(([featureratings]![rating]="u" Or [featureratings]![rating]="u/s"),1,Null)) AS Rating01, Trim(Trim(ConditionTable.Type) & " " & Trim(ConditionTable.Problem) & " " & Trim(ConditionTable.[Location/Comments]) & IIf((IsNumeric([ConditionTable!Number])) And (Val([ConditionTable!Number])>1)," (" & ConditionTable![number] & ")",Null) & IIf(ConditionTable![Extent]>0," (" & Round(ConditionTable![Extent]*100,0) & "%).",Null)) AS [Comment/Hazard], InspectionMain.Comments
FROM (((ALLSITES INNER JOIN InspectionMain ON ALLSITES.[Prop ID] = InspectionMain.[Prop ID]) INNER JOIN FeatureRatings ON InspectionMain.[Inspection ID] = FeatureRatings.[Inspection ID]) LEFT JOIN tempLPName_Dist ON (ALLSITES.District = tempLPName_Dist.District1) AND (ALLSITES.Boro = tempLPName_Dist.Boro1) AND (ALLSITES.PropNum = tempLPName_Dist.PropNum1) AND (ALLSITES.Category = tempLPName_Dist.Category1)) LEFT JOIN ConditionTable ON FeatureRatings.[Feature #] = ConditionTable.[Feature #]
WHERE (((InspectionMain.Date) Between #7/1/2005# And Date()-1) AND ((InspectionMain.Round)<>0) AND ((ALLSITES.Category)="Greenstreet" Or (ALLSITES.Category)="large park" Or (ALLSITES.Category)="Small Park") AND ((FeatureRatings.Rating)="a" Or (FeatureRatings.Rating)="u" Or (FeatureRatings.Rating)="u/s") AND ((InspectionMain.[Mock Inspection?])=0) AND ((InspectionMain.InspectionType)="pip") AND (([ConditionTable].[type] & [ConditionTable].[problem] & [ConditionTable].[Location/Comments]) Is Not Null))
UNION ALL SELECT
FeatureRatings.Feature, FeatureRatings.Rating, IIf([featureratings]![rating]="a",0,IIf(([featureratings]![rating]="u" Or [featureratings]![rating]="u/s"),1,Null)) AS Rating01, Trim(Trim([hazard]) & " " & Trim(HAZARDTABLE![Location/Comments]) & IIf((IsNumeric(HAZARDTABLE![Number])) And (Val(HAZARDTABLE![Number])>1)," (" & HAZARDTABLE![number] & ")",Null) & IIf(HAZARDTABLE![Extent] Is Not Null," (" & HAZARDTABLE![Extent] & ")",Null) & ". P-" & Trim(HAZARDTABLE![priority]) & ".") AS [Comment/Hazard], InspectionMain.Comments
FROM (((ALLSITES INNER JOIN InspectionMain ON ALLSITES.[Prop ID] = InspectionMain.[Prop ID]) INNER JOIN FeatureRatings ON InspectionMain.[Inspection ID] = FeatureRatings.[Inspection ID]) LEFT JOIN tempLPName_Dist ON (ALLSITES.Category = tempLPName_Dist.Category1) AND (ALLSITES.PropNum = tempLPName_Dist.PropNum1) AND (ALLSITES.Boro = tempLPName_Dist.Boro1) AND (ALLSITES.District = tempLPName_Dist.District1)) LEFT JOIN HazardTable ON FeatureRatings.[Feature #] = HazardTable.[Feature #]
WHERE (((InspectionMain.Date) Between #7/1/2005# And Date()-1) AND ((InspectionMain.Round)<>0) AND ((ALLSITES.Category)="Greenstreet" Or (ALLSITES.Category)="large park" Or (ALLSITES.Category)="Small Park") AND ((FeatureRatings.Rating)="a" Or (FeatureRatings.Rating)="u" Or (FeatureRatings.Rating)="u/s") AND ((InspectionMain.[Mock Inspection?])=0) AND ((InspectionMain.InspectionType)="pip") AND ((HAZARDTABLE.[hazard] & [HAZARDTABLE].[location/comments]) Is Not Null));
