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 🙂 .
Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.
