Conditional Update or Append

[esskaykay]

I’m trying to either create a query (or queries) or write some code that either updates or appends data from one table (ACTION) to another table (LOCATION).

If a LABEL field value already exists in the table ACTION, I want to “update” the ADDRESS field in ACTION to the value in the ADDRESS field in the table LOCATION.

If a LABEL field value does not exist in the table ACTION, I want to add the LABEL field and the ADDRESS field in ACTION to the values in the LABEL and ADDRESS fields in the table LOCATION.

In other words if LABEL exists, update other wise append. I don’t want duplicate LABELS.

Any suggestions would be greatly appreciated.

Thanks,
SKK

 

[jal]

I’m trying to either create a query (or queries) or write some code that either updates or appends data from one table (ACTION) to another table (LOCATION).

If a LABEL field value already exists in the table ACTION, I want to “update” the ADDRESS field in ACTION to the value in the ADDRESS field in the table LOCATION.

If a LABEL field value does not exist in the table ACTION, I want to add the LABEL field and the ADDRESS field in ACTION to the values in the LABEL and ADDRESS fields in the table LOCATION.

In other words if LABEL exists, update other wise append. I don’t want duplicate LABELS.

Any suggestions would be greatly appreciated.

Thanks,
SKK

Dim rs as New Adodb.RecordSet
'return 0 records (only need column names)
rs.Open "SELECT * FROM Action WHERE 1= 0", CurrentProject.Connection
Dim fld as Adodb.Field
dim foundLabel as Boolean
For each fld in rs.Fields
if Lcase(fld.Name) = "label" Then foundLabel = True
Next fld
rs.close

If not foundLabel Then
DoCmd.RunSql "ALTER TABLE Action ADD COLUMN Label MEMO"
DoCmd.RunSql "ALTER TABLE Action ADD COLUMN Address MEMO"
End if

Sorry about any syntax errors. Maybe this will get you started.

Also, not sure if "CurrentProject.Connection" will work over a network. YOu may need substitute "Cn" for that were Cn is defined as:

Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
Cn.Provider = "Microsoft.Jet.OLEDB.4.0"
Cn.ConnectionString = "Data Source=C:\MyFolder\Northwind.MDB"

 

[jal]

Ok, maybe I misunderstood. I thought you were saying that you might need to create the column "label" -

Now I'm thinking that the column definitely exists, you just want to avoid duplicates.

is that so?

 

[jal]

Never worked with labels. How is the logic supposed to determine whether a label of tblLocation is already present in tblAction?

Is there some kind of label-ID?

 

[esskaykay]

LABLE is the field in both tables noting a unique manhole label -- we do not have to create the field. If a LABLE value exists in tblLOCATION, then just update the ADDRESS field in tblLOCATION to the same as the ADDRESS field in tblACTION. If a LABEL value does not exist in tblLOCATION, then update the LABEL field in tblLOCATION and the ADDRESS field in tblLOCATION.

In other words.
Assume the MH Lable is R17-4 and the Address 123 Maine Street. In the tblACTION the LABEL field will have an entry "R17-4" and a corresponding ADDRESS field entry "123 Main Street".

In tblLOCATION, if there is a LABEL entry "R17-4", then simply update the corresponding ADDRESS field to "123 Main Street". If however, there is no LABEL entry "R17-4" in tblLOCATION, then add a record with LABEL="R17-4" and ADDRESS="123 Main Street".


Does this make more sense?
Thanks again,
SKK

 

[jal]

As for this part

If a LABLE value exists in tblLOCATION, then just update the ADDRESS field in tblLOCATION to the same as the ADDRESS field in tblACTION.

Maybe try this:

UPDATE Location as L
INNER JOIN Action as A
On A.Lable = L.Lable
SET L.Address = A.Address

 

[esskaykay]

I'm not exactly sure what you mean by this (I'm not programmer). I may have to dismiss this and hopefully find some other resolution.

Thanks for your time,
SKK

 

[jal]

Sorry, I think I got it backwards, I guess you wanted

UPDATE Action as A
INNER JOIN Location as L
On A.Lable = L.Lable
SET A.Address = L.Address

 

[jal]

Maybe I didn't get it backwards - I think you stated it both ways, as though you haven't made up your mind.
First you say to update Action:

If a LABEL field value already exists in the table ACTION, I want to "update" the ADDRESS field in ACTION to the value in the ADDRESS field in the table LOCATION.

then you say to update Location:

If a LABLE value exists in tblLOCATION, then just update the ADDRESS field in tblLOCATION to the same as the ADDRESS field in tblACTION.

 

[esskaykay]

To be honest JAL, I'm so confused myself, let's just call it a day for now. I may return to this in a bit for now...

Thank you very much for your time and consideration. It truly is appreciated.
SKK

 

[jal]

I'm not exactly sure what you mean by this (I'm not programmer). I may have to dismiss this and hopefully find some other resolution.

Thanks for your time,
SKK

The idea is to paste the query into Sql View. When you do a table operation using Access wizards, Access generates the SQL code as though you had written the query manually in sql view. This code is visible in SQL view (to see it, start a new query object from the object pane in Design View and the look for SQL view on the menus - paste in code and then save it under any desired name for the query).

Those of us who know SQL often prefer to write it out ourself. This can be more flexible than using Access wizards.

 

[jal]

To be honest JAL, I'm so confused myself, let's just call it a day for now. I may return to this in a bit for now...

Thank you very much for your time and consideration. It truly is appreciated.
SKK

Confused? Join the club. Welcome to programming. I'm always confused...