Adding records to an existing table using another table with different

[rmork]

I have two tables:
Table one:

invoiceID | projectID | invoiceNumber | InvSum | paidDate | etc...

Table two:

Projectid | inv.numb.1 | inv.sum1 | paidDate1 | inv.numb.2 | inv.sum2 | PaidDate2 | inv.numb.3 | inv.sum3 | paidDate3 etc...

I would like to update table one with the records in table 2.
In table one, InvoiceID is autonumber and key.
Every project can have more than one invoice (up to 7), or the invoice can be paid in parts. That is wy table two has one projectID and many invoices.
For every invoicenumber, invoiceSum and paidDate I would like to add a line to the existing table one and add a new InvoiceID (Autonumber). Can this be done?

 

[lespaul]

Is it too late to change the table design? A more normalized database would be set up more along these lines and would follow the rules defined in the Database Fundamentals document linked below:

tProject
ProjectID (PK)
ProjectName
StartDate
Other info about the project

tInvoices
InvoiceID(PK)
ProjectID(FK)
VendorID(FK to tVendor not shown)
InvoiceDate
InvoiceAmt

tPayments
CheckID(PK)
InvoiceID(FK)
CheckAmount
CheckDate



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[rmork]

I have thought about it, but since I'm not the one who designed it in the first place, and this is the only thing I'm going to change, I'll rather give this a try first...

You think it can be done?