How do I create numeric groups in Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a balance field containing currency data that could basically have any
amount in it. I am trying to create a balance band field without writing lots
of queries. This field would show the balance in groups such as £0 to £500,
£500 to £5000 etc.

Any help with a simple and quick way of doing this would be very much
appreciated!!
 
Hi,

You can create a (small) table just for that, with 3 fields: a primary key
(autonumber, or label for the group), a lowerLimit and a upperLimit fields.
You can then use this small table with a BETWEEN comparison to define the
groups:


SELECT b.primaryKey, SUM(a.currencyValue)
FROM myTable As a INNER JOIN mySmallTable As b
ON a.currencyValue >= b.lowerLimit AND a.currencyValue < b.upperLimit
GROUP BY b.primaryKey



Hoping it may help,
Vanderghast, Access MVP
 
Hi Michel,

Thanks very much for this, it has helped tremendously! its something I have
been doing by writing an update query for each band. I simply never thought
of creating a lookup table and doing it that way. Thanks again, you have
saved me hours of boredom!

Cheers

Simon
 
Create a table with fields that define your bands, e.g.:
BandID Number. Primary key.
MinBalance Currency

You can then look up the band number like this:
=DLookup("BandID", "tblBand", Nz([Balance],0) & " >= [MinBalance]"))
or use a subquery to return the value. If subqueries are new, see:
http://support.microsoft.com/?id=209066
 
Back
Top