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 🙂 .