Max query doesn't return the Max value for a field?!

[Guest]

I'm trying to run a query that will determine the maximum value for a
calculated field from another query. I've run the exact same query syntax on
several other queries based on other tables and they work just fine, but for
some reason this one does not. I've run a Max query on the underlying table
and that works fine, but when I run the max query after calculating it
returns the value 8 as the Max value even though I have values for 10 and 15
in that field. I've even gone back the original data and re-entered it and
made sure all fields are set to long integer and that field properties of the
calculated field ([LMSCOMU]) and query Max field are set to general number
format. I've included the SQL syntax for the query below. Any suggestions
would be greatly appreciated!

SELECT tblColony_counts.Date, Avg(qryLMSavgCOMU.LMSCOMU) AS AvgOfLMSCOMU,
Max(qryLMSavgCOMU.LMSCOMU) AS MaxOfLMSCOMU
FROM tblColony_counts INNER JOIN qryLMSavgCOMU ON
tblColony_counts.ColonyCountID = qryLMSavgCOMU.ColonyCountID
GROUP BY tblColony_counts.Date
HAVING (((tblColony_counts.Date) Between [Enter Beginning Date] And [Enter
Ending Date]));


Cheers,
Peter

 

[Guest]

You have an INNER JOIN between the tables. If either table doesn't have a
matching record, then the records with the 10's and 15's might not be showing
up.

Also try removing the HAVING clause. Maybe the bigger numbers don't fall
within the expected date range.

 

[Guest]

Hi Jerry,

I've checked the data and the INNER JOIN is ok, the records with 10's and
15's have related records and are included in the Ave field. I'll look at
the dates and see if something is going on there, maybe a formatting issue or
something. However, I'm entering from 1/1/06 to today and all the data is
from 2006 so the dates should be correct. Also, since the Avg function is
including these values I would believe the Max would, too. Any other ideas
would be greatly appreciated. I'm at a complete loss?!

Cheers,
Peter

You have an INNER JOIN between the tables. If either table doesn't have a
matching record, then the records with the 10's and 15's might not be showing
up.

Also try removing the HAVING clause. Maybe the bigger numbers don't fall
within the expected date range.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm trying to run a query that will determine the maximum value for a
calculated field from another query. I've run the exact same query syntax on
several other queries based on other tables and they work just fine, but for
some reason this one does not. I've run a Max query on the underlying table
and that works fine, but when I run the max query after calculating it
returns the value 8 as the Max value even though I have values for 10 and 15
in that field. I've even gone back the original data and re-entered it and
made sure all fields are set to long integer and that field properties of the
calculated field ([LMSCOMU]) and query Max field are set to general number
format. I've included the SQL syntax for the query below. Any suggestions
would be greatly appreciated!

SELECT tblColony_counts.Date, Avg(qryLMSavgCOMU.LMSCOMU) AS AvgOfLMSCOMU,
Max(qryLMSavgCOMU.LMSCOMU) AS MaxOfLMSCOMU
FROM tblColony_counts INNER JOIN qryLMSavgCOMU ON
tblColony_counts.ColonyCountID = qryLMSavgCOMU.ColonyCountID
GROUP BY tblColony_counts.Date
HAVING (((tblColony_counts.Date) Between [Enter Beginning Date] And [Enter
Ending Date]));


Cheers,
Peter