By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,896 Members | 1,997 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,896 IT Pros & Developers. It's quick & easy.

Code VS query gives different average

P: n/a
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

Jul 7 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.

Jul 7 '06 #2

P: n/a
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.
Jul 7 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.