# merging tables in Access



## rbeaufor (Feb 7, 2003)

I am trying to combine the data from 2 tables both tables have a ID number which forms a "relationship" between the two tables. The "master" table is considerably longer than the subordinate table.

When I create a query using data from both tables, the query only shows records that are common to both tables.

What I'd like is a final result whic has All results from the master table and fills in those values from the other table.

I hope I am explaining what I'm triing to do. 
Previously, I was trying to do this in Excel:down: :down:

see this post

The point is I need to maintain the integrity of the "master" table.

Any thoughts???


----------



## Anne Troy (Feb 14, 1999)

Go to Relationships.
Link the 2 ID fields of the tables.
Make Query
Choose both tables.
Choose only ONE of the ID fields
Bring all other fields in that are unique, i.e., you don't need a name field twice, just once.

Run the query. Correct?
If yes, open it in design view again. Hit Query, Make table. Give it a table name.
Run it again.
It'll tell you it's making a table with XX records.
Else, send me the DB.
[email protected]


----------



## rbeaufor (Feb 7, 2003)

Sending now...still doesn't seem to work correctly. Only getting the common records as opposed to ALL records


----------



## Raphael (Jun 20, 2001)

A Query should do it pretty easily!!
Click on Query, click on the Wizard. It will show one table with a drop down list. Click on the double arrow, and all fields will move into the second row.
Click on the drop down list, and choose your other table. TABLES MUST BE RELATED! Click on the fields that you need added.


----------



## rbeaufor (Feb 7, 2003)

THanks Raphael

The dilemma lies not in creating the query but in getting one to keep all the records.

Say for instance you have 2 tables both identifying people by a number. In the main table you have all the inofrmation about each person listed (lets pretend there's 100 people). In another table you have one fact (column) to add about 20 of the 100 people. The rest should just have a blank space in this column.

Doing the query as described creates a table with only 20 records ( for the people who exist in BOTH tables) as opposed to 100 records which I am trying to get. 

Any idea on how I can get a resulting query with 100 records???


----------



## Anne Troy (Feb 14, 1999)

rbeaufor:

I never did get an attachment...


----------



## rbeaufor (Feb 7, 2003)

np.

I'll send again!

THX


----------



## Raphael (Jun 20, 2001)

Sorry for misunderstanding.....how about creating a Form!!!! Would that suit your needs? It would bring up All Records, not just the common ones....


----------



## Anne Troy (Feb 14, 1999)

I don't think so, Raphael. I think he needed a union query. I'm not good with SQL, so I just did it in Excel for him. He can pull that into a new table in Access. Bob: You'll need to delete that column A and let Access assign its own to your table.


----------



## rbeaufor (Feb 7, 2003)

WOW!!!!

Superb

Explain to the masses how you did that so easily?
(and so I can do it next time)


----------



## Anne Troy (Feb 14, 1999)

Lots of explaining...

I exported to Excel of course.
Copied all to one worksheet, keeping the worksheet with the additional column intact and ignoring it for now.
In sheet2 of the workbook, I sorted by Number. Of course, many of your records were duplicates.

In a blank column to the right, I put a heading called "dupe".
Below it, in D2, I left it blank. In D3, I put:

=if(B3=B2,"dupe","")

Copy that down to all cells.
Then copy all those cells, hit Edit-Paste special-Values
That removes unwanted underlying formulas.
Now sort by the column, descending, so all "dupe" come to the top.
Delete all rows that have "dupe" in column D.

Now I have: Sheet 1 with the extra column, but not all the people. Sheet 2 with all the people but no extra column. So...

I delete the old dupe column. Now I use it to get the info from that other column on the other sheet. I sort both sheets by the number column (B). On Sheet1 I select the cells that contain the number, name and new column info. I hit Insert-name-define and call it mydata.

In D2 of the second sheet, where I want the data from the other worksheet to come in, I put:

=if(isna(vlookup(b2,mydata,3,false)),"",vlookup(b2,mydata,3,false))

What does that do?

The vlookup says go to that area she named mydata. find the value in B2 (number), go across that row to the 3rd column and give me that value there. FALSE means, if you can't find that exact number, then don't give me the closest one--I only want the exact one.

The IF(ISNA part says "if you're going to return a #N/A error at me, don't do it. Instead give me "", which looks like a blank cell"

That brought over all the values from the column and put it next to the right names...

Vlookup is also explained here:

http://www.theofficeexperts.com/excel.htm#TheDreadedVlookup

So, I went ahead and got rid of the underlying formulas again by copy, edit-paste special, as values. Then I deleted the unneeded sheet because everything's all in one workbook.

Phew. My fingers hurt.


----------



## rbeaufor (Feb 7, 2003)

Brilliant...Absolutely Brilliant!!!

I now understand the *dreaded* vlookup !!!  

I wish I had known about it 6 months ago, I cannot begin to tell you how much time I've wasted lining up columns....inserting blank rows, etc...

Thanks ALOT!!!!!!


----------



## Anne Troy (Feb 14, 1999)

I hear ya. I could never UNDERSTAND Vlookup until someone explained it to me the way I explain it there, at which time it made complete sense. Vlookup can be a really great tool once you know how. Check out the free downloads at my web, like MyInvoicing, and see how it works there. It's awesome.


----------



## deej (Jun 12, 2003)

Hi

Looking at your original post on this topic it seems to me that you were on the right track with your original query but were using the wrong type of join between the two tables. If you use an 'Inner Join' (or 'Equi-Join') the query will only return records where the joined fields (ID number in your example) are common to both tables - hence in your example of a main table with 100 records and a sub table with 20 records the query will return 20 records only. This is the default join type in the Access QBE builder.

If you use an 'Outer Join' (from 'Main Table' to 'Sub Table' in your example) the query will return all the records from one side of the join (100 records from 'Main Table') and in columns that are taken from the other side of the join ('Sub Table') data will appear in those records where the joined field (ID Number) is present in both tables and a NULL (blank) will appear in the column for records that are not common to both tables. This will return 100 records in your example - 20 of which will have data in the column (or columns) from 'Sub Table'.

To create an outer join in the QBE builder drag the joined field from 'Main Table' to its partner in 'Sub Table', this will create the default inner join. Now right-click on the join line and select 'Join Properties' from the pop-up menu, this will bring up a dialog box that offers you 3 choices. 

Choice 1 is the inner join (labelled "Only include rows where the joined fields from both tables are equal").

Choices 2 and 3 are outer joins and are labelled "Include ALL records from (table name 1) and only those records from (table name 2) where the joined fields from both tables are equal".

In your example you need to select the option labelled "Include ALL records from 'Main Table' and only those records from 'Sub Table' where the joined fields from both tables are equal". Running the query should then return 100 records, only 20 of which will have data in the 'Sub Table' column(s) - the rest will have blank(s) in the 'Sub Table' column(s).

The tables do not have to be related to do this, they only need to each have fields that can be joined by virtue of having common data in them. The joined fields don't even have to have the same name (in fact it's handy sometimes if they are named differently) - they just need to have common data. Joined fields must be of the same data type - i.e. you can't join a number field to a text field.

This seems a long-winded explanation but hopefully you can follow it. If not post your SQL (in the QBE builder click on the SQL button or select 'SQL View' from the 'View' menu, highlight the SQL text and copy then paste it to wherever) and I will doctor it and repost for you to copy back.

Regards
Deej, UK


----------



## rbeaufor (Feb 7, 2003)

Deej, nice!!!

That worked as well.

Now I have 2 ways to merge my data, each infinitely useful.

Thanks,
RBB


----------



## deej (Jun 12, 2003)

You're welcome. I don't know how many thousands of SQL queries I have written in Access over the last 5 years in my job! Queries is one topic I do feel sure of ...

Regards
Deej


----------

