Nested select/update

[Stu]

Hi

I have two tables, ProjectLog and OrderHeader. There are address
fields in both and I need to update the address fields in ProjectLog
where they are blank with the data held in OrderHeader. There is one
record per Project in ProjectLog and potentially multiple OrderHeader
records referring to each project. I just want to take the first
entry from OrderHeader for a Project and update the address
information in ProjectLog if it is blank. I know I need to do some
form of nested select/update statement but my efforts so far have been
pretty lame and so I am hoping someone here can help me.

Table Structures

OrderHeader ProjectLog

WorksOrderNumber ProjectNumber
ProjectNo Customer
Customer Address1
DelAdd1 Address2
DelAdd2 Address3
DelAdd3 Postcode
DelPostCode

So I need to update ProjectLog.Address1 with OrderHeader.DelAdd1 where
Address1 (and 2 and 3) are blank and the link between the tables is
the ProjectNumber / ProjectNo.

TIA

 

[Lynn Trapp]

Why are you trying to store the data redundantly?

 

[Stu]

Why are you trying to store the data redundantly?

--

Lynn Trapp
Microsoft MVP (Access)www.ltcomputerdesigns.com












- Show quoted text -

Because the ProjectLog table is used as a reference table and needs
the delivery address to be stored in it for other applications The
order header is volatile data and is periodically archived and also
the addresses can change over time but the ProjectLog will be used as
the lookup for all project information. It's not my design but I
can't think of another simple way to do this.