Merging and deleting duplicate records

[RiderOfTheli]

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

 

[Privateer]

Matthew,
I want to say I am not testing this beforehand, but I think it is what you are looking for. Create a query with all three tables. Put Dunning in the middle and draw the relationship lines from the Dunning invoice number to the invoice numbers in Main and VendorLines. Now, carefully mouse over one of the lines and right click on it and select Join Properties, then option two, all records from Dunning and any that match the other table. Repeat this for the other line. You should get an arrow on the line, aimed at the outer tables. Pull down the fields you need. When you pop the query you will get many duplicates. Go back to the design mode and mouse over the upper half of the query but not on a table. Right click and select properties. Change the "Unique Values" to yes, which should get rid of the dupes.

Now a few comments. This answer is predicated on Dunning have all invoice numbers. If this is false, (Main or Vendor has an Invoice number not in Dunning) you will need to create a table with a full list of all invoices. Then use this table as the central table in the query with the other three linked to it, with the arrows point out from the center table. Also, play with the Unique Values and Unique Records option. One works better than the other and I am still confused as to how they differ.

One last thought, consider using vlookup in Excel to merge the Main and Vendor information on the Invoice number before you import the stuff to Access. Create a blank sheet and copy all the invoices from Main and Vendor to into the same column. To get a unique copy of the invoices, do a data filter, advanced filter and select unique records. Then use vlookup to "pull" the fields from Main and Vendor that you need into this blank. Sometimes a little prep work in Excel can go a long way.
Cheers,
Privateer

 

[RiderOfTheli]

Privateer,

Thank you very much, unfortunately it's not working - and I dont know why...hmm. Ive got also this code:

Private Sub Command15_Click()
'On Error GoTo cmd_End_Click_Err
Dim tdf As TableDef
Dim fld As Field
Dim dbs As Database
Set dbs = CurrentDb
Dim strSQL As String
Dim sf As String
'Dim sf1 As String

Set tdf = dbs.TableDefs("tbl_dunnAll")
For Each fld In tdf.Fields
sf = fld.Name
If sf <> "Vendor" Then
'Name and Field1 already exist when you created
'the new table. Only update the other fields.
strSQL = "UPDATE tbl_Final INNER JOIN tbl_dunnAll " & _
"ON tbl_Final." & sf & " = tbl_dunnAll." & sf & _
" SET tbl_Final." & sf & " = tbl_dunnAll." & sf & _
" WHERE ((Not (tbl_dunnAll." & sf & ") Is Null));"
' And sf <> "Reference" And sf <> "Email" And sf <> "CoCode" And sf <> "Rej_Ind" And sf <> "Email" And sf <> "CoCode" And sf <> "Rej_Ind" And sf <> "Reject_Comment" And sf <> "St" And sf <> "Vendor_Name" And sf <> "Status" And sf <> "Amount" And sf <> "Rep_Date" And sf <> "PBk" And sf <> "S" And sf <> "Comment" And sf <> "Clearing" And sf <> "InvoiceNotPosted" And sf <> "Pstng_Date" And sf <> "Net_due_dt" And sf <> "Amt_in_loc_cur" And sf <> "Reason_Of_Rejection" And sf <> "Overdue" And sf <> "Posted" And sf <> "Paid" And sf <> "Blocked" And sf <> "Payment_Date" And sf <> "Action" And sf <> "Clrng_doc" And sf <> "User" Then
dbs.Execute strSQL
End If
Next
Set tdf = Nothing
End Sub

but still, it doesn't update tblFinal...
(for the rest, tbl's name are changed)

Best regards

 

[Privateer]

Matthew,
Your original post was " how to go about merging the data for each name into one record" and the suggestions I gave you should have worked. Telling me it didn't and going hmm, does not help. What exactly did not work? Did you get a list of all invoice numbers in one table? Would you be willing to post the tables?
Also in your original request was "Query is sorting from two first tables only records that refer to the third one (by InvoiceNo)" and "query shows me only two records". That is what a query is supposed to do. It shows you only the amount of records that appear the least amount of times. If invoice 121212 is in Main ten times, Dunning five and Vendor twice, you will get only two records. Again, to fix this you need to create a table that has all invoice numbers in there, a master invoice table if you will. Then you do an outer join on the three others and you can get the results you are asking for. Reread my first post, the first paragraph explains this in detail.
Good Luck
Privateer