Combine Tables

[stbeaver]

I am trying to find a specific way to combine/merge/join two tables and need some direction as to how to go about creating the query. I have two tables. Both tables have the same fields. Table A has 300 records, Table B has 50 records. There is an unique ID number field to index the two tables. The data in Table B is what I will call exception data. Every index number record in Table B is also in Table A, but the rest of the data in the other fields for that record is different. I want to create a query that combines the records of the two tables. If the index number is in Table A, but not in Table B, I want that record in the query. But if the index number is in Table B, I want the query to use the record in Table B instead of Table A. The resulting query would have 300 records. I have not been successful experimenting with queries and Googling for examples of this situation. I would appreciate some insight into how I might accomplish this with Access 2003.

[pbaldy]

Create a query that joins the two tables on the index number. Edit the join so it pulls all records from A. In the appropriate fields:

Nz(TableB.FieldName, TableA.FieldName)

[stbeaver]

I can't use the Nz function. The value in Table A is never null. If there is a record in Table B with an index number 123, there will always be a record in Table A with the same index value of 123. The data for other fields for that record will be different, but never null. I just want to use the record data from Table B if it is there, Table A if its not.

[pbaldy]

You didn't try it? The Nz() is handling tableB not having a record.

[stbeaver]

I did not try it. Apparently, the right side of my brain is not working very well. That function worked beautifully. Exactly what I was trying to do. It never occurred to me to check Table B for nulls. I kept wanting to check it for data, and if present, use it. I was trying to make the solution harder than it was. Thank you very much for your expertise.

[pbaldy]

Happy to help, and welcome to the site by the way!