Item number ranges

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
An addeendum -

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

Jeff
 
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
 
John,

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