splitting values into classes

[damrus]

Hi,

I would like to update my dtb (in Access) on class information.
The database looks like that:

nr_sim Euler_id EL_Gross class
3172 15013729 13452345
3167 15297205 234514
3169 96873838 1345
3173 70984066 25134
3170 70584807 123
3168 70816660 235134
3171 70282458 1452145
3173 70941751 1345
3168 95030718 1451345
3168 43446420 14351


I would like to split this values (update then aggregate) into classes defined in dictionary table (which includes 10000 classes with fixed parameters 'from' 'to')
The dictionary looks like that:

class No from to
1 0 1933
2 1933 3866
3 3866 5799
4 5799 7731
5 7731 9664
6 9664 11597
7 11597 13530


Have anyone has an idea what query (SQL) should work for that?

Thanks

 

[dhookom]

What is a dtb?
What is the relationship/rule from your first data list to your last list?
You really need to explain more clearly.


Duane
Hook'D on Access
MS Access MVP

 

[damrus]

Sorry...

dtb=database, meaning table in Access database

There is no relationships between these two tables (I mean no common id).

Shortly speaking I need to update the field 'class' depending on which class it belongs (basing on range 'from' - 'to')
The code for small numbers of classes would be like this:

IF(EL_gross<from and EL_gross<=to;class_No;IF(...))

Please let me know if you need further clarification.

Thanks for your response.

 

[MajP]

SELECT 
 tblData.nr_sim, 
 tblData.Euler_id, 
 tblData.EL_Gross, 
 Dictionary.Class_No
FROM 
 Dictionary, 
 tblData
WHERE 
 tblData.EL_Gross >= [Dictionary].[from] And tblData.EL_Gross <= [Dictionary].[to]

 

[damrus]

Thanks a lot!

This is exactly what I need. And it seems very easy.

Regards