Querying from 2 tables

[egnaro]

I have a SQL question and was hoping someon can help out. I have two tables with the exact same column headings. What is the most efficienc/effective method (if there is one) of querying for rows from the two separate tables into one data set based upon common data within a single column.



In other words, if tables X and Y both have columns a, b, c and d is it possible to create another table (or query for rows) based on data that appears in, say, column b within both tables?

[ConneXionLost]

Hi egnaro,

Use a Make Table query to create your "Z" table from the records selected from your "X" table, then use an Append query to add the records from your "Y" table.

Cheers,

[egnaro]

Thanks for the response. However, I am still scratching my head a little as my problem is i do not know which of the rows within X and Y will be in the final result...here is an example -
Table X

[egnaro]

Thanks for the response. However, I am still scratching my head a little as my problem is i do not know which of the rows within X and Y will be in the final result...here is an example. Assume these are my two tables. What I'd like to be able to do is capture the data that's colored (because apple and banana are the itemss that show up under column a of both tables, whereas orange, grape, kiwi is only in one of the tables and not the other). Thanks again!
Table XTable YColumn aColumn bColumn aColumn bappleCarappleTruckappleCarappleTruckappleCarbananaTruckorangeCarbananaTruckorangeCarkiwiTruckbananaCarkiwiTruckbananaCarkiwiTruckbananaCarkiwiTruckbananaCarkiwiTruckgrapeCar

[egnaro]

Table XTable YColumn aColumn bColumn aColumn bappleCarappleTruckappleCarappleTruckappleCarbananaTruckorangeCarbananaTruckorangeCarkiwiTruckbananaCarkiwiTruckbananaCarkiwiTruckbananaCarkiwiTruckbananaCarkiwiTruckgrapeCar

[egnaro]

Table XTable YColumn aColumn bColumn aColumn bappleCarappleTruckappleCarappleTruckappleCarbananaTruckorangeCarbananaTruckorangeCarkiwiTruckbananaCarkiwiTruckbananaCarkiwiTruckbananaCarkiwiTruckbananaCarkiwiTruckgrapeCar

[egnaro]

Let me try again...

Table X Table Y
Column a Column b Column a Column b
apple Car apple Truck
apple Car apple Truck
apple Car banana Truck
orange Car banana Truck
orange Car kiwi Truck
banana Car kiwi Truck
banana Car kiwi Truck
banana Car kiwi Truck
banana Car kiwi Truck
grape Car