Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Dec 14, 1999

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  , 40010
FA1111   ,   400401  , 40010
FA1111   ,   400402  , 40010
FA1111   ,   400403  , 55555
FA1111   ,   400404  , 40010
FA1111   ,   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)
)
go
insert prod values ('FA1111'   ,  '400400'  , '40010')
go
insert prod values ('FA1111'   ,   '400401'  , '40010')
go
insert prod values ('FA1111'   ,   '400402'  , '40010')
go
insert prod values ('FA1111'   ,   '400403'  , '55555')
go
insert prod values ('FA1111'   ,   '400404'  , '40010')
go
insert prod values ('FA1111'   ,   '400405'  , '40010')
go

select 
cust_no, box_number
from prod
order by cust_no
compute 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 :) .
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date