Large query and grouping numbers 2

[kiwibaldman]

I'm working with very large numbers of billing data (apprix 17 million rows) and looking to group the information into logical groupings e.g counting the number of customers in $10 billed increments. I suspect I'm using the wrong tool but don't have a workplace alternative from Access - is there a useful web download I can use or some other nifty SQL thingy ? AND, other than using pivottables is there some way I can group this information in a query ? I'm not too fancy at sql but can work out what happens when the query design tool translates what I've done to Sql.

 

[Golom]

You haven't provided a lot of detail so the following may provide a pattern for you that you will need to adapt to your specific situation.

The usual way to portray groups is to set up another table that defines the limits of the groups. For example
[tt]
tblGroups
StartValue EndValue GroupNum[/tt][tt]
0 100 1
101 200 2
201 300 3
: : :
901 1000 10
[/tt]
Then your table containing the data that you want to group may look like
[tt]
tblMain
Key gField[/tt][tt]
1 50
2 127
3 614
4 196
5 811
[/tt]
Then you could write SQL like
[tt]
Select GroupNum, Count(*) As [Number In Group]

From tblMain, tblGroups

Where tblMain.gField BETWEEN StartValue AND EndValue

Group By GroupNum

Order By GroupNum
[/tt]
This is "vanilla" (i.e. pretty basic) but it does provide a pattern for you to start with. Sorry but I avoided attempting to describe how to do this in Query view. It is certainly possible there but Query Analyser is a thing best seen rather than talked about.

As to your 1.7 million records.

Of course your queries will run a bit long but, if you make sure that the fields that you are using to filter the results (gField in my example) are indexed then it should perform adequately. You may also look into placing additional restrictions on the query (e.g. only after some date) to further restrict how many records the query must handle.