Problem with SUM Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dd - 30 May 2006 17:04 GMT

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;

Reply to this Message

Jerry Whittle - 30 May 2006 19:58 GMT

Try removing the DISTINCTROW clause. It did nothing in the first SQL
statement as it only hit one table.

In the second SQL statement it could bring in more data than you wanted plus
you already have a group by statement.

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Hi,
>
[quoted text clipped - 27 lines]
> GROUP BY SurveyData.[Date of Survey], Table2.SubDivision, Table1.Site
> ORDER BY Table1.Site;

Reply to this Message

dd - 31 May 2006 09:14 GMT

Jerry,

Thanks for your response. I tried this and it probably helps, but I see no
difference in the results. The query returns zero results. I enclose, below
clips of the three tables I am trying to use in the second SQL statement.

The statement now reads:
SELECT 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 Table2.SubDivision, Table1.Site;

Table1
ID SiteLookup to Table2
287 AberdeenNorth East
298 Aberdeen WorkshopNorth East
176 AboyneNorth East
177 AlfordNorth East
204 AlnessNorth West
158 AlvaForth Valley
247 AnnanDumfries & Galloway
162 ArbroathTayside

Table2
SubDivision ID
Argyll & Clyde 1
Ayrshire & Arran 2
Borders 3

SurveyData
ID Date of Survey Site Element Condition Priority
Capital Cost
1448 05/02/2006 Aberdeen 01 B 4
£1000.00

1449 05/02/2006 Aberdeen 02 C 3
£5000.00

etc.

Try removing the DISTINCTROW clause. It did nothing in the first SQL
statement as it only hit one table.

In the second SQL statement it could bring in more data than you wanted plus
you already have a group by statement.

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"dd" wrote:

> Hi,
>
[quoted text clipped - 30 lines]
> GROUP BY SurveyData.[Date of Survey], Table2.SubDivision, Table1.Site
> ORDER BY Table1.Site;

Reply to this Message

Jerry Whittle - 31 May 2006 15:02 GMT

Your sample data does not show any possible links between the tables. For
example you have the following record in SurveyData. It meets the requirement
of SurveyData.Condition)="C". So far so good.

1449 05/02/2006 Aberdeen 02 C 3
£5000.00

However there is no matching SurveyData.ID data to the Table1.ID field.
Further there is no data showing where Table2.ID = Table1.Table2_ID. So
unless your sample data does not depict where there is an actual join between
the tables, you will not get any returns when running the query. Instead you
may need a Left or Right join to return records; however, that could be
difficult while doing a totals query.

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Jerry,
>
[quoted text clipped - 80 lines]
> > GROUP BY SurveyData.[Date of Survey], Table2.SubDivision, Table1.Site
> > ORDER BY Table1.Site;

Reply to this Message

dd - 31 May 2006 15:25 GMT

Table 1.Site is related to SurveyData.Site Table 2.Subdivision is related to
Table 1.Lookup to Table 2

Is this what you mean?

Regards

Dylan Dawson

Your sample data does not show any possible links between the tables. For
example you have the following record in SurveyData. It meets the
requirement
of SurveyData.Condition)="C". So far so good.

1449 05/02/2006 Aberdeen 02 C 3
£5000.00

However there is no matching SurveyData.ID data to the Table1.ID field.
Further there is no data showing where Table2.ID = Table1.Table2_ID. So
unless your sample data does not depict where there is an actual join
between
the tables, you will not get any returns when running the query. Instead you
may need a Left or Right join to return records; however, that could be
difficult while doing a totals query.

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"dd" wrote:

> Jerry,
>
[quoted text clipped - 90 lines]
> > GROUP BY SurveyData.[Date of Survey], Table2.SubDivision, Table1.Site
> > ORDER BY Table1.Site;

Reply to this Message

dd - 31 May 2006 16:29 GMT

Problem solved.

I combined Table 1 and Table 2 into a query "Locations" and added it to my
Overall Condition query. I had to create a relationship within the query. It
seems to work fine.

SELECT SurveyData.[Date of Survey], Locations.SubDivision, Locations.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 Locations ON SurveyData.Site = Locations.Site
GROUP BY SurveyData.[Date of Survey], Locations.SubDivision, Locations.Site
ORDER BY Locations.SubDivision, Locations.Site;

I also removed the "C" criteria, becasue I want the total of all Capital
Costs to be analysed in the expression and not just the Catergory C items.

Thanks !
Dylan Dawson
for and on behalf of
Faithful+Gould
Project Management
Tel: +44 ( 0 )141 220 2200
Direct Dial: +44 ( 0 )141 220 2160
Fax: +44 ( 0 )141 220 2201
Mobile: 07803 260 959
www.fgould.com

Your sample data does not show any possible links between the tables. For
example you have the following record in SurveyData. It meets the
requirement
of SurveyData.Condition)="C". So far so good.

1449 05/02/2006 Aberdeen 02 C 3
£5000.00

However there is no matching SurveyData.ID data to the Table1.ID field.
Further there is no data showing where Table2.ID = Table1.Table2_ID. So
unless your sample data does not depict where there is an actual join
between
the tables, you will not get any returns when running the query. Instead you
may need a Left or Right join to return records; however, that could be
difficult while doing a totals query.

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"dd" wrote:

> Jerry,
>
[quoted text clipped - 90 lines]
> > GROUP BY SurveyData.[Date of Survey], Table2.SubDivision, Table1.Site
> > ORDER BY Table1.Site;

Reply to this Message