Populating a query result based on a range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to populate a query's result based on a table's range between
two fields, any suggestions on how to do this?

Example:

Table content:

Coach Begin year End Year Team
Bob 2000 2004 Red
Ron 1998 1999 Red
Bob 1999 1999 Blue
Desired Query result:

Coach Year Team
Ron 1998 Red
Ron 1999 Red
Bob 2000 Red
Bob 2001 Red
Bob 2002 Red
Bob 2003 Red
Bob 2004 Red
Bob 1999 Blue
 
I would create a table of years "tblYears" with a single field "Yr" and
values 1998-2004. Then create a query with sql like:

SELECT Coach, Team, Yr
FROM tblCoaches, tblYears
WHERE Yr Between [BeginYear] And [EndYear]
ORDER BY Coach DESC , DESC , Yr;
 
Back
Top