devxlogo

Finding the Max and Min in Consecutive Data

Finding the Max and Min in Consecutive Data

Question:
I have a table in Microsoft Access 97 with sales info in the following format:

 Prod_Type, Box_Number, Cust_No.FA1111   ,   400400  , 40010FA1111   ,   400401  , 40010FA1111   ,   400402  , 40010FA1111   ,   400403  , 55555FA1111   ,   400404  , 40010FA1111   ,   400405  , 40010

When I write a report, I want to show that customer 40010 got boxes 400400 to 400402 and boxes 400405 to 400406, totalling 5 boxes. How do I do this using SQL?

Answer:
I don’t have Access, but I worked this out in Pubs. Try this:

 create table prod (prod_type  char(6),box_number char(6),cust_no    char(5))goinsert prod values ('FA1111'   ,  '400400'  , '40010')goinsert prod values ('FA1111'   ,   '400401'  , '40010')goinsert prod values ('FA1111'   ,   '400402'  , '40010')goinsert prod values ('FA1111'   ,   '400403'  , '55555')goinsert prod values ('FA1111'   ,   '400404'  , '40010')goinsert prod values ('FA1111'   ,   '400405'  , '40010')goselect cust_no, box_numberfrom prodorder by cust_nocompute count(box_number) by cust_no

I don’t know whether Access supports a COMPUTE BY. It’s a handy way to drive report style output from a SELECT statement. My guess is that it does because Access seems to provide other extensions (crosstabs) out of the box, so I’m hoping Microsoft included the nicety of COMPUTE BY in Access too 🙂 .

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist