Update Access Table with daily excel spread sheet

[ItsMe]

You can have as many joins as you want. You chose to join all three fields. If you double click one of the lines that is a join, you will see the join type and a brief description. Because you joined all three fields, your query will only retrieve data where all three fields are equal. Kinda defeats the purpose of an Update query. Try Using one JOIN. Choose a field that will cause the query to retrieve the records you want to update, like joining on HB.

[stildawn]

Ah perfect, so the "join" is a check for a match? And since it was checking all three, there was never a match hence no updates. Since I want to update everything for each "HB" the only join should be the HB. I think I have it thanks.

So I also made a Add Query like so:


This seems to work, although I get a few boxes saying things like "access cant append all records" etc but it seems to work.

Using a procedure:

Sub Test1()

CurrentDb.Execute "Add Query"
CurrentDb.Execute "Update Query"




End Sub

It seems to run through both perfectly with no prompts and work...

Thanks heaps and heaps ItsMe, that's the major step solved as I can then work these things into some userforms.

The only thing now is somehow keeping a record of these updates? Is this possible through theses queries or would I need something more complex? Getting something like this in a ChangesTB:

ID	HB	FieldName	OldValue	NewValue	UserName	TimeStamp
1	55	ETA	29/04/14	30/04/14	tbaker	29/04/14 10.35 AM

[ItsMe]

To get a timestamp, you can use a default value at the table level. Add a field of Date/Time type. In the default property type the word Date

Now when your append query runs a timestamp will be added to each new record.

You might want to add a line of code to make sure there is not a conflict with your action queries.

CurrentDb.Execute "Add Query"
DoEvents
CurrentDb.Execute "Update Query"

Play around with it and study the data. Counting records is always a good idea. Maybe keep notepad open to take notes (no pun) as you test your action queries.

[stildawn]

I've added the "DoEvents" and I've played around quite a bit, it all seems to be working perfectly so far.

With the recording changes thing, I think this is a bit complicated and might be a bit of a stretch to be honest.

Basically, I would like to keep an audit trail on every single field for every single record (HB). By using a table like this:


So ideally when I run the Update Query (that we have already worked on), this tbl_Changes table keeps track of all the changes to every field.

So for example if the Main Table has HB 55 with ETA of 29/04/14, and the Import_Temp table has HB 55 with ETA 01/05/14, then when I run the Update Query, it also adds the following recording into tbl_Changes

ID	HB	FieldName	OldValue	NewValue	UserName	TimeStamp
1	55	ETA	29/04/14	01/05/14	tbaker	29/04/14 10.35 AM

Obviously after awhile there will be hundreds of change records on this tbl_Changes table as it records every change in every field. Is this sort of this possible to add into our Update Query we build in the previous posts?

[stildawn]

Sorry to better explain myself...

I have currently set up a Userform (which opens in Datasheet view), this form is what users will be using to work with the database within Access.

Here is the Userform (still in example form):


Now in each of the fields of the userform (in design mode) I have created this VBA code behind the form:

Private Sub ETA_BeforeUpdate(Cancel As Integer)

Call LogChanges(HB, "ETA")


End Sub


Private Sub Status_BeforeUpdate(Cancel As Integer)


Call LogChanges(HB, "Status")


End Sub


Function LogChanges(lngID As Long, Optional strField As String = "")
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim varOld As Variant
    Dim varNew As Variant
    Dim strControlName As String
    
    varOld = Screen.ActiveControl.OldValue
    varNew = Screen.ActiveControl.Value
    strControlName = Screen.ActiveControl.Name
    Set dbs = CurrentDb()
    Set rst = dbs.TableDefs("tbl_Changes").OpenRecordset
    
    With rst
        .AddNew
        If strField = "" Then
            !FieldName = strControlName
        Else
            !FieldName = strField
        End If
        !HB = lngID
        !UserName = Environ("username")
        If Not IsNull(varOld) Then
            !OldValue = CStr(varOld)
        End If
        !NewValue = CStr(varNew)
        .Update
    End With
    'clean up
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
End Function

This code logs all changes to each field by any user into the tbl_Changes table.

Now if in the userform, if I were to on HB 70, tick status and change the ETA, and then on HB 80 untick status and change the ETA. This code automatically records these changes in tbl_Changes, and will look like this:





This is working perfectly at the moment for all changes made via a userform which is something I need. What I dont know how to do is do the same recording of changes but do it when I do my Update Query?

Is this possible?

Thanks

[ItsMe]

You should be able to add the date field to your table for when the UPDATE happens. So, one timestamp/column for when the record gets created/appended and another timestamp/column for when the record is UPDATED.

Add the new date/time field from your table to your UPDATE query and in the "Update To" area type Now.

I also just realized that I told you to type Date in the default area in your table for the other date field. Date provides a date and Now is the function for Date with timestamp. You will want to use the Now function in your table and your query.

[stildawn]

Ah ok I see what your saying. So there would be a timestamp for when records are added/update (via daily update). But the Update Query cant do the full Field by Field changes record that the form can? (Like in the example above)

I think I can live with that.

[ItsMe]

I was thinking you would want two different timestamps, one for when the record was created and one for when the status changed. You can have your query change the status and update the timestamp to reflect the date and time the status changed.

[stildawn]

Hi ItsMe

So I have an issue with the update query, it works fine, however I added in a field in the tbl_Main called "Last Updated TimeStamp" to record when each record recieves an update from the daily import.

Here is a pic of my Update Query now, I have added this "Last Updated TimeStamp" with "NOW()" so that it puts in the current time and date when the query is run:


So it does update any updated information as I expected it would. However it seems to add this "Last Updated TimeStamp" to every single record at once??? I was hoping it would only add it to those that have actually been updated? In the test I did there would have been only 1 updated field in the Main Table, but every record got the "Last Updated" timestamp?

Is there a way to make it only add the timestamp in when the record is actually updated? Perhaps something needs to be put into Criteria?

Thanks

[ItsMe]

I am not at a computer right now. I will look closer for a solution later.

If you look at the grid at the bottom of the window, just below the Update To field, you will see a field where you can add criteria. This is where you can add criteria to retrieve specific records over others.

I am imagining a possible solution where we will employ an additional join and add some criteria such as is null.

I should be able to look at it later tonight.

[stildawn]

Thanks ItsMe, I saw the criteria field but cant think of how to use it. Have plenty of time thanks for all your help.

[ItsMe]

I believe the following will get you the results you need. Create another join. You are going to create a RIGHT JOIN from tblImport to tblMain on HBL. Click on the HBL field in tblImport and drag over to HBL on tblMain. Double click the line to edit the JOIN type. Select the "Include ALL records from tblImport and only those records from tblMain where the joined fields are equal".

Double click MBS on tblMain
Uncheck the box in the grid for the MBS field (do not display option)
In the criteria for tblMain.MBS type, Is Null

That should do it. The original JOIN may need to be adjusted to. If you get an error make the old JOIN the same type as the new one. "Include ALL records from tblImport and only those records from tblMain where the joined fields are equal"

If you still get an error, we can create a subquery and join the results of the first query with the new one.

[stildawn]

Um I think I'm doing it wrong?

HBL in this one isnt as important, Document (the one which is already joined) is the Unique Key on both tables, the HBL could be empty for example and it wont matter, as the users will enter it later.

Also what is MBS? I dont have a MBS that I can see?

Sorry might be reading you wrong, here is what I have done but I dont think I'm getting it right?

[stildawn]

From looking at those options in the join box? Dont I need something like "NOT EQUAL" so it returns all the records that are going to be updated cause the fields are not equal or something? I'm probably getting this all wrong?

[ItsMe]

From looking at those options in the join box? Dont I need something like "NOT EQUAL" so it returns all the records that are going to be updated cause the fields are not equal or something? I'm probably getting this all wrong?

That is correct. You need something like NOT EQUAL. The combination of the join and its type, along with Is Null, will create the unmatched query. It will be a combination of things. However, I quoted the wrong field name. You should be creating the second join on [Manifest Status], I believe. This is the field you want to find unmatched records for after you create the first join on Document.