Merge 2 tables with conditions

[Vicente]

Hi!

I have 2 tables:

Table Users:
ID
Name
Password
Birth

Table Status:
ID
Status

Both tables are related by "ID" field, but both tables have not the
same number of elements

I want to obtain a new table with this structure:

Table Result:
ID
Name
Password
Birth
Status

As I said before both tables have not the same number of elements
(rows) so in my result table, "Status" field should be empty for those
"ID"'s not found in "Status" table.

Example:

Users: 1 2 3
John Mary Peter
1234 3333 3456
5/11/81 9/3/80 7/7/69

Staus: 1 3
CODE1 CODE5

Result: 1 2 3
John Mary Peter
1234 3333 3456
5/11/81 9/3/80 7/7/69
CODE1 (EMPTY) CODE5

I am working with access, but I have knwoledge of SQL.

Thanks in advance.

[Tom van Stiphout]

On Tue, 06 Nov 2007 00:26:48 -0800, Vicente <uc***@ono.comwrote:

You need an outer join.
Create new query select both tables draw a line between the two ID
fields if it's not created automatically right-click the line >
Choose 'Include all from Users and only ...'

-Tom.

>Hi!

I have 2 tables:

Table Users:
ID
Name
Password
Birth

Table Status:
ID
Status

Both tables are related by "ID" field, but both tables have not the
same number of elements

I want to obtain a new table with this structure:

Table Result:
ID
Name
Password
Birth
Status

As I said before both tables have not the same number of elements
(rows) so in my result table, "Status" field should be empty for those
"ID"'s not found in "Status" table.

Example:

Users: 1 2 3
John Mary Peter
1234 3333 3456
5/11/81 9/3/80 7/7/69

Staus: 1 3
CODE1 CODE5

Result: 1 2 3
John Mary Peter
1234 3333 3456
5/11/81 9/3/80 7/7/69
CODE1 (EMPTY) CODE5

I am working with access, but I have knwoledge of SQL.

Thanks in advance.

[Michel Walsh]

SELECT users.id, users.name, users.password, status.status, users.birth
FROM users LEFT JOIN status
ON users.id = status.id

In the query designer, you can switch from the SQL view to the graphical
view to see how your could have done it, graphically.
Hoping it may help,
Vanderghast, Access MVP
"Vicente" <uc***@ono.comwrote in message
news:11**********************@d55g2000hsg.googlegr oups.com...

Hi!

I have 2 tables:

Table Users:
ID
Name
Password
Birth

Table Status:
ID
Status

Both tables are related by "ID" field, but both tables have not the
same number of elements

I want to obtain a new table with this structure:

Table Result:
ID
Name
Password
Birth
Status

As I said before both tables have not the same number of elements
(rows) so in my result table, "Status" field should be empty for those
"ID"'s not found in "Status" table.

Example:

Users: 1 2 3
John Mary Peter
1234 3333 3456
5/11/81 9/3/80 7/7/69

Staus: 1 3
CODE1 CODE5

Result: 1 2 3
John Mary Peter
1234 3333 3456
5/11/81 9/3/80 7/7/69
CODE1 (EMPTY) CODE5

I am working with access, but I have knwoledge of SQL.

Thanks in advance.