Newbie needs help appending table

[hara]

Hi there,

I am brand new here and pretty much a week working with Access. And of course I ran into a problem. I want to combine two tables using Access into one.

Table 1 has 22,000 records:


ID; X1; X2; X3; X4; X5; X6; X7; X8

Table 2 has 2,000 records:
ID; Y1; Y2; Y3; Y4; Y5

ID is not the Access ID for records, but a user ID and is the common element. I want to create a new table that would look like this:

ID; X1; X2; X3; X4; X5; X6; X7; X8; Y1; Y2; Y3; Y4; Y5

Any ID that has nothing for Y1-Y5 should leave that part of the record empty.

Any idea?

Thanks!

[pbaldy]

Well, a query that joins both of those tables together could produce that result. It would look like:

SELECT Table1.ID, Table1.X1, Table2.Y1
FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID

The key is the LEFT join, rather than the default INNER join. I left out all the other fields because I'm lazy. If you want to use that query to create or populate a new table, you should be able to.

[hara]

Tried that, but it returns a table that consist only of all records that are in Table2 (2,000). I am still missing the other 20,000 from Table1 in the new table.

[pbaldy]

Yes, that is the join issue I mentioned. If you're in query design view, right click on the line between the tables to edit the join. Choose the appropriate "Include all records...".

[hara]

Ah, I understand!

Thanks a lot, that helped!

By the way, any suggestions on how to learn Access?

[pbaldy]

No problem, and welcome to the site by the way! I learned via sites like this and the "throw him in the middle of the pond to teach him how to swim" method. I have some helpful info and FAQ's at my website below, plus these are good references:

The Access Web - Welcome
Allen Browne's tips for Access users

[hara]

Awesome! Thanks! Any suggestions on SQL?