Update Access Table with daily excel spread sheet

[stildawn]

Can you run me through the reasoning behind choosing Manifest Status as the next join? Manifest Status is a "Yes/No" field (represented in the import as "-1/0", it represents if the job is still local or at our overseas data entry department basically?


I have tired to run through your instructions:
1. Dragged from Import to Main the Manifest Status, creating join.
2. Double clicked on the join and changed to "Include ALL records from tblImport and only those records from tblMain where the joined fields are equal"
3. In the grid under Manifest Status, in criteria I put "IsNull"
4. There is no uncheck box

Tried to run the above and got this error:


So then I changed the original join for "Document" to the same "Include ALL records from tblImport and only those records from tblMain where the joined fields are equal"

So it looks like this:




But when I ran it I got this error:


Any ideas?

[ItsMe]

Yes, data types must match if you are going to create a JOIN. If [Manifest Status] is a text type on tblImport then it must also be of text type on tblMain in order to create a JOIN between the two.

The reason I chose [Manifest Status] as a join is because I believe this is the field you want to compare between the two tables and locate unmatched records within tblMain.

I could probably mock up a sample DB here on my computer and offer a tested solution but, I have chosen to drag you through the process and have you do the leg work. Get the data types corrected and I believe you will be in a good position. Afterwards, I believe you may have an idea how this works and there is a tool you can use to help you recreate this type of Unmatched Query.

[stildawn]

Manifest Status data types match on both tbl_Main & tbl_Import. They are both "yes/no" fields.

Manifest Status is not the only one that needs to be updated, all the fields on tbl_Import need to be updated on tbl_Main, the only one not updated would be Document as this is a unique unchanging number (hence why it was used as the key on both tables).

So the full list of fields that need to be updated is:
Port of Loading
Port of Discharge
Voyage
Vessel
Expected Arrival Date
Master Bill of Lading
HBL
Manifest Status

So maybe the join needs to be on all the above fields? And return only the Documents (records) that don't match and hence need to be updated? Then it can update them, and add the "Last Updated TimeStamp" to only those that were updated?

Don't know if I'm explaining it properly, in mind Access needs to follow a sequence of events like this:

1. Locate all Documents(records) on tbl_Import that have different values in any fields(listed above) on tbl_Main
2. This list of records returned from step 1, would have a "=NOW()" put into the "Last Updated TimeStamp" field on tbl_Main
3. All fields on tbl_Main that are different to their corresponding fields on tbl_Import would need to be updated with the data from tbl_Import

This would be perfect, it would update all the fields on tbl_Main, and ONLY put the "Last Updated TimeStamp" on records that were actually updated in this run.

Currently before, it would do the updating perfectly, but it would put the "=NOW()" into all tbl_Main records, regardless of if they were updated or not.

And yeah I prefer to do the leg work haha, that way I learn and its interesting anyway thanks again.

[ItsMe]

OK I will have to test a couple of things then

If I do not have a chance tonight I will tomorrow. I will look at how you could possibly be getting a miss match.

You can check out the query Wizard. When you create a new query object, you can use the wizard and choose the Unmatched Query option.

This is the method I am trying to employ here. The problem I see now is that you have many fields to update and comparing fields via SQL may, in the end, not be feasible.

[stildawn]

Thanks heaps ItsMe, really you are awesome, I'll try play around a bit myself and see if I can figure it out in the mean time.

[ItsMe]

Well I discovered a couple of things.

One, Access 2010 does not like to find Unmatched with the YesNo field. Not a problem in Access 2003 but in newer versions there is not a triple state for Yes No fields.

Two, cannot run the update query at the same time as pulling the Where clause with the Left Joins (Unmatched Query). Must run the Where clause first, in a separate query.

Did not discover where the mismatch is because I could not duplicate your error but I will guess it has to do with the Is Null. However, I tried different data types that do not like Nulls and did not get an error. Might have to do with your SQL. I noticed in one of your screen shots that you have a left join and then a right join, both LEFT and RIGHT naming the same table. Moot point right now and I am tired.

The important part is you want to compare multiple fields in a single recordset, updating the different ones. I do this with variables and VBA. I incorporate query objects and or SQL often but, data verification is data verification is.... Retrieve it, place it in a variable, and compare it to another variable. If it does not match, call code to update. I prefer DAO and a loop or While/Wend.

So, I will ask where this data is coming from??? Typically, steamship data is retrieved via EDI and is issued by the Steamship lines in an XML format/schema using a somewhat international standard. Maybe you do not want to divulge too much info as to where and how you get your data. What I am interested in is the most raw format this stuff can come in. What you are trying to do is not simple and rather than trying this and that, it will be prudent if Best Practices are considered first. If your data is formatted in Excel XLS, that is fine. You will just need to import into a table and look at it row by row, field by field. I am not trying to change the structure of your tables etc. Just trying to consider the best approach. Right now, DAO seems to be best.

[stildawn]

Right so we have a new system here, its a SAP based system but is it terrible (but I dont make these decisions unfortunately). Basically at the moment it doesnt have all the "Steps" that need to be performed in each jobs, hence this Access database which is going to keep track of these "steps" so users can know what point jobs are at etc and update them in SAP.

The "steps" I'm talking about, are all the extra fields that is in tbl_Main that are not in tbl_Import in my screenshots.

So basically each morning the department head will get a xls pull of data from SAP into a xls file. This xls file is then imported (I have covered this in other code in other threads you might have seen), creating the tbl_Import table each morning. The xls data import is the raw data, the only formatting I do to it (as seen in code in my other threads here) is delete some unneeded columns, change the dates into dates that Excel and Access can read (as dates) and change a "X" to a "-1" (for the Yes/No Manifest Status field). Other than that the tbl_Import table is a straight import from the xls file.

Does that answer your question? Actually here is a demo xls: Demo Data.zip

Basically I have a file like this (but much much bigger, around 2k lines), and run this code on it:

Sub FormattExcel()

SelectFile:
Set f = Application.FileDialog(1)
With f
    .AllowMultiSelect = False
    .Title = "Please select daily upload from NFE"
    .Show
    For i = 1 To .SelectedItems.Count
        filepath = .SelectedItems(i)
    Next i
End With


If filepath = vbNullString Then
    Answer = MsgBox("Do you want to select daily upload file again?", vbYesNo, "No Upload File Selected!")
    If Answer = vbYes Then
        GoTo SelectFile
    Else
        Exit Sub
    End If
Else
End If


Dim objExcel As Excel.Application
Set objExcel = New Excel.Application


With objExcel
    .Workbooks.Open filepath
    file = Right(filepath, Len(filepath) - InStrRev(filepath, "\"))
    .Workbooks(file).Sheets(1).Range("G:H,K:K").Delete
    Lastrow = .Workbooks(file).Sheets(1).Range("A" & .Rows.Count).End(xlUp).Row
    .Workbooks(file).Sheets(1).Range("F2:F" & Lastrow).NumberFormat = "dd/mm/yyyy"
    For Each rgCell In .Workbooks(file).Sheets(1).Range("F2:F" & Lastrow).Cells
        DateSplit = Split(Left(rgCell, 10), ".", 3)
        rgCell.Value = DateSerial(DateSplit(2), DateSplit(1), DateSplit(0))
    Next rgCell
    
    For Each rgCell In .Workbooks(file).Sheets(1).Range("I2:I" & Lastrow).Cells
        If rgCell.Value = "X" Then
            rgCell.Value = -1
        Else
            rgCell.Value = 0
        End If
    Next rgCell
    
    .Application.DisplayAlerts = False
    .Workbooks(file).SaveAs "C:\Users\tbaker\Desktop\Merge\FB DB Saves\ImportTemp.xls", 56
    .Workbooks("ImportTemp.xls").Close False
    .Quit
End With


Set objExcel = Nothing


End Sub

So yeah, delete a few columns, change the dates and change the X's.

You mention comparing variables with VBA using loops, I can do that easily in Excel (Access VBA I'm still new with). But there are thousands of records involved, that loop would take sometime I believe, or at least it would in Excel.

[ItsMe]

I see two options that start with the raw data. Import the data directly onto a temp table without any edits or have Access open and look inside the Excel file and parse data before making edits in tblMain.

Most of my examples related to transportation connect to the data remotely. My code will then look at the data and validate before causing updates or appends to a permanent table. My general rule for text and Excel files is to import the entire raw file into a temp table and analyze/parse/validate the data after it is inside my DB.

You could create and save an import process within Access for your XLS file. With that, you could call the named import process via VBA. I use a yes no field in my temp table that defaults to No to indicate whether or not a specific record has been analyzed and used. After the record has been parsed and the data from said record has been used to update the permanent table, I change the status of the Yes/No field to Yes or -1. The parsing procedure does not look at -1 values for that Boolean field.

If you write your VBA well, the entire process should not take very long. Another key is to use a machine/PC that does not have 100 apps installed on it and goes on the internet to download freeware, internet browsing tools, virus scanners, etc. Use a machine that performs well and has a decent amount of RAM. The processing power does not need to be huge if there are only a few apps running at a given time.

One way I ensure my VBA works well is to make declarations to reserve the machines memory as soon as the form opens or there is intention to run the process.
Here is a small example from a transportation app of my mine

Dim strCntrNo As String
Dim strCntrType As String
Dim strCntrLgth As String
Dim strCntrHt As String
Dim strContainerComments As String
Dim strSteamshipLine As String
Dim strChassisNo As String
Dim strChasLgth As String
Dim strTruckingCompany As String
Dim strBOLBookingReleaseNo As String
Dim strYard As String
Dim strGatePassNo As String
Dim strTroubleStart As String
Dim strTroubleEnd As String

Notice how I treat everything as text. Also, I am not using Variant type which is twice the size of String. When Access uses DAO to look at your temp table, you want to take each field and assign it to its respective variable.

Before I use these variables to update the permanent table, I use functions like instr() to analyze and validate the data. After that, I use the .FindFirst (looking inside the permanent table) method to match the indexed Key values in the two tables. When I find a match in the permanent table, I write the data to the table using the string Variable. I do not bother converting data type from string to match the perm table's field using VBA. My DateField in my permanent table will accept a String Variable as long as I correctly validate it prior.

Notice another thing that is happening, I am not comparing the two fields for a difference. In other words, after I find a match on the Key values, I write the data to the permanent table. I do not check to see if the value in the perm table is different than the value in the temp table. With data, there is only one authoritative source. In this case, the temp table is the authoritative source. If the temp table says strSteamshipLine is literal text "Peanut Butter", so be it.

Use two loops. One loop looks at the raw data and stores each field in a variable. The second loop uses the .FindFirst method to locate the record in the perm table and then .Edit to edit and then .Update to save. MoveNext, rinse and repeat.

[stildawn]

Ok I'll start working on that.

I know how to do loops etc, what syntax do you use to reference records and fields in Access VBA?

[ItsMe]

I quickly typed up the following and should be a good example how to get records from a temp table and write them to a perm table. This is just DAO from one table to another and does not offer data verification.

Dim strCntrNo As String
Dim strCntrType As String
Dim strCntrLgth As String
Dim db As DAO.Database
Dim rsTemp As DAO.Recordset
Dim rsSave As DAO.Recordset

Set db = CurrentDb
Set rsTemp = db.OpenRecordset("SELECT * tbl_Import WHERE [Updated] = 0", dbOpenSnapshot)
    If rsTemp.EOF = True Then   'No records found must exit
    
        'cannot rsTemp.Close because there are no records
        Set rsTemp = Nothing
        Set db = Nothing
        Exit Sub
    
    End If

Set rsSave = db.OpenRecordset("tbl_Main", dbOpenDynaset)

With rsTemp
.MoveFirst
    While rsTemp.EOF = False
    
        If Not IsNull(![CntrNo]) Then
            strCntrNo = ![CntrNo]
        End If
        
            If Not IsNull(![CntrType]) Then
                strCntrType = ![CntrType]
            End If
            
                If Not IsNull(![CntrLgth]) Then
                    strCntrLgth = ![CntrLgth]
                End If
    
    
'This is where I would do data verification of
'the string variables before AddNew
'Additional code needed
'Here we will add a new record to tblMain
rsSave.AddNew
'No need for .Edit when using AddNew
rsSave![CntrNo] = strCntrNo
rsSave![CntrType] = strCntrType
rsSave![CntrLgth] = strCntrLgth
rsSave.Update
    
    .MoveNext 'Move to the next record within rsTemp
    
    
    Wend 'rsTemp.EOF
End With   'rsTemp

'Tidy Up
rsSave.Close
Set rsSave = Nothing
rsTemp.Close
Set rsTemp = Nothing


'Let the temp table know it has been updated.
Dim strUpdate As String
strUpdate = "UPDATE tbl_Import " & _
            "SET tbl_Import.[Updated] = -1 " & _
            "WHERE [Updated] = 0"

db.Execute strUpdate

Set db = Nothing

[stildawn]

Ok, I'll see if I can make sense of that.

Thanks ItsMe, you've been totally awesome. Cheers again.

[ItsMe]

This example is more geared towards appending records to tblMain. You need a nested loop. This example uses the nested loop to append records. Later on, you can adjust the nested loop to edit or update existing records in tblMain.