D
dd
Hi,
In my building inspection database, I've created a query, based on a single
table, that adds up the values for repair works (Capital Cost) at each site
and based on my expression, assigns an overall condition category for each
site.
SELECT DISTINCTROW SurveyData.[Date of Survey], SurveyData.Site,
Sum(SurveyData.[Capital Cost]) AS [Sum Of Capital Cost], IIf([Sum Of Capital
Cost]>=10000,"C",IIf([Sum Of Capital Cost]<10000,"B")) AS [Overall
Condition]
FROM SurveyData
WHERE (((SurveyData.Condition)="c"))
GROUP BY SurveyData.[Date of Survey], SurveyData.Site
ORDER BY SurveyData.Site;
Each site is located within a subdivision and I want to group the sites by
their SubDivision in the Report. When I try to add this field from another
table the results either get screwed up showing multiple subdivisions for
each site, incorrect Capital Cost sums, or the query returns with zero
results.
SELECT DISTINCTROW SurveyData.[Date of Survey], Table2.SubDivision,
Table1.Site, Sum(SurveyData.[Capital Cost]) AS [Sum Of Capital Cost],
IIf([Sum Of Capital Cost]>=10000,"C",IIf([Sum Of Capital Cost]<10000,"B"))
AS [Overall Condition]
FROM SurveyData INNER JOIN (Table2 INNER JOIN Table1 ON Table2.ID =
Table1.Table2_ID) ON SurveyData.ID = Table1.ID
WHERE (((SurveyData.Condition)="c"))
GROUP BY SurveyData.[Date of Survey], Table2.SubDivision, Table1.Site
ORDER BY Table1.Site;
In my building inspection database, I've created a query, based on a single
table, that adds up the values for repair works (Capital Cost) at each site
and based on my expression, assigns an overall condition category for each
site.
SELECT DISTINCTROW SurveyData.[Date of Survey], SurveyData.Site,
Sum(SurveyData.[Capital Cost]) AS [Sum Of Capital Cost], IIf([Sum Of Capital
Cost]>=10000,"C",IIf([Sum Of Capital Cost]<10000,"B")) AS [Overall
Condition]
FROM SurveyData
WHERE (((SurveyData.Condition)="c"))
GROUP BY SurveyData.[Date of Survey], SurveyData.Site
ORDER BY SurveyData.Site;
Each site is located within a subdivision and I want to group the sites by
their SubDivision in the Report. When I try to add this field from another
table the results either get screwed up showing multiple subdivisions for
each site, incorrect Capital Cost sums, or the query returns with zero
results.
SELECT DISTINCTROW SurveyData.[Date of Survey], Table2.SubDivision,
Table1.Site, Sum(SurveyData.[Capital Cost]) AS [Sum Of Capital Cost],
IIf([Sum Of Capital Cost]>=10000,"C",IIf([Sum Of Capital Cost]<10000,"B"))
AS [Overall Condition]
FROM SurveyData INNER JOIN (Table2 INNER JOIN Table1 ON Table2.ID =
Table1.Table2_ID) ON SurveyData.ID = Table1.ID
WHERE (((SurveyData.Condition)="c"))
GROUP BY SurveyData.[Date of Survey], Table2.SubDivision, Table1.Site
ORDER BY Table1.Site;