How can I merge data with duplicate PKs?

I have an Access 2003 application that contains a table for recording payments received by post. The Primary Key of this Payments table is an autonumber field called PaymentID.

I also run an ASP.NET website that has an SQL Server Payments table, in this case for recording PayPal payments. It also has an autonumber PaymentID PK field (aka an 'identity' column in SQL Server terminology).

My plan is to merge the online payment data with the postal payment data on the Access version of the Payments table to enable various Access forms and reports to show all payments, regardless of source. For this to work, I need to end up with a single PaymentID column as PK.

However, my problem is that at present there is duplication of PaymentID numbers between Access and SQL Server as there is no mechanism in place to prevent this. In other words, there could be an online payment record with a PaymentID of 1234 that is totally unrelated to a postal payment with the same PaymentID. I cannot therefore merge the data without modifying the PKs in some way.

I would be very grateful for any suggestions on how to address this problem.

David


BTW, this thread is an offshoot of a previous thread at http://www.access-programmers.co.uk/forums/showthread.php?t=186837. In that previous thread I was asking for guidance on how to implement a specific solution to this problem. However, I now think I was premature in selecting that solution, which is why I have now posted my original problem.

Couple of things to digest.

Where does the payee reside? Access? Internet? Both?

When payments are being recorded whoever is making the payment is known as the Payee. They should have a primary key.

When the payee pays for something that something must have some sort of product code, identifier that would act as a PK

So all transactions, whether by post or online should have the related keys against them. Therefore you can group payments by payee by payment source (Post/Paypal) and ofset them against the product or service they are paying for.

David

Hi David,
Both the website and the Access app are there to support an annual International photographic competition. The payees are identified by an EntrantID number and steps were taken from the outset to ensure that EntrantIDs allocated by the website for online entrants would never overlap with EntrantIDs allocated by the Access app for postal entrants. A high proportion of entrants return every year so the number of new EntrantIDs each year is relatively small (which helps to make the rather kludgy process for preventing duplicated numbers work without problems).

What they pay for is entry to the current competition, identifed by CompID. In some cases we can have more than one payment from a given entrant for a given competition. All the online data is merged with the matching tables in Access. However, the Payments table is the only one giving me problems.

I didn't really understand what you meant by "So all transactions, whether by post or online should have the related keys against them. Therefore you can group payments by payee by payment source (Post/Paypal) and offset them against the product or service they are paying for". Please clarify.

David