Group rows with number ranges in Access 2000 SQL?

Question

I have this picture with my two columns:

"povrsina" is area size in square meters. It can be classified in ranges, for example (0 < povrsina <= 10), (10 < povrsina <= 30), (30 < povrsina <= 60) etc...

"tipprod" can have textual values like "01", "05", "09", "04" etc...

I need to group all data I have by "povrsina" but in the given ranges. I have created helper table in access which looks like this:

You can see the ranges. I tried it with my main table but I am not sure if I get correct results. Is it really checking "povrsina" value against the range in my helper table?

How can I do this in Access 2000?

score 2 · Accepted Answer · answered Jul 10 '17 at 12:10

2

You can add two fields to your guide table with povrsina_min, povrsina_max to define your ranges:

povrsina_tag|povrsina_min|povrsina_max
10          |0           |10
30          |11          |30
....

Then you can do a query with a CROSS JOIN against your guide table adding a where clase with povrsina <= povrsina_max and povrsina >= povrsina_min

And then you can GROUP BY povrsina_tag and tipprod.

answered Jul 10 '17 at 12:10

Sergio Prats

1,043
1
14
19

That's probably a cleaner solution, it does not involve `VBA` – Vityata Jul 10 '17 at 12:16
Technically CROSS JOIN does not exist in Access 2000, but here is how to get it with plain SQL: https://stackoverflow.com/questions/1231676/how-to-use-cross-join-in-access – Vlad Jul 10 '17 at 14:02

score 1 · Answer 2 · answered Jul 10 '17 at 12:06

Just guessing... Can you try something like this?

select MainTable.Povishnina as [Povishnina], count(*) as [number of occurences]
from (
  select case  
    when score between 0 and 10 then ' do 10 m2'
    when score between 30 and 50 then ' od 11 do 30 m2'
    else 'Nad 3000 m2'
  from scores) MyTable
group by MainTable.Povishnina

In general, take a look at this question: In SQL, how can you "group by" in ranges?

score 1 · Answer 3 · answered Jul 10 '17 at 12:53

1

In an Access query you can use:

Select 
    *,
    (Select Top 1 T.PovrsinaM2 From T13_Povrshina As T
    Where T.povrsina <= YourTable.povrsina
    Order By T.povrsina Desc)
From
    YourTable

You will have to adjust T13_Povrshina slightly.

answered Jul 10 '17 at 12:53

Gustav

53,498
7
29
55

Group rows with number ranges in Access 2000 SQL?

3 Answers3