Solved get a value from another table based on the range (1 Viewer)

[talha]

Hi,

I have a customer table that has a column (Customer age). I also have a reference table that has an age range and based on where the customer age falls, it identifies an age_Group.

I need to know how to do this through SQL.

Here is what I am trying to do

I tried following, but it didn't work.

SELECT
c.*,
RT_C.Age_group
from
tbl_customer c
join
tbl_age RT_C
on
c.Customer_age between RT_C.Age_from and RE_C.Age_to;

I've attached the DB for reference.

Thanks in advance

 

[MajP]

See recent thread
Post in thread 'Calculated field from marks' https://www.access-programmers.co.uk/forums/threads/calculated-field-from-marks.330100/post-1907448

 

[arnelgp]

paste on SQL view on Query designer:

SELECT
    tbl_Customer.[Customer Name],
    tbl_Customer.[Customer Age],
    tbl_Customer.[Customer Salary],
    (SELECT TOP 1 Age_Group FROM Tbl_Age AS T WHERE Tbl_Customer.[Customer Age] Between T.Age_From And T.Age_To) AS Age_Group
FROM tbl_Customer;

see query, 1_CustAgeGroupQ on the demo.

 

[plog]

JOINs don't have to use equal signs:

SELECT [Customer Name], [Customer Age], [Customer Salary], Age_Group, Salary_Group
FROM tbl_Customer
INNER JOIN Tbl_Salary ON [Customer Salary]>=Salary_from AND [Customer Salary]<=Salary_to
INNER JOIN Tbl_Age ON [Customer Age]>=Age_from AND [Customer Age]<=Age_to

To do this in Access you have to get your hands dirty with SQL. The query designer doesn't allow you to make that type of JOIN using it. So, in the designer you bring in the 3 tables, bring down all the fields you want to show, JOIN the tables per the JOIN fields above. That makes them equijoins (using the = sign) so the last step is to go into SQL view and change the JOIN operators from = signs to the ones above and save it. Once you do that however you can no longer edit the query using the designer or it will revert to = signs.

Of course you should be able to just go straight to SQL view and paste in my above code (but I did free hand it, so no guarantees there aren't typos.)