validation for append query help?

[Cam]

Hi,

I am creating an append query so I can track all historical data because
once the orders in WIP (orderWIP table) are completed, they will be removed
from the table. My question is what type of validation/ criteria can I put in
the append query so it is not appending multiple order# in the appended table?

The table has multiple records for each order# because of different
operations within the order#. New order# will be added to the orderWIP table.
Thanks

 

[KARL DEWEY]

Use a multi-field index set to unique.

 

[Cam]

Karl,

Thanks for the sugguestion, but I am not sure what do you mean by
multi-field index? where do I set this option or how do I acheive this?

 

[KARL DEWEY]

Open your table in design view, click on menu VIEW - Indexes.
Select the fields that in combination would be an unique record, enter a
name for the index, select unique and chose to ignore nulls or not.

 

[Cam]

Hi Karl,

I just found out the indexes option does not work because I might have
multiple records for a order#. Is there a way to put in the append query
criteria to say that if the data to be appended with order# already in the
appended table, then do not append them, ONLY append order# that are not in
the appended table.

 

[KARL DEWEY]

Try this in your criteria --
<>DLookup("[Order#]", "orderWIP" )
The above is based on the assumption your table is 'orderWIP' and the order
number field is 'Order#'.

 

[Cam]

Karl,

I tried your criteria in the append query, but it is appending every records
in my orderWIP table; therefore, I am getting double records. Not sure what
is wrong.

Try this in your criteria --
<>DLookup("[Order#]", "orderWIP" )
The above is based on the assumption your table is 'orderWIP' and the order
number field is 'Order#'.
--
KARL DEWEY
Build a little - Test a little

Hi Karl,

I just found out the indexes option does not work because I might have
multiple records for a order#. Is there a way to put in the append query
criteria to say that if the data to be appended with order# already in the
appended table, then do not append them, ONLY append order# that are not in
the appended table.

 

[Cam]

I forgot to mention the Order# is a text field like 000123456. Not sure if it
make any different if it is a number field.
Current situation, I currently have 899 record in the orderWIP table, when I
append the query, it is appending 903 records with 899 duplicate. My target
is to ignor the 899 duplicate record and only append 4 new records.

Karl,

I tried your criteria in the append query, but it is appending every records
in my orderWIP table; therefore, I am getting double records. Not sure what
is wrong.

Try this in your criteria --
<>DLookup("[Order#]", "orderWIP" )
The above is based on the assumption your table is 'orderWIP' and the order
number field is 'Order#'.
--
KARL DEWEY
Build a little - Test a little

Hi Karl,

I just found out the indexes option does not work because I might have
multiple records for a order#. Is there a way to put in the append query
criteria to say that if the data to be appended with order# already in the
appended table, then do not append them, ONLY append order# that are not in
the appended table.

:

Open your table in design view, click on menu VIEW - Indexes.
Select the fields that in combination would be an unique record, enter a
name for the index, select unique and chose to ignore nulls or not.
--
KARL DEWEY
Build a little - Test a little


:

Karl,

Thanks for the sugguestion, but I am not sure what do you mean by
multi-field index? where do I set this option or how do I acheive this?

:

Use a multi-field index set to unique.
--
KARL DEWEY
Build a little - Test a little


:

Hi,

I am creating an append query so I can track all historical data because
once the orders in WIP (orderWIP table) are completed, they will be removed
from the table. My question is what type of validation/ criteria can I put in
the append query so it is not appending multiple order# in the appended table?

The table has multiple records for each order# because of different
operations within the order#. New order# will be added to the orderWIP table.
Thanks