Q

Returning factors in MS Access

I am using MS Access to try and return "Factors" based upon age. I have Table "A" that lists Age (anywhere from 0 to 100) and I want to join it to Table "B" that lists ages in intervals (0, 25, 30, 35, 40, 45...) and returns a factor based upon where the age falls in that table. Is this possible? Please Help! Thanks.

Assuming you have tables similar to this:

create table Person
( Name varchar(10) primary key,
  Age int not null
);

create table AgeFactor
( Age int primary key,
  Factor varchar(20) not null
);

With data similar to this:

insert into Person values ('John B'  , 37);
insert into Person values ('John J'  ,  2);
insert into Person values ('Jimmy P' , 65);
insert into Person values ('Robert P', 17);
insert into Person values ('Eric C'  , 84);
insert into Person values ('Syd B'   , 42);
insert into Person values ('David G' , 50);
insert into Person values ('Roger W' , 28);

insert into AgeFactor values (0,'Infant');
insert into AgeFactor values (2,'Toddler');
insert into AgeFactor values (4,'Child');
insert into AgeFactor values (13,'Adolescent');
insert into AgeFactor values (18,'Young Adult');
insert into AgeFactor values (30,'Adult');
insert into AgeFactor values (65,'Senior');

Then my first inclination would be to write the SQL using SQL-89 style joins, like this:

select p.Name, af.Factor
  from Person p, AgeFactor af
  where af.Age = ( select max(Age) from AgeFactor 
                     where Age <= p.Age )

This statement would return a result set that looked like this:

Name       Factor
---------- -----------
John B     Adult
John J     Toddler
Jimmy P    Senior
Robert P   Adolescent
Eric C     Senior
Syd B      Adult
David G    Adult
Roger W    Young Adult
 

However, this works fine unless you have a person with an age less than the lowest age in your AgeFactor table. For example, if Infant and Toddler were not in your AgeFactor table, then John J would not be a part of your result set. Also, if a person had an unknown age, represented by NULL, then he or she would not show up in the result set. If you wanted every person to show up regardless, then your SQL might look like this:

SELECT p.Name, af.Factor
  FROM Person p LEFT JOIN AgeFactor af
    ON p.Age >= af.Age
  WHERE af.Age is null 
    OR af.Age = ( select max(Age) from AgeFactor 
                    where Age <= p.Age )

This is a variation of the first SQL, but using LEFT JOIN allows every person to show up. Checking if af.Age is null keeps the last WHERE condition from excluding rows without matching AgeFactor rows. Here is another way to write your SQL:


SELECT Name, Factor
  FROM ( SELECT Name, max(af.Age) as AgeGroup
           FROM Person LEFT JOIN AgeFactor af
             ON Person.Age >= af.Age
           GROUP BY Person.Name ) NameAge
       LEFT JOIN
       AgeFactor
    ON NameAge.AgeGroup = AgeFactor.Age

Here, the subquery gives us all of the rows from Person with their age group. Then, we simply join that to the AgeFactor table to get the needed factor(s).

These queries will most likely perform differently at varrying row counts, so I would pick the one that best fits your environment.


This was first published in February 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close