0

after a question that I put before, I need to create ranges of DAP values for differents 'EspCodigo'. See table.

Previous result:

InvntID EspCodigo   DAP   Objectivo 
15      Ec          15    Rolaria_Ec
16      Ec          5     Rolaria_Ec
26      Pb          50    Folha_Pb  
27      Pb          20    Lenha_Pb  
28      Ec          12    Rolaria_Ec
29      Ec          30    Rolaria_Ec
30      Ec          5     Rolaria_Ec
31      Ec          7     Rolaria_Ec
32      Ec          40    Desenrolar_Ec

I had use the function Partition but it presents not so good result like example:

Range: Partition([DAP];0;250;5)

 Range      Count    EspCodigo
  0:  4     32        EC
  5:  9     294       EC
 10: 14     673       EC
 15: 19     521       ...
 20: 24     421       ...
 25: 29     288       ...
 30: 34     199       ...

The answer should be:

The ranges are not: >0 to <5, >=5 to <10, >=10 to <15, >=15 to <20, ... How can I make like this?

Thanks,

António
  • 23
  • 3
  • I don't know about this specific Access funtion, but there's a similar one in Standard SQL and this allows switching the ranges, i.e. `Partition([DAP];250;0;5)` which might result in your logic – dnoeth Nov 03 '20 at 20:11
  • In standard SQL? Which function? [In python is possible to use bin function](https://www.python-course.eu/pandas_python_binning.php). – António Nov 04 '20 at 19:30
  • It's `width_bucket` https://www.oreilly.com/library/view/sql-in-a/9780596155322/re91.html – dnoeth Nov 04 '20 at 20:11

1 Answers1

0

I'm sure in Access you will be able to define an additional query over the top of the existing query (the one which has the Partition function) and apply a formula to transform the Range column to the presentation format you want i.e. set a formula over the Range column to something like:

=">" & Replace(Range, ": ", " to <=")

i.e. you want to prefix each Range value with ">" and then replace the ": " with " to <=".

user9601310
  • 1,076
  • 1
  • 7
  • 12
  • Hello, I try to use it and is not what I want. Result: "> 0 to <=4". Even when I have intervals to create from 3 digits it doesn't make the correction. – António Nov 04 '20 at 19:19
  • It would help if you could share the text of the SQL queries you are using. Otherwise it's difficult to understand precisely where your issues may be coming from. – user9601310 Nov 05 '20 at 05:15