Fill in missing field in record 1

[surfbum3000]

The database includes Agency name, Address, City, State, and Zip Code. Due to expert outsourced data entry, some zip codes are blank. I would like to complete the zip code for all records when the Agency name, Address, City, State are equal.

So, if record 1 has Agency name, Address, City, State, and Zip Code and record 2 has the same Agency name, Address, City, State, and Zip Code (but the Zip Code is blank), fill in the Zip Code from the previous entry.

 

[PHV]

You may try something like this:
UPDATE yourTable A INNER JOIN yourTable B
ON A.[Agency name]=B.[Agency name] AND A.Address=B.Address AND A.City=B.City AND A.State=B.State
SET A.[Zip Code]=B.[Zip Code]
WHERE Trim(A.[Zip Code] & "") = "" AND Trim(B.[Zip Code] & "") <> "";

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[surfbum3000]

I'm confused. I only have one table named tblResourceDir.

 

[PHV]

I only have one table named tblResourceDir
Where are the correct Zip codes ?
Have you tried something like this ?
UPDATE tblResourceDir A INNER JOIN tblResourceDir B
ON A.[Agency name]=B.[Agency name] AND A.Address=B.Address AND A.City=B.City AND A.State=B.State
SET A.[Zip Code]=B.[Zip Code]
WHERE Trim(A.[Zip Code] & "") = "" AND Trim(B.[Zip Code] & "") <> "";

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[surfbum3000]

The correct zip codes are in the table tblResourceDir. An record would look like this:

Record 1: AgencyName, Address, City, State, ZipCode
Record 2: AgencyName, Address, City, State, ZipCode (not entered, blank)

I copied the table tblResource twice to attempt the solution. I now have tblResourceA and tblResourceB. I created a query, went to SQL View section, and pasted the code:

UPDATE tblResourceDirA INNER JOIN tblResourceDirB
ON A.[AgencyName]=B.[AgencyName] AND A.Address=B.Address AND A.City=B.City AND A.State=B.State
SET A.[ZipCode]=B.[ZipCode]
WHERE Trim(A.[ZipCode] & "") = "" AND Trim(B.[Zip Code] & "") <> "";

Error: Syntax error in Join operation.

 

[PHV]

You modified the suggested code:
UPDATE tblResourceDir[highlight] [/highlight]A INNER JOIN tblResourceDir[highlight] [/highlight]B

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[surfbum3000]

Looks good. Thank you very much. Here is the code:

UPDATE tblResourceDir AS A INNER JOIN tblResourceDir AS B ON (A.State=B.State) AND (A.City=B.City) AND (A.Address=B.Address) AND (A.[AgencyName]=B.[AgencyName]) SET A.ZipCode = B.[ZipCode]
WHERE Trim(A.[ZipCode] & "") = "" AND Trim(B.[ZipCode] & "") <> "";