Re: Problem with SUM Query
- From: "dd" <dd.dd>
- Date: Wed, 31 May 2006 15:25:36 +0100
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
"Jerry Whittle" <JerryWhittle@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:294A9841-DB41-4711-BE23-16809BC79971@xxxxxxxxxxxxxxxx
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" wrote:
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.
"Jerry Whittle" <JerryWhittle@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:86723CD8-4B5C-4975-9055-CD8E328FDB98@xxxxxxxxxxxxxxxx
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" wrote:
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;
.
- References:
- Problem with SUM Query
- From: dd
- Re: Problem with SUM Query
- From: dd
- Re: Problem with SUM Query
- From: Jerry Whittle
- Problem with SUM Query
- Prev by Date: Re: Why do Access queries stop working if I use more than 2 criter
- Next by Date: Re: Select everything but the last three numbers in the field query
- Previous by thread: Re: Problem with SUM Query
- Next by thread: Re: Problem with SUM Query
- Index(es):
Relevant Pages
|
|
