merging two tables and listing all of the data

[roperj]

Alright, I don't feel like this is especially complicated, but I can't quite seem to get it right.


I have two tables of plant information: perenials and propagation

In one I have a number of fields - zone, field, row, location, Batch ID, dates, and the Primary Key Plant ID which is a concatenated field of the location numbers + Batch ID because there are multiple batches in different locations.

In the propagation table I have similar fields - seed source, start date, amt treated, etc. and Batch ID is used as the primary key for this field.


Now, I have a query that combines these tables, and matches Batch IDs of the two tables relationally so that the information matches the records are combined in the query. However, there are plant batches in propagation information that do not have a location and so have no matching BatchID in the perenial database.



My problem is this, the query will only return records that have matching BatchIDs, which is most of the information, how can I create a query that shows these two tables merged together this way but ALSO lists the BatchIDs and corresponding records from the Propagation database?




So in summary I need the query to:
* Return all fields from both perenials AND propagation log matched by their Batch IDs

*Return the fields from propagation that do not have corresponding batch ID's in perenials

 

[pbaldy]

With the query in design view, right-click on the join line between the two tables to edit it. Choose the appropriate "All records from xxx table". That will change INNER JOIN to LEFT or RIGHT join as appropriate to how you listed the tables.