Linking two tables on a range of data.

[hswerdfe]

I amusing an MS Access 2003 and I
I have 2 tables The first one is "people" and looks like this

First_N, Last_N, Age
================
sue, smith, 23
bob, smith, 42
sherri, smith, 81
bill, black, 9
john, black, 51
sherri, black, 22

My second table is "AgeRange" and looks like this

MinAge, MaxAge, AgeType
======================
0, 30, young
31, 65, middle
66, 150, old


I am looking for a query that tells me how many people of each last
name are of each AgeType.

the output I am looking for would look something like this.

Last_N, AgeType, Number
=====================
smith, young, 1
smith, middle, 1
smith, old, 1
black, young, 2
black, middle, 1
black, old, 0

I don't care if rows with zeros are omitted or not.
I know how to do the grouping and the counting, but not how two link
two tables with a range of values like this.

If anybody has a solution or a good tutorial on this I would
appreciate any help you can give me,
Note I am looking for a general solution as my actual range table has
13 rows not 3 and my main table has something like 500,000 rows.


Thanks in advance,

Howie

 

[Jeff Boyce]

Howie

I can't tell if the example you gave is real or imaginary. If real, you
have a serious problem with your tblPeople.

Folks "age" every day. Recording someone's "age" may only be valid for ONE
DAY! Are you prepared to go back through your table every day and update
folks' ages? You're much better off storing DateOfBirth and calculation Age
from that.

It seems to me you could create a query and use selection criteria related
to the (calculated) Age and the Age Range values. For example, Sue Smith's
comparison would be to find the AgeRange for which Sue's age (23) is less
than the Max and greater than the Min.

Is that how you are looking to make the comparison?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I amusing an MS Access 2003 and I
I have 2 tables The first one is "people" and looks like this

First_N, Last_N, Age
================
sue, smith, 23
bob, smith, 42
sherri, smith, 81
bill, black, 9
john, black, 51
sherri, black, 22

Well... storing the age will guarantee one thing: every single record in your
table will contain incorrect data within one year. Consider instead storing
the birthdate, or (if that's not available) the birth year.

My second table is "AgeRange" and looks like this

MinAge, MaxAge, AgeType
======================
0, 30, young
31, 65, middle
66, 150, old


I am looking for a query that tells me how many people of each last
name are of each AgeType.

odd... surely you could have unrelated people all named Smith? But ok...

the output I am looking for would look something like this.

Last_N, AgeType, Number
=====================
smith, young, 1
smith, middle, 1
smith, old, 1
black, young, 2
black, middle, 1
black, old, 0

I don't care if rows with zeros are omitted or not.
I know how to do the grouping and the counting, but not how two link
two tables with a range of values like this.

A "Non Equi Join" query is the solution here. Create a query joining your
first table to AgeRange - initially just link Age to MinAge. Then go into SQL
view and edit the JOIN clause from

INNER JOIN AgeRange ON People.Age = AgeRange.MinAge

to

INNER JOIN AgeRange ON People.Age >= AgeRange.MinAge AND People.Age <=
AgeRange.MaxAge

You can then use a totals query, grouping by Last_N and AgeType.

 

[hswerdfe]

Howie

I can't tell if the example you gave is real or imaginary. If real, you
have a serious problem with your tblPeople.

Folks "age" every day. Recording someone's "age" may only be valid for ONE
DAY! Are you prepared to go back through your table every day and update
folks' ages? You're much better off storing DateOfBirth and calculation Age
from that.

It seems to me you could create a query and use selection criteria related
to the (calculated) Age and the Age Range values. For example, Sue Smith's
comparison would be to find the AgeRange for which Sue's age (23) is less
than the Max and greater than the Min.

Is that how you are looking to make the comparison?

Regards

Jeff Boyce
Microsoft Office/Access MVP

yes made up the example, I am actually recording a defined numbered
geographical location, which aggregates to a larger geographical
location depending on its range, but I didn't want to bother
explaining all that. and I am not recording peoples First name and
Last Name but businesses Name and Business Type. All of which is
irrelevant to the question at hand.

Anyway I agree I could probably make several queries one each for
"young", "middle", and "old", and then make a join query, but I was
hoping to do it in such a way that if I changed the number of
groupings the query would not have to be rebuilt.

 

[hswerdfe]

Well... storing the age will guarantee one thing: every single record in your
table will contain incorrect data within one year. Consider instead storing
the birthdate, or (if that's not available) the birth year.




odd... surely you could have unrelated people all named Smith? But ok...




A "Non Equi Join" query is the solution here. Create a query joining your
first table to AgeRange - initially just link Age to MinAge. Then go into SQL
view and edit the JOIN clause from

INNER JOIN AgeRange ON People.Age = AgeRange.MinAge

to

INNER JOIN AgeRange ON People.Age >= AgeRange.MinAge AND People.Age <=
AgeRange.MaxAge

You can then use a totals query, grouping by Last_N and AgeType.

Thanks, John this seems to work perfectly (just quick testing) with
one small exception, my version of Access gives me an error when I try
to switch back to design view, after changing to any form of "Non
Equi Join", so basically I have to do the work in SQL view.

Thanks again

Howie

 

[John W. Vinson]

Thanks, John this seems to work perfectly (just quick testing) with
one small exception, my version of Access gives me an error when I try
to switch back to design view, after changing to any form of "Non
Equi Join", so basically I have to do the work in SQL view.

That's not an error - it's just one of several limitations of the query-design
grid view. The query grid is just a tool to build SQL; the SQL is the *real*
query. A non-equi join query, a UNION query, and probably a few others can be
manipulated in SQL view but are beyond the capabilities of the grid.