easy query question....i hope

[Ferntown]

I have 2 tables that I need to join. Both tables have the same primary key (ROLL_NUM). The first table (ROLL-CURRENT)has a complete list of all of the companies roll numbers. The second table has only the certain roll numbers which have a certain value (YES). I want to create a table that takes all of the data from the first table (ROLL_NUM) as well as the IMPROVEMENT records from the second table???? Hope that makes sense, here is the table structure:

ROLL_CURRENT table:
ROLL_NUM LOCATION NAME YEAR
10011 yyyyy xxxx 2001
10012 yyyyy xxxx 2001
10013 yyyyy xxxx 2001
10014 yyyyy xxxx 2001

IMPROVEMENTS table:
ROLL_NUM IMPROVEMENTS
10011 yes
10014 no

there are no entries for ROLL_NUM's 10012 or 10013.

RESULT:

NEW TABLE:

ROLL_NUM LOCATION NAME YEAR IMPROVEMENTS
10011 yyyyy xxxx 2001 yes
10012 yyyyy xxxx 2001 yes

 

[BNPMike]

Your sample data doesn't agree with your question.

If you want all data from ROLL_CURRENT plus any matching data from IMPROVEMENTS then use a left join. Click on the join line in Query Design and select what you want.

 

[TonyJollans]

Hi Ferntown,

How do you come by your result table? Why is 10012 (with IMPROVEMENTS = yes) in it?

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[BNPMike]

I guess he meant:

ROLL_NUM LOCATION NAME YEAR IMPROVEMENTS
10011 yyyyy xxxx 2001 yes
10012 yyyyy xxxx 2001
etc

 

[TonyJollans]

Maybe, but what does he want for ROLL_NUM 10014?

I think this SQL ought to do it ..

[blue]

SELECT [ROLL_CURRENT].ROLL_NUM, 
       [ROLL_CURRENT].LOCATION, 
       [ROLL_CURRENT].NAME, 
       [ROLL_CURRENT].YEAR, 
       [IMPROVEMENTS].IMPROVEMENTS

FROM   [ROLL_CURRENT] 
  LEFT JOIN [IMPROVEMENTS] 
      ON  ([ROLL_CURRENT].ROLL_NUM = [IMPROVEMENTS].ROLL_NUM 
      AND [IMPROVEMENTS].IMPROVEMENTS = Yes);

[/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[Ferntown]

Thanks for the help. I was trying to hard to do something easy....I need a vacation. All I needed to do was a join.
Thanks again.