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.

 

[Guest]

1. In the database window go to queries and click on new
2. Add your 2 tables to the query designer
3. Join you 2 ID fields (drag 1 ID field name to the other ID field name)
4. Double click on the join line itself and click in "Include all recods
from the 'Users" table (this gives you an outer join so that you include all
the records in users, and only those records in Status where the ID filds
match)
5. Double click on each filed name required (ID, name, password, birth,
status) to add the fields to your query
6. On the menu select Query / Make-table query and enter the name of your
new table when prompted
7. Run your query

 

[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