Append function 1

[samotek]

I want to insert from 2 identical tables only that row that has the Yes/No field Amenity set to Yes.More specificaly, i want to copy that field from table
NewParks into the table Parks.Usually there is only one row that has to be copied.
The original table Parks is set to have the field Amenity set to false.So i want to copy fields with Yes of Amenity into the the table Parks.
How can i do that ?

The function below is not right,it has errors

Dim MyEstate As String
strMyEstate = "INSERT INTO Parks " & _
"SELECT * " & _
" FROM NewParks As o1 WHERE NOT EXISTS (SELECT * FROM NewParks WHERE " & _
"ParkID=o1.ParkID And Amenity = True))"
CurrentDb.Execute MyEstate
End Function

 

[Golom]

I think that you want

Dim MyEstate As String
strMyEstate = "INSERT INTO Parks " & _
              "SELECT * " & _
" FROM NewParks As o1 WHERE NOT EXISTS (SELECT * FROM [COLOR=blue]Parks[/color] WHERE " & _
"ParkID=o1.ParkID And Amenity = True))"
CurrentDb.Execute MyEstate

You are excluding based on the source table rather than the destination table. Presumably the record always exists in the source table.

 

[lancombee]

Thank you very much.It works indeed. Can you give me a further advice or shall i open another thread? Thw pooint is that i need to append not all the rows where Amenity = true, but only the last one, i.e. the last row where Amenity = True.How shall i proceed ?

 

[Golom]

Question is What defines "Last"? Relational Databases do not guarantee any particular ordering of records (including input order) so you will need some field or combination of fields that you can test to determine which is the "Last" record.