Code VS query gives different average

[Steeler]

I am getting the averages of a field LT. When I run it in the query
designer:

SELECT tbl_LTandWait.EngModule, Avg(tbl_LTandWait.LT) AS AvgOfLT,
Avg(tbl_LTandWait.LT_Mfg) AS AvgOfLT_Mfg
FROM tbl_LTandWait
WHERE (((tbl_LTandWait.EngSN)<>' Median LT' And
(tbl_LTandWait.EngSN)<>' Lead Time Goal' And (tbl_LTandWait.EngSN)<>'
Avg Lead Time'))
GROUP BY tbl_LTandWait.EngModule
HAVING (((tbl_LTandWait.EngModule)='Horizontal'));
I get the AvgOfLT as 3.916 which is correct.

When I do it in code:

lsSQL = "SELECT tbl_LTandWait.EngModule, Avg(tbl_LTandWait.LT) AS
AvgOfLT, Avg(tbl_LTandWait.LT_Mfg) AS AvgOfLT_Mfg " & _
"FROM tbl_LTandWait " & _
"WHERE (((tbl_LTandWait.EngSN)<>' Median LT' And
(tbl_LTandWait.EngSN)<>' Lead Time Goal' And (tbl_LTandWait.EngSN)<>'
Avg Lead Time')) " & _
"GROUP BY tbl_LTandWait.EngModule " & _
"HAVING (((tbl_LTandWait.EngModule)='Horizontal'))"

Set dbs = CurrentDb
Set rstMods = dbs.OpenRecordset(lsSQL)

MsgBox rstMods!AvgOfLT
Exit Sub
I get 3.57 which is wrong. It is exactly the same query whether in code
or query so I don't understand why I get different results.

Anbody have any ideas.

Thanks,
Steeler

[Lyle Fairfield]

Steeler wrote:

I am getting the averages of a field LT. When I run it in the query
designer:

SELECT tbl_LTandWait.EngModule, Avg(tbl_LTandWait.LT) AS AvgOfLT,
Avg(tbl_LTandWait.LT_Mfg) AS AvgOfLT_Mfg
FROM tbl_LTandWait
WHERE (((tbl_LTandWait.EngSN)<>' Median LT' And
(tbl_LTandWait.EngSN)<>' Lead Time Goal' And (tbl_LTandWait.EngSN)<>'
Avg Lead Time'))
GROUP BY tbl_LTandWait.EngModule
HAVING (((tbl_LTandWait.EngModule)='Horizontal'));
I get the AvgOfLT as 3.916 which is correct.

When I do it in code:

lsSQL = "SELECT tbl_LTandWait.EngModule, Avg(tbl_LTandWait.LT) AS
AvgOfLT, Avg(tbl_LTandWait.LT_Mfg) AS AvgOfLT_Mfg " & _
"FROM tbl_LTandWait " & _
"WHERE (((tbl_LTandWait.EngSN)<>' Median LT' And
(tbl_LTandWait.EngSN)<>' Lead Time Goal' And (tbl_LTandWait.EngSN)<>'
Avg Lead Time')) " & _
"GROUP BY tbl_LTandWait.EngModule " & _
"HAVING (((tbl_LTandWait.EngModule)='Horizontal'))"

Set dbs = CurrentDb
Set rstMods = dbs.OpenRecordset(lsSQL)

MsgBox rstMods!AvgOfLT
Exit Sub
I get 3.57 which is wrong. It is exactly the same query whether in code
or query so I don't understand why I get different results.

Anbody have any ideas.

Thanks,
Steeler

I suppose you could save the query created in the query designer as,
say, "query2", or whatever and then use something like:

Dim isSQL As String
isSQL = DBEngine(0)(0).QueryDefs("query2").SQL
Debug.Print DBEngine(0)(0).OpenRecordset(isSQL)!AvgOfLT

to ensure that no syntax error has been made.

[salad]

Steeler wrote:

I am getting the averages of a field LT. When I run it in the query
designer:

SELECT tbl_LTandWait.EngModule, Avg(tbl_LTandWait.LT) AS AvgOfLT,
Avg(tbl_LTandWait.LT_Mfg) AS AvgOfLT_Mfg
FROM tbl_LTandWait
WHERE (((tbl_LTandWait.EngSN)<>' Median LT' And
(tbl_LTandWait.EngSN)<>' Lead Time Goal' And (tbl_LTandWait.EngSN)<>'
Avg Lead Time'))
GROUP BY tbl_LTandWait.EngModule
HAVING (((tbl_LTandWait.EngModule)='Horizontal'));
I get the AvgOfLT as 3.916 which is correct.

When I do it in code:

lsSQL = "SELECT tbl_LTandWait.EngModule, Avg(tbl_LTandWait.LT) AS
AvgOfLT, Avg(tbl_LTandWait.LT_Mfg) AS AvgOfLT_Mfg " & _
"FROM tbl_LTandWait " & _
"WHERE (((tbl_LTandWait.EngSN)<>' Median LT' And
(tbl_LTandWait.EngSN)<>' Lead Time Goal' And (tbl_LTandWait.EngSN)<>'
Avg Lead Time')) " & _
"GROUP BY tbl_LTandWait.EngModule " & _
"HAVING (((tbl_LTandWait.EngModule)='Horizontal'))"

Set dbs = CurrentDb
Set rstMods = dbs.OpenRecordset(lsSQL)

MsgBox rstMods!AvgOfLT
Exit Sub
I get 3.57 which is wrong. It is exactly the same query whether in code
or query so I don't understand why I get different results.

Anbody have any ideas.

Thanks,
Steeler

It almost sounds like 1 query is returning more records than the other
when avg'ing. Maybe make a copy and remove the grouping/avging thus
making it a Select query instead of a totals query and see if they both
return the same number of records.