Getting Data from 2 Tables

[Carly]

I currently have 2 tables in a query containing the sames columns but different data. What I need to do is create a table containing all the data from both tables (i.e. all the ones that match, all the ones from the left table that don't match in the right & vice versa)

Is there anyway of doing this?

Kind Regards
Carly

 

[Shadez]

think what your looking for is called a 'UNION' query

ie

select * from someTbl UNION select * from someOtherTbl

its something like that, cant remenber exactlty. Look it up in access help




ShadeZ

 

[neileg]

Do you want to create a query that combines the data so you can run it anytime you want, or do you want to create a single table that contains all the data as a one off exercise, or to update that table periodically?

In the first case Shadez is right, you need a union query.

If the second, you want to use an append query to add the data from table 2 to table 1.

However, your question worries me, since your language is suggesting a join between the tables. This is not appropriate if your columns are the same but the rows are different. It is appropriate if the rows are the same (or at least have some matching data) but the columns are different.

Do you want to clarify?

 

[R2D2]

I frequently have to deal with this issue. The way I do it is to do it in 2 steps. First, I create a make-table query that has the outer joins pointed one way (which will get all records that exist in both tables and those that exist in just one of them). Then I use a 2nd query to append records to this new table that exist only in the other one.

For example, if I want to combine records from Tables A and B in this manner, I will first run a make-table query that has the outer joins set up so that it includes all records in both A and B, and those records only in A. This will create table C. My 2nd query (the append query) selects the records that are only in B by using an outer join between table B and table C, and setting the criteria on all the fields in table C that are part of the Joins to "is Null" (which will cause the query to drop all records that are in both C and B, leaving you with only those records that appear only in C).

This is hard to explain, but I hope this makes sense. I just started a thread trying to get some ideas for how to do this when I'm joining together 5 tables without going through 30 some-odd steps to deal with all the permutations of which tables the records may or may not be in.

 

[C-PRS]

under edit goto replace and replace the same type with one