Connection and updating records
Here's my problem:
I have table A with fields: PO, PART, QTY, ID
Table B consists of: PART, QTY, ID
In table A, ID is blank and I'd like to update the record with ID from table B. In table B ID is a unique number.
The relationship between the 2 tables is one-to-many on the PART field with table A on the one side.
What I want to do is for each record in table A insert into the ID field, the lowest ID from table B where field PART is same and if QTY
for ID in table B is less than QTY in Table A, insert another line but with the next available ID from table B.
This is a frontend backend project with Ms Access as frontend and MySQL backend.
Anyone got an idea of how to achieve this?
Cheers / Fred