Merge 2 tables without duplicates (1 Viewer)

[DavRob]

Hi
I know that has been asked many times before as I have read through almost all of them, but I just cannot get it to work.

I have 2 tables Payments and Payments_Import I have a web portal for my work, any payments made to me is listed in a spreadsheet in the portal, which I copy into an Excel spreadsheet, that is then imported into a table "Payments_Import" in my DB I then append the data into the Payments table, this is prone to duplicate records.

There is 8 fields in each table the main fields are Job_No and Payment

Initially I had the Job_No field set as the Primary Key this worked fine as at the time there was 1 payment against a Job No, things have now changed and I now can have several payments against 1 job no.

I have tried several methods to achieve this

 

[June7]

What constitutes a 'duplicate' - JobNo/InvoiceNo/Payment/DateSubmitted?

Can set a multi-field index to prevent duplicates.

 

[isladogs]

See if my 2 part web article helps

Synchronise Data 1

This is the first part of an article exploring explores some of the methods that can be used to synchronise data with an external source. The advantages and disadvantages of each method are also discussed.

www.isladogs.co.uk

 

[Eugene-LS]

... I have 2 tables Payments and Payments_Import I have a web portal for my work, any payments made to me is listed in a spreadsheet in the portal, which I copy into an Excel spreadsheet, that is then imported into a table "Payments_Import" in my DB I then append the data into the Payments table, this is prone to duplicate records.
...

If I understood your task correctly, then you should use two queries of something like this:
First: UpdateQuery

UPDATE Payments
    INNER JOIN Payments_Import
    ON (Payments.DateSubmitted = Payments_Import.DateSubmitted)
        AND (Payments.Payment = Payments_Import.Payment)
        AND (Payments.Job_No = Payments_Import.Job_No)
SET Payments.Approved = [Payments_Import].[Approved],
    Payments.DatePaid = [Payments_Import].[DatePaid],
    Payments.InvoiceNo = [Payments_Import].[InvoiceNo];

Second: InsertQuery

INSERT INTO Payments
    ( Job_No, Payment, Approved, DateSubmitted, DatePaid, InvoiceNo )
SELECT Payments_Import.Job_No, Payments_Import.Payment, Payments_Import.Approved,
    Payments_Import.DateSubmitted, Payments_Import.DatePaid, Payments_Import.InvoiceNo
FROM Payments
    RIGHT JOIN Payments_Import ON
    (Payments.DateSubmitted = Payments_Import.DateSubmitted)
    AND (Payments.Payment = Payments_Import.Payment) AND (Payments.Job_No = Payments_Import.Job_No)
WHERE (Payments.PaymentsID Is Null);

 

[jdraw]

Each of your tables should have a primary key.
InvoiceNo is dimmed differently in each table which is not allowing a join.
See June's response in post #2.

I think appending the imports to your existing payments is an incorrect process.
Eugene_LS has separated imports into updates to existing records and inserts for new records.
This sounds correct. But we really need to hear more about the web portal and how Jobs, Invoices etc fit in your "business".

 

[Pat Hartman]

If you can identify uniqueness, then add the correct unique index to the table. To make a multi-column unique index, you MUST use the indexes dialog regardless of how you do the import.
.

 

[DavRob]

Thank you for the prompt replies

Jdraw One of the companies that I contract to uses a web portal to administer our up-coming jobs, invoices, payments etc, in regards to invoicing they use the Recipient Created Tax Invoice (R.C.T.I.) method which common here in Australia Ie they create our invoices then we approve them prior to submitting for payment on a fortnightly basis

What I do is copy the current list of up-coming jobs, the list of completed jobs and the invoice list to an Excel spreadsheet then import into my DB into the these tables "Customers", "Completed_Jobs" and "Payments" then I run a series of queries the Job_No field is the field that is constant in all 3 tables the tables

In the case at hand I am using the Job_No and The Payment fields in a query to sort for un-invoiced jobs, as I stated originally things have changed from having an agreed set payment per job to a payment that has an agreed set payment per job plus additional payment for travel, incentives etc

For example Job_No 1978463 would have a base payment of $350.00
Travel $50.00

The base payment remains constant depending on to job performed
For example
Job A $350.00
Job B $400.00

So there will be many records in the payment column that are duplicate, therefore I need to check for duplicate records on the Job_No and Payment fields

Hope this makes sense

 

[jdraw]

@DavRob

Have you looked at the suggestions from others?
Isladogs has provided a link to several well-described examples.
You should review composite unique index to prevent duplicates.
Your database in #1 does not show all parts of your set up.

If the suggestions are not helping, then I suggest you give us more representative info and database and tables including "Customers", "Completed_Jobs" and "Payments". The data doesn't have to be real, but it does have to be consistent throughout your sample/examples so readers see the data and logic in context.