combining tables with different primary keys

[roper]

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

[weekend00]

use outer join query.

[NTC]

When you make a join in query design between two tables - the default join is a line and it will result in records for which have the same value.

you can put your cursor on that join line, right click, and change it to be ALL the records of one table (or the other) and that line will now have an arrow.

Access has embedded both duplicate and nonmatching query wizards.

You should have an Access textbook in the version you are using, available from Amazon or any big book store - and they will have examples of all these issues for you.