Join two fields in one table to one field in another

[Guest]

I have two tables

Table1 fields = [Yearof], [Company]
Table2 fields = [BeginYear],[EndYear], [President] ,[Company]

I would like to join both tables in a query so that the output would contain
the year and the company and the employee listed for the years between
BeginYear and EndYyear

query1 output=;

year company president
2003 USA George Bush
2002 USA George Bush
2001 USA Bill Clinton
2001 Texas George Bush

I have no idea how to do this but I have tried joining beginyear and
endyear to yearof but it does not work. Any ideas?

 

[Marshall Barton]

I have two tables

Table1 fields = [Yearof], [Company]
Table2 fields = [BeginYear],[EndYear], [President] ,[Company]

I would like to join both tables in a query so that the output would contain
the year and the company and the employee listed for the years between
BeginYear and EndYyear

query1 output=;

year company president
2003 USA George Bush
2002 USA George Bush
2001 USA Bill Clinton
2001 Texas George Bush

I have no idea how to do this but I have tried joining beginyear and
endyear to yearof but it does not work. Any ideas?

You can not do a non-equi join in the query desifn grid.
Switch the query to SQL view and adjust the ON clause to
something like:

ON table1.YearOf >= table2.BeginYear
AND table1.YearOf <= table2.EndYear

 

[Guest]

David:

Firstly does Table1 have a row for every possible year relevant to the
company, e.g. for USA this would presumably be from 1776 (or whenever the
Presidency was created – I'm a bit sketchy on early US history) to at least
the present if all presidents were represented in the database.

If so try this:

SELECT YearOf, President, Table1.Company
FROM Table1, Table2
WHERE Table1.Company = Table2.Company
AND Table1.YearOf BETWEEN Table2.BeginYear AND Table2.EndYear;

The tables are joined in the WHERE clause here as you can't use a
BETWEEN…AND operation in a JOIN clause in Access.

Ken Sheridan
Stafford, England

 

[Guest]

This worked perfectly, thanks.

my query statement:

SELECT Year, Name, Table7.Company
FROM Table6, Table7
WHERE Table6.Company = Table7.Company
AND Table7.Year BETWEEN Table6.YearBegin AND Table6.YearEnd;
--
David McKnight

David:

Firstly does Table1 have a row for every possible year relevant to the
company, e.g. for USA this would presumably be from 1776 (or whenever the
Presidency was created – I'm a bit sketchy on early US history) to at least
the present if all presidents were represented in the database.

If so try this:

SELECT YearOf, President, Table1.Company
FROM Table1, Table2
WHERE Table1.Company = Table2.Company
AND Table1.YearOf BETWEEN Table2.BeginYear AND Table2.EndYear;

The tables are joined in the WHERE clause here as you can't use a
BETWEEN…AND operation in a JOIN clause in Access.

Ken Sheridan
Stafford, England

I have two tables

Table1 fields = [Yearof], [Company]
Table2 fields = [BeginYear],[EndYear], [President] ,[Company]

I would like to join both tables in a query so that the output would contain
the year and the company and the employee listed for the years between
BeginYear and EndYyear

query1 output=;

year company president
2003 USA George Bush
2002 USA George Bush
2001 USA Bill Clinton
2001 Texas George Bush

I have no idea how to do this but I have tried joining beginyear and
endyear to yearof but it does not work. Any ideas?