Query shows all years and all data

[Wylie C]

I have a query that is supposed to show the totals for each years Timed
miles, all miles, hours ridden and sum of elevation. What I get is each year
shows records five times per year instead of totals for the year. The problem
is in the hours ridden field. This field gets total number of seconds from
anther queries field named Tseconds and calculates total miles, totoal hrs,
min and seconds for each year. The calculations are correct, just the output
results are wrong. Instead, it returns each year 5 times with the hrs, min
and seconds for each year showing up. IE..

Year Timed Miles Ridden All Miles Ridden Hours Ridden Sum of
Elevation
2005 3986.76 4109 153:43:00
0
2005 3986.76 4109 16:29:40
0
2005 3986.76 4109 190:47:30
0
2005 3986.76 4109 249:46:00
0
2005 3986.76 4109 47:57:00
0
2004 2466.50 2531 153:43:00
0
2004 2466.50 2531 16:29:40
0
2004 2466.50 2531 190:47:30
0
2004 2466.50 2531 249:46:00
0
2004 2466.50 2531 47:57:00
0
etc..

Below is the SQL statement I am working with....

SELECT DISTINCTROW Year(Statistics.StatDate) AS [Year],
Sum(Statistics.Mileage) AS [Timed Miles Ridden], Sum(Statistics.[Total
Miles]) AS [All Miles Ridden],
Format((qryYearlyMileageTotalsTrial!SumOfTSeconds\3600),"00") & ":" &
Format(((qryYearlyMileageTotalsTrial!SumOfTSeconds Mod 3600)\60),"00") & ":"
& Format((qryYearlyMileageTotalsTrial!SumOfTSeconds Mod 60),"00") AS [Hours
Ridden], Sum(tblMoreStats.Elevation) AS SumOfElevation
FROM qryYearlyMileageTotalsTrial, Statistics INNER JOIN tblMoreStats ON
Statistics.RecNum = tblMoreStats.RecNum
GROUP BY Year(Statistics.StatDate),
Format((qryYearlyMileageTotalsTrial!SumOfTSeconds\3600),"00") & ":" &
Format(((qryYearlyMileageTotalsTrial!SumOfTSeconds Mod 3600)\60),"00") & ":"
& Format((qryYearlyMileageTotalsTrial!SumOfTSeconds Mod 60),"00"),
Format$(Statistics.StatDate,'yyyy')
ORDER BY Year(Statistics.StatDate) DESC;


Thanks for you help

 

[Chris2]

I have a query that is supposed to show the totals for each years Timed
miles, all miles, hours ridden and sum of elevation. What I get is each year
shows records five times per year instead of totals for the year. The problem
is in the hours ridden field. This field gets total number of seconds from
anther queries field named Tseconds and calculates total miles, totoal hrs,
min and seconds for each year. The calculations are correct, just the output
results are wrong. Instead, it returns each year 5 times with the hrs, min
and seconds for each year showing up. IE..

Below is the SQL statement I am working with....

Thanks for you help

Wylie C,

Year Timed Miles Ridden All Miles Ridden Hours Ridden Sum of Elevation
2005 3986.76 4109 153:43:00 0
2005 3986.76 4109 16:29:40 0
2005 3986.76 4109 190:47:30 0
2005 3986.76 4109 249:46:00 0
2005 3986.76 4109 47:57:00 0
2004 2466.50 2531 153:43:00 0
2004 2466.50 2531 16:29:40 0
2004 2466.50 2531 190:47:30 0
2004 2466.50 2531 249:46:00 0
2004 2466.50 2531 47:57:00 0

SELECT DISTINCTROW
Year(Statistics.StatDate) AS [Year]
,Sum(Statistics.Mileage) AS [Timed Miles Ridden]
,Sum(Statistics.[Total Miles]) AS [All Miles Ridden]
,Format((qryYearlyMileageTotalsTrial!SumOfTSeconds\3600),"00")
& ":" &
Format(((qryYearlyMileageTotalsTrial!SumOfTSeconds Mod
3600)\60),"00") & ":" &
Format((qryYearlyMileageTotalsTrial!SumOfTSeconds Mod
60),"00") AS [Hours Ridden]
,Sum(tblMoreStats.Elevation) AS SumOfElevation
FROM qryYearlyMileageTotalsTrial
,Statistics
INNER JOIN
tblMoreStats
ON Statistics.RecNum = tblMoreStats.RecNum
GROUP BY Year(Statistics.StatDate)
,Format((qryYearlyMileageTotalsTrial!SumOfTSeconds\3600),"00")
& ":" &
Format(((qryYearlyMileageTotalsTrial!SumOfTSeconds Mod
3600)\60),"00") & ":" &
Format((qryYearlyMileageTotalsTrial!SumOfTSeconds Mod
60),"00")
,Format$(Statistics.StatDate,'yyyy')
ORDER BY Year(Statistics.StatDate) DESC;

Why is the cartesian product of qryYearlyMileageTotalsTrial and the
INNER JOIN of Statistics and tblMoreStats called upon? I recommend
using ANSI standard JOIN syntax.

Why do both these lines appears on the GROUP BY?

Year(Statistics.StatDate)

Format$(Statistics.StatDate,'yyyy')


Sincerely,

Chris O.