I have two tables: one is generated from and ODBC datasource (Table A). The other is a normal access table (Table B). The field in the odbc table that is needed to join is being uploaded as a text record, and is centered upon the upload. I had to set the format property to 'fixed' in this field property.
In order to 'archive' the odbc data, I created an append query to a newly created access table (Table C, insuring all properties are mirrored).
Problem is when I'm trying to join Table A with Table C. With the set field to join, there is no data being transfered (only showing records that are equal).
I have done hundreds of joining tables and field, but can't figure this one out. I have tried changing settings, uploading data, properties, but nothing seems to help. It has to be something simple, just can't put my thumb on it.
If it is only showing records that are equal, then you are using an inner join. If you want all records in one table and only those that match from the other then you will need to do an outer join -- either a left or a right join.
The query below shows all unused vendors:
FROM PurchaseOrders RIGHT JOIN Vendors ON PurchaseOrders.Vendor = Vendors.Code
WHERE (((PurchaseOrders.Vendor) Is Null));