ms access query problem

[Jimmy Tran]

Hi Everyone,

I am doing a small database in ms access. I have two tables: One has all
the information such as name, address, phone number, and etc. The second
table contains the keyID of the first table and some additional
information about the data in the first table( But not every record in
the first table will have additional information).
Something like this:
TABLE1:
KeyID Name Major
1 Jack PreMed
2 Tom Engineering
3 Susan Writing

TABLE2:
KeyID GPA Year
1 3.5 1999
1 3.4 2000
3 2.9 2000

When I use query:
Select Table1.Name, Table1.Major, Table2.GPA, Table2.Year
From Table1 inner join Table2 on Table1.KeyID = Table2.KeyID

I am only getting records that exists in both tables. How can I change
this query so that I will get all records from Table1 and all records
from Table2. Like this:

Name Major GPA Year
Jack PreMed 3.5 1999
Jack PreMed 3.4 2000
Tom Engineering
Susan Writing 2.9 2000

Thank you a lot for helping me out.

Jim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Steven Wilmot]

Re: ms access query problem


"Jimmy Tran" <hunganh20@yaho o.com> wrote in message
news:40a3a412$0 $208$75868355@n ews.frii.net...[color=blue]
> Hi Everyone,
>
> I am doing a small database in ms access. I have two tables: One has all
> the information such as name, address, phone number, and etc. The second
> table contains the keyID of the first table and some additional
> information about the data in the first table( But not every record in
> the first table will have additional information).
> Something like this:
> TABLE1:
> KeyID Name Major
> 1 Jack PreMed
> 2 Tom Engineering
> 3 Susan Writing
>
> TABLE2:
> KeyID GPA Year
> 1 3.5 1999
> 1 3.4 2000
> 3 2.9 2000
>
> When I use query:
> Select Table1.Name, Table1.Major, Table2.GPA, Table2.Year
> From Table1 inner join Table2 on Table1.KeyID = Table2.KeyID
>
> I am only getting records that exists in both tables. How can I change
> this query so that I will get all records from Table1 and all records
> from Table2. Like this:[/color]

left outer join

(or just left join)

[David Portas]

Re: ms access query problem

> I am doing a small database in ms access.

But you are posting in a Microsoft SQLServer newsgroup...
[color=blue]
> I am only getting records that exists in both tables. How can I change
> this query so that I will get all records from Table1 and all records
> from Table2. Like this:[/color]

It's called an OUTER join. Lookup OUTER JOINs in the Access help file:

SELECT Table1.Name, Table1.Major, Table2.GPA, Table2.Year
FROM Table1
LEFT JOIN Table2
ON Table1.KeyID = Table2.KeyID

--
David Portas
SQL Server MVP
--

[Jimmy Tran]

Re: ms access query problem

Thank You Guys!!!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!