Update or Append Query in Access 2003

  • Thread starter Thread starter Shae.Simpson
  • Start date Start date
S

Shae.Simpson

I have a table that needs to be updated via query. Easy enough, use an
update query. However, sometimes, the record is not there to be updated - it
needs to be appended. How do I tell the query:

if (([table1].field1=[table2].field1) and ([table1.field2=[table2].field2)
and ... ([table1].fieldx=[table2].fieldx))
then update ([table1].fieldy) with ([table2].fieldy)
else append ([table1].field1) with ([table2].field1) and ([table1.field2)
with([table2].field2) and ... ([table1].fieldx) with ([table2].fieldx) and
([table1].fieldy) with ([table2].fieldy)

Thanks in advance for any input.

Shae
 
Hi Shae.Simpson,
you need 2 separate queries.

Dim strSQL as String

if (([table1].field1=[table2].field1) and ([table1.field2=[table2].field2)
and ... ([table1].fieldx=[table2].fieldx)) then
strSQL = "qryA"
Else
strSQL = "qryB"
End If

Current.Db.Execute strSQL, DbFailOnError


Note: qryA and qryB can be saved queries or they can be query strings in
VBA.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
Thank you very much. This is very helpful.

Jeanette Cunningham said:
Hi Shae.Simpson,
you need 2 separate queries.

Dim strSQL as String

if (([table1].field1=[table2].field1) and ([table1.field2=[table2].field2)
and ... ([table1].fieldx=[table2].fieldx)) then
strSQL = "qryA"
Else
strSQL = "qryB"
End If

Current.Db.Execute strSQL, DbFailOnError


Note: qryA and qryB can be saved queries or they can be query strings in
VBA.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Shae.Simpson said:
I have a table that needs to be updated via query. Easy enough, use an
update query. However, sometimes, the record is not there to be updated -
it
needs to be appended. How do I tell the query:

if (([table1].field1=[table2].field1) and ([table1.field2=[table2].field2)
and ... ([table1].fieldx=[table2].fieldx))
then update ([table1].fieldy) with ([table2].fieldy)
else append ([table1].field1) with ([table2].field1) and ([table1.field2)
with([table2].field2) and ... ([table1].fieldx) with ([table2].fieldx) and
([table1].fieldy) with ([table2].fieldy)

Thanks in advance for any input.

Shae
Click to expand...
Click to expand...
 
Back
Top