# Assign categories to records from one table by matching on the largest number in the second table which is less than or equal to a value in the first table

I have two tables, each with a numeric value and a text value.  I want to use the numeric value in each record in the first table to select the record in the second table whose numeric value is the largest value less than or equal to the value in the first table, and join the two records.  An example might be to categorize people into age groups:

Table 1

Fred  50
Ralph  32
Sarah  43

Table 2

Twenties  20
Thirties  30
Forties  40
Fifties  50
Sixties  60

Result

Fred  50  Fifties
Ralph  32  Thirties
Sarah  43  Forties

I don't even know what to call such a join, nor if it is possible with a query or would have to be done in SQL.  Seems like this would be a fairly common problem for sorting things into groups, but I haven't been able to word an online search properly to turn up postings of similar problems.

ADDENDUM:  Thank you for your responses.  That was a bad example, I apologize.  The second table will contain starting points for each range, but in the "real life" problem I am trying to solve, they will not all be multiples of 10, they could be any integer.
###### Who is Participating?

CIOCommented:
Then it could be:

``````Select
Table1.Name,
Table1.Age,
(Select DecadeName From Table2 As T1
(Select Top 1 Decade From Table2 As T2
From
Table1
``````
0

Commented:
You inner join on Table2.[Age] = (Table1.[Age] - (Table1.[Age] mod 10))
0

freelancerCommented:
This is not a common method of joining in my view. If you wanted to design a more generic solution you would define ranges in the second table, e.g.

dataTable
name, val
Fred,  50
Ralph,  32
Sarah,  43

rangeTable
rangename, low, high
Twenties,  20, 30
Thirties,  30, 40
Forties,  40, 50
Fifties,  50, 60
Sixties,  60, 9999

select  d.name, d.val, r.rangename
from dataTable d
inner join rangeTable r on d.val >= d.low and d.val < r.high

select r.low, r.rangename, count(d.val)
from dataTable d
inner join rangeTable r on d.val >= d.low and d.val < r.high
group by r.low, r.rangename
order by r.low
0

CIOCommented:
You can simply divide the age by 10:

``````Select
Table1.Name,
Table1.Age,
From
Table1,
Table2
Where
``````
Notice the backslash to perfrom integer division.

That will return your requested result.
0

Commented:
@Gustav

Won't the Table2 values also need integer division?
0

CIOCommented:
Yes, you are right - or this, which I originally had in mind:

``````Where
(Table1.Age \ 10) * 10 = Table2.Decade
``````
0

Author Commented:
Hi, all, and thank you.  Please see Addendum to the question, the way I originally presented the problem was misleading and I have clarified it.
0

Commented:
both my and Gustav's solutions should work for any values of 10.
0

Commented:
If your tests prove otherwise, please post a more representative data sample
0

freelancerCommented:

This was the point i attempted to make in my original response. I believe you need to define ranges through the second table,  not rely on just a start point of a range.

It can't be assumed that each range is of equal size (such as 10 years) or that they are mutually exclusive. Add another column to the second table and provide both the lower and upper boundary points of each range.
0

Author Commented:
I did not get to look at this yesterday, just getting back to it today.

I do not know SQL beyond the very basics so I am having to go through Gustav's proposal very carefully to try and understand it, but testing with it in my database, it looks like it is exactly what I am looking for.

I still want to review it a little further before marking it as the answer, I will try to finish understanding it tomorrow and get it so marked once I am sure.

Thank you very much!
0

Author Commented:
Thank you so much to everyone who replied.

The second solution from Gustav was exactly what I was looking for.  This has also helped me grasp a few more aspects of SQL (this is being done in Access but I almost exclusively use the graphical query builder).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.