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];