merging tables

[ianking]

I have two tables with identical fields.

I would like to merge them together without duplicating any records - there is one field which can be used to identify which data is to be transferred:

fields are: Barcode, Title, Author, ISBN, and Date Borrowed

Table A has some records with data only in: Barcode, ISBN, and Date Borrowed fields

Table B has records with data in: Title, Author, and ISBN fields

The barcode field is indexed and unique.


How do I merge them together so that the records in Table A contain the data from Table B whenever the ISBN data is the same, without ending up with duplicate records?

I can get a query to export the records from table A as a CSV file where the ISBN fields match (along with the data from Table B) but how do I delete this data from Table A in order to merge it with this csv file (or am I going about this the wrong way?)
Thanks,

Ian

 

[burrcm]

You would do this in several steps, starting with taking a copy of the db so if you muck up it doesn't have consequences. A find duplicate query using the the ISBN column will supply the matches, and you can include whichever fields you wish in the output. Change the query to a make-table, and create a new table with the required dataset in it. Find unmatched will do the opposite, return records which are unique. No need to alter the data in the original tables, but once you have the required dataset in a new table, the originals may be deleted.

Chris B

 

[neileg]

I assume that Table A may have duplicate ISBN data, but that Table B does not. If you create a select query joining the two tables on the ISBN then you will get the same number of records as you have in Table A. If you had duplicate ISBNs you will still have them, but the barcode field will mean that the records are not duplicated.

Or to put it another way, where's the problem?

 

[ianking]

Merging queries

Thanks - I eventually twigged to that - I was trying to merge them the other way (B int A) and kept getting the "no records added" problem - once I merged Table A into Table A all records were added except the duplicates.