Problem with SUM Query

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;
 
G

Guest

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.
 
D

dd

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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


dd said:
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;
Click to expand...
 
G

Guest

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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


dd said:
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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


dd said:
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;
Click to expand...
Click to expand...
 
D

dd

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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


dd said:
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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


dd said:
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;
Click to expand...
Click to expand...
 
D

dd

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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


dd said:
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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


dd said:
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;
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Selecting in combo box rather than using, Like [enter] & "*" 4
Print selected records 3
System Resource Exceeded/Query Too Complex 4
Query on two tables with matching null fields 4
Query help - modify SQL to get records 2
Exported query Runs forever, creates huge spreadsheet with no data. 2
Error - CANNOT HAVE AGGREGATE FUNCTION IN EXPRESSION 4
crosstab query fails when based on query with subquery? 6

Top