Joining 2 tables

[Mhypertext]

I have a table of 2506 records
and a table with 53754 records

I need to join them together

I have a primary key but some of the records from the 2506 table wont be in the 53754 table how do i merge the tables to together regardless if there is no match

basically i need one table with all the data

 

[DCrake]

First create an unmatched query through the query wizard to find the records that don't appear in the larger table. Then use the results of this query as the basis for an append query into the source table. This way you will only have all the record sin one table.

 

[MSAccessRookie]

I have a table of 2506 records
and a table with 53754 records

I need to join them together

I have a primary key but some of the records from the 2506 table wont be in the 53754 table how do i merge the tables to together regardless if there is no match

basically i need one table with all the data

A Query that performs a Left Join between the tables might get you all of the records that you want, and then you can create an Append Query based on the to create the Merged Table. The code below has not been tested, but should provide a good start.

Insert Into tblNewTable
(Select * 
From tblWith53754Recs 
Left Join tblWith2506Recs On {Whatever they join on})

 

[gemma-the-husky]

you can do a lot of this visually, but some of it isnt obvious

in the query pane, dbl-click the line that joins the tables, then select a different option

"select ALL from one table and everything that matches etc", in your case select all from the 2506 records table - this is a left join, and this will give you all the master records, even where there are no related records, as rookie suggests.

out of interest, just put in any column from the right table (the ID column is best, but any will do), and enter NULL in the criteria for that column.

This query will just give you ONLY the records in the master table that have no matches (this is an unmatched query), which is very useful sometimes

Also rightclick a blank area of the query, and then look at properties.
One property is "unique values". This is useful , and just gives you one example of each different combinations you have in your joined tables, rather than every row

finally change a query to a "totals" query (click the sigma icon). This offers loads of statistical options (count, sum, max, min) being the most often used ones. with a count, putting ">1" in the critera row is useful to find duplicate rows.