need some SQL Update Help

[Ed Cohen]

I have a Access 2000 db and I have these 2 tables.

ArchInvoices
---------------
InvoiceID - Primary Key - Autonumber
InvoiceNumber - long
SaleType - char(1)
TransactionDate - DateTime
Discount - Integer
WholesaleFlag - Boolean
PaymentMethod - Char(6)
CustPONumber - Char(30)
ShortName - Char(30)
MdCountCode - char(2)
TaxFreeID - char(30)
Paid - Boolean
FestInvoiceNumber - long

The second Table is:

ArchLineItems
---------------
TransactionType - char(1)
Units - integer
UnitCost - Double
BottleSize - Double
LineItemId - PrimaryKey - Autonumber
InvoiceNumber - Long - Foreign Key Field from ArchInvoices
Productcode - char(6)
TransactionDate - DateTime
MdcountyCode - char(2)
ShortName - char(30)
Wholesalerflag - Boolean
Discount - integer
TaxFree - Boolean

Now, the two tables are in a 1 to Many relationship. For every 1 ArchInvoice
record there are Many ArchLineItems records.

Now, I need to take the ArchLineItems.TransactionDate and set it to the
ArchInvoices.TransactionDate.

I have tried many update statements and Access keeps on giving me an error
saying missing 1 parameter. I need some sort of Update SQL that will do this.
Can anybody help. Yes, I know the tables need to be normalized. Thanks in
advanced.

 

[Tom van Stiphout]

On Tue, 25 Sep 2007 06:06:02 -0700, Ed Cohen

Create a new update query. In the QBE window select both tables. Join
them by InvoiceNumber. Drag LineItems.TransactionDate to the grid
below. Set its value to ArchInvoices.TransactionDate

Indeed, that's a terrible database design.

-Tom.