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.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.