I read in Ben Forte's book that the results of a query is actually an array. What I want to do is use array functions ArrayMax, ArrayMin to snag the min and max values of my query results. I know that this can be done with my query "MAX(Apartments.NUM_Bedrooms) AS Max_NUM_Bedrooms" , however the query (Access 2K) is this monster containing multiple inner joins (See below) . When I add a "MAX(Apartments.NUM_Bedrooms) AS Max_NUM_Bedrooms" to the query I get this error message:
@@@@@@@@@@@@@ ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'Apartment_ID' as part of an aggregate function. @@@@@@@@@@@@@ What I want to do is snag the min and max value for a sub set of data... Kinda like a the min and max values of of the results of a nested CF_Output. My question: How can I get these values, either with my query or snagging the values out of the query results using an array function.. Here is the catch. I don't want to hit the database again (Hence all the joins) and I do not want have to create an array from query results beacuase the are already in an array. Any takers? Thanx, Coz @@@@@@@ SELECT DISTINCT Apartments.Apartment_ID, Apartments.NUM_Bedrooms, Apartments.NUM_Bathrooms, Apartments.CUR_Price_Low, Apartments.CUR_Price_High, Apartments.NUM_Size, Apartments.TXT_Unit_Name, Apartments.NUM_Number_Available, Apartments.LKP_Availability_Status, Apartments.LKP_Communities_ID, Communities.Communities_ID, LKP_Availability_Status.TXT_Availability_Status, Communities.TXT_Communities_Name, Communities.TXT_Street_1, Communities.TXT_Street_2, Communities.TXT_City, Communities.LKP_State, TXT_Zip, MAX(Apartments.NUM_Bedrooms) AS Max_NUM_Bedrooms FROM Apartment_Amenities2 INNER JOIN ((Communities INNER JOIN (LKP_Availability_Status INNER JOIN Apartments ON LKP_Availability_Status.Availability_Status_ID = Apartments.LKP_Availability_Status) ON Communities.Communities_ID = Apartments.LKP_Communities_ID) INNER JOIN LKP_Apartment_Amenities ON Apartments.Apartment_ID = LKP_Apartment_Amenities.Apartment_ID) ON Apartment_Amenities2.Apartment_Amenity_ID = LKP_Apartment_Amenities.Apartment_Amenities_ID Cozmo <HoloGraphic>INC.</HoloGraphic> 522 E. Park Ave Tallahassee, Florida 32301 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
