Item number ranges

J

Jeff Shanker

I have a table with all of our item numbers in it. Is there a query that I
can run that will give me a range, ie 100000 - 150000 and the # of items in
that range. I can have up to 1M total items.

Thanks,

Jeff Shanker
 
J

Jeff Shanker

An addeendum -

I would prefer not to keep typing in the ranges, but let the query pick them
out for me...

Jeff
 
J

John Spencer (MVP)

How do you expect the query to pick out the ranges for you? There has to be
some way to specify the range.

Your query would be a totals query.

Something like:

SELECT Count(*), Min(SomeField), Max(SomeField)
FROM YourTable
WHERE SomeField Between 100000 and 150000

If you had a table of ranges you could use that to get the data. Another
possibility would be to use your number field and split the data up using it.
Assuming your ranges are every 50000 records.

SELECT (NumberField \ 50000) * 50000 as RangeStart,
Count(*) as CountRecords,
Min(NumberField) as Smallest,
Max(NumberField) as Largest
FROM YourTable
GROUP BY (NumberField \ 50000) * 50000
 
J

Jeff Shanker

John,

It took a little VBA and typing in the 20 ranges into its own table, but I
got it to work. Thanks - Jeff
 
Top