Calculate record in value range (1 Viewer)

edyz11 · Dec 28, 2016

Hi master..i need some advice here on how to calculate record in specific range.

i have a table with data type set to currency. Under my report i want to add a box that calculate record on specific range of value something like this :-

(1) <$10,000
(2) $10,001 - $29,999
(3) $30,000 - $49,999
(4) >$50,000

It means, if there are 5 records which value entered below $10,000 it will show 5.

So far i can't find the solution on what formula to use in the control source.

Much appreciated. TQ.

Ranman256 · Dec 28, 2016

build a custom Function to examine the field and return your 'group'.
put the function in a module so queries can read it.

in the query, supply the field with the value...
usage:
Batch:GetGroup([field])

Code:

public Function GetGroup(byval pvFld)
dim vRet 

select case pvFld
   case <= 10000
      vRet = "Small"
  case < 30000
        vRet = "under3"
  case  < 50000
        vRet = "under5"
  case else
        vRet = "Large
end select

GetGroup = vRet
end function

sneuberg · Dec 28, 2016

I found an interesting idea here you could try.

Let say the field name is Price then for the case of less than 10,000 the control source might be:

Code:

=Sum(IIf([Price]<10000, 1, 0))

For $10,001 - $29,999

Code:

=Sum(IIf([Price]>=10000 And [Price] < 30000 , 1, 0))

etc. It seems to be a clever way to turn Sum into a Count function with a condition.

Frothingslosh · Dec 28, 2016

I actually use Steve's solution all the time.

I've attached a sample database with a table called tblSales and a query. (There's a module there too with code I used to populate the table, I just didn't bother deleting it.)

tblSales has 4 fields:
SaleID, which is an autonumber and primary key
SalespersonID, which is honestly there just for verisimilitude
SaleDate, for the same reason as SalespersonID (and so you can adjust to filter for a date range if necessary)
and
SaleAmount, which ranges from $0.01 to $75,000.

To get the query you wanted, I did this:

Code:

SELECT "< $10,000" As SalesRange, SUM(IIf([SaleAmount] <= 10000,1,0)) As NumberOfSales FROM tblSales
UNION ALL
SELECT "$10,000.01 - $29,999.99" As SalesRange, (SUM(IIf([SaleAmount] > 10000,1,0) * IIF([SaleAmount] < 30000,1,0))) As NumberOfSales FROM tblSales
UNION ALL
SELECT "$30,000 - $49,999.99" As SalesRange, (SUM(IIf([SaleAmount] >= 30000,1,0) * IIF([SaleAmount] < 50000,1,0))) As NumberOfSales FROM tblSales
UNION ALL 
 SELECT ">=  $50,000" As SalesRange, SUM(IIf([SaleAmount] >= 50000,1,0)) As NumberOfSales FROM tblSales;

You could also get fancy, if you want, and use another table with value ranges and have the If/Thens pull from the range, but that seemed more work than it was worth.

The results were specifically what you requested, as can be seen in the attached screenshot. (I added that in case you aren't able to open the database - I don't know what version of Access you're using.)

edyz11 · Dec 29, 2016

First of all, thanks for the feedback!

I've tried Mr. Ranman256 suggestion and it works fine. Thank you so much sir!

I've also tried Mr. sneuberg suggestion but i get syntax error and i don't know which part is wrong.

As for Mr. Frothingslosh suggestion, i don't have the idea on how to add it to my current query.

Calculate record in value range (1 Viewer)

edyz11

New member

Ranman256

Well-known member

sneuberg

AWF VIP

Frothingslosh

Premier Pale Stale Ale

Attachments

edyz11

New member

Similar threads

Users who are viewing this thread