append query... translation required...

[Chrisopia]

I am having trouble with an append query and I have a theory which needs to be translated into access lingo...

basically to only add things to the list if the ID isnt already there to avoid duplicates?

Something like:

 Append: OrderID, OrderType, DescriptionID, to: tblInvoice, Where: OrderType=3 AND DescriptiionID IS Not already on the list

To some that may sound silly, I make append queries in the query window, not SQL, but either way I cant think of an access translation for this?? :S

 

[WIS]

I am having trouble with an append query and I have a theory which needs to be translated into access lingo...

basically to only add things to the list if the ID isnt already there to avoid duplicates?

Something like:

 Append: OrderID, OrderType, DescriptionID, to: tblInvoice, Where: OrderType=3 AND DescriptiionID IS Not already on the list

To some that may sound silly, I make append queries in the query window, not SQL, but either way I cant think of an access translation for this?? :S

I never use SQL except for UNION qrys - I always use the QBE Access window. I have qrys which are cascaded upto 8 deep - imagine trying to write SQL on one of those.

Have you tried using the QBE.

 

[neileg]

If the ID you refer to is the primary key then Access will prevent this anyway. If not, please explain more fully.

 

[Chrisopia]

The OrderID is not allowed to be repeated, as the orderID needs to be unique during each append.

How do I try and get it to identify new order IDs? Its a primary key in the table it came from, but not the one it's heading to.

 

[gemma-the-husky]

access cant handle your business rules for you all the while

if you have an order number in one table, why do you need to add the same order to another table?

if you have a rule that says you want to add those items that arent already there then

a) do an unmatched query to find the new items AND THEN
b) do an append query to insert these new items