Designing an update query to detect/fill multiple blank destination fields if new data is present

[CJ_Beer]

Hi everyone, I'm trying to design a database to track shipping containers and their status updates. The info comes over in the form of a daily Excel spreadsheet in an email, which can sometimes have a lot of info to comb through and validate. We also don't want to just go off of the data in the newest report and overwrite our data with the new report; we want to be able to compare what we previously saw with the newest report, in case containers fall of the report or re-appear.



I was thinking I'd create a database to put the original tracking info into, then every day bring the new report into Access and have a query add new shipping containers. Another query would be, when there is a duplicate container ID, it may not have all the relevant info in the previous report and now there's updated info in the new report, such as ETA or seal number. I'm trying to create an update query where, if a field in the master data table is blank and the corresponding field in the update table has data, the data updates into the master data table.

There are a couple dozen fields in these tables, and I'd like the query to check all of them for new data. I know the general structure of a WHERE (Is Null) looks like:

[code]UPDATE TableName SET TableName.FieldName = "YourWord"
WHERE (((TableName.FieldName) Is Null));

But what would that look like for multiple fields? Do you have to "where" each field? Because my field list looks like this:

[code]UPDATE [09580 Master] SET [09580 Master].[AB SAP Code] = [09580 Update].[AB SAP Code], [09580 Master].[AB SAP LINE#] = [09580 Update].[AB SAP LINE#], [09580 Master].[Order 1] = [09580 Update].[Order 1], [09580 Master].[Order 2] = [09580 Update].[Order 2], [09580 Master].[Order 3] = [09580 Update].[Order 3], [09580 Master].[Seal#] = [09580 Update].[Seal#], [09580 Master].Brand = [09580 Update].[Brand], [09580 Master].[CBP Entry#] = [09580 Update].[CBP Entry#], [09580 Master].BOL = [09580 Update].[BOL], [09580 Master].[Ocean Carrier] = [09580 Update].[Ocean Carrier], [09580 Master].Vessel = [09580 Update].[Vessel], [09580 Master].[Port Of Lading] = [09580 Update].[Port Of Lading], [09580 Master].[Port of Unlading] = [09580 Update].[Port of Unlading], [09580 Master].[Port Of Entry] = [09580 Update].[Port Of Entry], [09580 Master].Consignee = [09580 Update].[Consignee], [09580 Master].[Wholesaler ID] = [09580 Update].[Wholesaler ID], [09580 Master].[Consignee REDIRECT] = [09580 Update].[Consignee REDIRECT], [09580 Master].[Wholsaler ID REDIRECT] = [09580 Update].[Wholsaler ID REDIRECT], [09580 Master].[Redirect Date] = [09580 Update].[Redirect Date], [09580 Master].[Final Destination] = [09580 Update].[Final Destination], [09580 Master].[Ocean ETA Date] = [09580 Update].[Ocean ETA Date], [09580 Master].[Docs Recv] = [09580 Update].[Docs Recv], [09580 Master].[Arrival Notice] = [09580 Update].[Arrival Notice], [09580 Master].[Ocean Arrival Date] = [09580 Update].[Ocean Arrival Date], [09580 Master].[Days Prior Notice] = [09580 Update].[Days Prior Notice], [09580 Master].[Inland ETA Date] = [09580 Update].[Inland ETA Date], [09580 Master].[Inland Arrival Date] = [09580 Update].[Inland Arrival Date], [09580 Master].[Customs Clearance] = [09580 Update].[Customs Clearance], [09580 Master].[FDA Clearance] = [09580 Update].[FDA Clearance], [09580 Master].[FINAL Clearance] = [09580 Update].[FINAL Clearance], [09580 Master].[Delivery Order] = [09580 Update].[Delivery Order], [09580 Master].[Last Free Day] = [09580 Update].[Last Free Day], [09580 Master].[Port Apt date] = [09580 Update].[Port Apt date], [09580 Master].Expedite = [09580 Update].[Expedite], [09580 Master].[Carrier Appt Date] = [09580 Update].[Carrier Appt Date], [09580 Master].[Carrier Appt Time] = [09580 Update].[Carrier Appt Time], [09580 Master].[Gate Out Date] = [09580 Update].[Gate Out Date], [09580 Master].[Gate Out Time] = [09580 Update].[Gate Out Time], [09580 Master].[Return Container Date] = [09580 Update].[Return Container Date], [09580 Master].[Delivery Date] = [09580 Update].[Delivery Date], [09580 Master].[Delivery Time] = [09580 Update].[Delivery Time], [09580 Master].POD = [09580 Update].[POD], [09580 Master].[Broker Ref#] = [09580 Update].[Broker Ref#], [09580 Master].[Entry Type] = [09580 Update].[Entry Type], [09580 Master].[ISF Number] = [09580 Update].[ISF Number], [09580 Master].[ISF Date] = [09580 Update].[ISF Date], [09580 Master].[Tax Due Date] = [09580 Update].[Tax Due Date], [09580 Master].[Whse/Yard] = [09580 Update].[Whse/Yard], [09580 Master].[Whse Est Ship Date] = [09580 Update].[Whse Est Ship Date], [09580 Master].[Whse Act Ship Date] = [09580 Update].[Whse Act Ship Date], [09580 Master].[Est Delivery Date] = [09580 Update].[Est Delivery Date], [09580 Master].[Dray/TranLoad] = [09580 Update].[Dray/TranLoad], [09580 Master].[Rail/Truck] = [09580 Update].[Rail/Truck];

[CJ_London]

From the looks of your sql, your data isn't normalised and you haven't followed the recommended naming convention (no space, no non alphanumeric characters, don't start with a number)- which could cause problems. Further your sgl does not have a FROM section which you need. Also your tables require a unique ID which links the two tables together in a one to one relationship. Perhaps containerID?

Your basic insert query template looks like this

UPDATE tblNewStuff
INNER JOIN tblMaster ON  tblNewStuff.PK= tblMaster.PK
SET tblMaster.Field1 = tblNewStuff.Field1
,tblMaster.Field2 = tblNewStuff.Field2
etc

PK=Primary Key - this might be your containerID providing it only appears in one record in each table

Moving on, to address this requirement

I'm trying to create an update query where, if a field in the master data table is blank and the corresponding field in the update table has data, the data updates into the master data table.

modify the set part of the sql to

SET tblMaster.Field1 = nz(tblMaster.Field1,tblNewStuff.Field1)
,tblMaster.Field2 = nz(tblMaster.Field2,tblNewStuff.Field2)
etc

note you use the excel term 'blank'. Blank does not exist in databases, you either have a value (which might be 0) or you don't, in which case it is null. Your text fields should have the 'allow zero length' property set to no, otherwise the field might be populated with a zero length string which a) like null cannot be seen visually and b) is not null - so the nz function won't work as expected. If you are not familiar with the nz function google it.

then every day bring the new report into Access and have a query add new shipping containers. Another query would be, when there is a duplicate container ID

To insert new records and update existing ones with one query, consider using what is frequently called an Upsert query which simply changes the INNER JOIN to a LEFT JOIN and includes adding a PK

UPDATE tblNewStuff
LEFT JOIN tblMaster ON  tblNewStuff.PK= tblMaster.PK
SET tblMaster.Field1 = tblNewStuff.Field1
,tblMaster.Field2 = tblNewStuff.Field2
,tblMaster.PK = tblNewStuff.PK
etc

When you are adding code to your post - highlight the code and click the # button

When replying, copy and paste the bit of the response to which you are replying (not the whole post), highlight and click the <> button