Add Flag to Append Query

[Rogueci5]

Hi Guys still on my course

I have an Append Query to update an "Orders" table from a "New Orders" table.

fields are:
tblNewOrders
ItemID
SupplierID
DateOrdered
NoOrdered
NoReceived

All of the above are used to add the New Orders to the Orders Table.

Then I am told that:

There is a major problem with this Append query. If you run it a second time it will append the records a second time. Can you suggest a way of using a flag to do this?

I have tried several ways, but I am not really sure what to do, as the ways I have tried have failed :banghead:

Any help or guidance to a thread about setting flags would be great please.

Cheers

 

[Ranman256]

Not a flag,but you'd have a query to test if the data you are going to add already exists in the target table.
Use that query,and make an outer join to allow only add those that don't exist.

 

[sneuberg]

Adding to Ranman256's post the typical way this is done is to make your append query an unmatched query, i.e., it would only append records to the table that did not already exist in the target table. For that to work you need field or a combination of fields that are both in the target table and the input table and those fields have to have data from which you can make a determination of whether a record has already been added.

 

[Rogueci5]

Thanks guys !! Off to look at the unmatched query cheers!

 

[Les Isaacs]

Sounds like you need a field in the "New Orders" table to record whether the record has been added to the "Orders" table: either a boolean field (say, blAdded), or perhaps a date/time field (say, dtAdded) if it would be useful to know when the record was added. Use an update query to populate the new field each time you run your append query. Then in your append query just add a criteria to the new field that excludes the added records (so blAdded = False, or Len(dtAdded) = 0).
Hope that helps!