Query or vba

[MelB]

I am looking for someone with expertise on queries and vba. I have done some of both but I am Not an expert. I have found queries to be easiest but I haven't been able to come up with anything on the problem I am working on. I wonder about a crosstab query but I have not done one before. Here is the goal...

I have a table with two columns, both numeric, one is a Location which is the measurement in feet from a starting point, the other is the Quantity of items at each location. I need to find the highest maximum Quantity within a mile from each location and then be able to report on that maximum quantity.

I am thinking that I need to open a recordset and loop through the records but is there an easier way? If not, does someone have some steps or sample code to get me started? Thanks in advance!!!

 

[FoFa]

SELECT QTY From YourTable
where Location = (select max(location) from YourTable where
location =< 5280)
(or how ever many feet are in a mile, I don't remember)

 

[MelB]

Thanks but...

Thanks but I need to summarize the quantities. There may be 15 locations in the first mile and they all have different quantities and I need the total for each mile in the report then identify the maximum. Maybe I don't understand what you are telling me but I don't think the query you suggested will do this... maybe I didn't explain well enough. Thanks though!