Update Table base on Another Table

Question

This involves two tables in our Access 2007.

A "Station Master" table consist of records of workstations

A "Move-Add-Change" table consist of records for move, add, change steps for specifics Project Id.

I created a query to pull all records from "Move-Add-Change" that match the a Project Id. Then, I will need to process as follows:

Go thru each row in "Pull MAC Records by Project # Qry" query (there's a Sequence field numbering from 1 counting up in there too)

If the Action field in the row is Move:

If the "To Loc" of the same row is not beginning with TSS (used for Inventory Loc), update the records in "Station Master" with the Loc Name equal to the "From Loc" field of the same row in "Pull MAC Records by Project # Qry" query, change its value to the To Loc field of the same row in "Pull MAC Records by Project # Qry" query
If the "To Loc" of the same row is beginning with TSS, delete the records in "Station Master" that has Loc Name match the "From Loc" of the same row in "Pull MAC Records by Project # Qry" query.

If the Action field in the row is Add:

Create a new record in "Station Master" with Loc Name is the "To Loc" field of the row

If the Action field in the row is Change:

Do the same as Move plus udpate the Description of "Station Master" using the info from the Notes field of the same row in "Pull MAC Records by Project # Qry" query

The code has to go one by one row in "Pull MAC Records by Project # Qry" because we might move another station into a location that just moved away.

Example partial results from the query:

Project Id   Seq   Action   From Loc   To Loc       Notes 
A123456      1     Move     WFC1234    TSSRepair    For OEM service
A123456      2     Move     WFC9999    WFC1234      Test station
...

If I call a query to do the update, it seems like it doesn't goes thru row by row and would prompt for violation (I think it tried to change WFC9999 to WFC1234 when the original WFC1234 record still there, cause duplicate).

Here is the SQL of my query:

UPDATE [Station Master], [Pull MAC Records by Project # Qry] 
SET [Station Master].[Loc Name] = [Pull MAC Records by Project # Qry]![To Loc], 
    [Station Master].[Loc Function] = Left([Pull MAC Records by Project # Qry]![To Loc],3), [Station Master].[Loc #] = Right([Pull MAC Records by Project # Qry]![To Loc],Len([Pull MAC Records by Project # Qry]![To Loc])-3)
WHERE 
    ((([Station Master].[Loc Name]) = [Pull MAC Records by Project # Qry]![From Loc]));

What did I do wrong? Can I have all those processes purely in VBA code (which I am not good about)?

score 0 · Answer 1 · edited May 23 '17 at 10:30

Depending on if you want an actual loop, you could use VBA to do this:

'Set your variables

Dim myR as Recordset
Dim myR2 as Recordset

'Then set your recordsets to the two tables you want to work with

Set myR = db.OpenRecordset("Station Master", dbOpenDynaset)
Set myR2 = db.OpenRecordset("Move-Add-Change", dbOpenDynaset)

'Now select the table you wish to work with until the end of file(EOF)

while Not myR.EOF
    If myR![Action] = "Move" Then

        'Code goes here if true

    Else

        'Code goes here if false

    End If

    'Move to the next record and loop if not end of file(EOF)

    myR.MoveNext
Loop

'Make sure to set close your recordsets when done

Set myR = Nothing
Set myR2 = Nothing

Also check here and here for more examples on looping.

I'll try that idea. I'll probably will have to code a loop for each action to go row by row. It's important because a station might be moved to another location, then another station will be moved into its former space and take its former Loc Name. As query goes, it was just out of order and complain on duplicated index. Thanks. Will get back for more guideline if I'd get lost. :-) — DPhan, Jun 17 '13 at 00:23

Update Table base on Another Table

1 Answers1