Merging and deleting duplicate records

[Rider]

Hello!



I have an Access table with a number of records which refer to the
same [InvoiceNo] but with data in different fields. Normally, my database works like this:
1)Import data from Excel File to tblMain in Access
2)Import data from Excel File to tblVendorLine in Access
3)Import data from Excel File to tblDunning in Access
4)Query is sorting from two first tables only records that refer to the third one (by InvoiceNo)
5)We have only items from tblDunning with data from other two tables to be send (mail to the vendor).

The point is, that when I have relation between those three tables, I receive only few records, but sometimes - for example - tblDunning refers to 5 records in tblMain and to 7 records in tblVendorLine - but there is only 4 records related between tblMain and tblVendorLine, so query shows me only two records. Ive made a query for each table (tblMain & tblVendorLine - relation with tblDunning). Then make one query of those two queries. It append data to new table tblDunnAll


So for example the table would look like this after

Vendor | InvoiceNo | CoCode | Status | Clearing_Date | PBk
100000 23333 GB11 23/12/2011
233332 121212 GB18 16/12/2011
233332 121212 Complete K
134444 777777 GB12 Complete
100000 23333 GB11 Complete K

What I'd like to have is a table with:

Vendor | InvoiceNo | CoCode | Status | Clearing_Date | PBk
100000 23333 GB11 Complete 23/12/2011 K
233332 121212 GB18 Complete 16/12/2011 K
134444 777777 GB12 Complete

I'm not sure how to go about merging the data for each name into one
record. There are lots of very helpful posting about using SQL to
delete duplicate but I can't find a posting with addresses this issue.

Any suggestions for merging and purging would be appreciated. I'm
happy to be pointed to VB, SQL or whatever.

Thanks in advance.

Matthew

[aytee111]

Query 1: (empty table) populate vendor and invoice, use grouping so only one record per

query 2: link on vendor and invoice, update CoCode if it is not null

query 3: link on vendor invoice, update status if it is not null

query 4: link on vendor and invoice, update date if it is not null

etc