Grouping query results and exporting to excel 1

[SHAWTY721]

Okay I have a database that contains records of billing information. I have created the queries I need to display the information but my issue is, is there a way to take the total values that are being produced from the query and separating them depending on amount for instance 0-30, 30-60, 60-90, … 690-720, 720+. I am just wondering is there a easier way than doing this by hand since there are tons of records for each of the billing methods which the information is being separated by.

 

[PWise]

Create A table called BillingGroups

BillingGroupsid number pk
GroupStartAmount Number
GroupEndAmount Number


enter values

GroupStartAmount GroupEndAmount
0 30
31 60
61 90
91 .............



721 xxxxxxxxxxx (The largest Amount you might have)

Select * from yourquery 
inner join BillingGroups
on yourquery.fieldtogroupby Between GroupStartAmount    GroupEndAmount

 

[Golom]

Have a look at the Partition Function.

You would place it in your SQl with something like

SELECT [Amount], 
       Partition([Amount], 0, 720, 30) As [In Range]

From SomeTable

Order By 2

 

[Golom]

BTW ... your specified ranges overlap.

Do you mean 0-30, 31-60, 61-90, etc.?

 

[SHAWTY721]

Yes I meant 0-30, 31-60, 61-90, etc.

Sorry about that

 

[PWise]

Golom:

I was not aware of the Partition Function. Have A pink one.

Note Partition Function can not be used for Variable intervals
IE

0-30, 1 month
31-60, 2 months
61-90, 3 months
91-180, 3-6 month
181 -360, 6-12 month

 

[Golom]

pwise

True. The first interval has 31 entries and those that follow have 30. The OP could probably use

Partition([Amount], [red][b]1[/b][/red], 720, 30)

and Partition would generate a range " :0" at the beginning if there were any account values that were zero or less.

Certainly your SQL would be the way to go if you had the sort of ranges that you illustrated.