Combine two fields in a query to get a unique third one.

[Frank Martin]

I have a Append query "QrySuppTxns" and some of its fields
are constantly appended to a table "tblLedgerTxns".

This works OK but I also need a unique field from the
"QrySuppTxns" to avoid any possible duplication in the
append step.

Can I combine two fields in the "QrySuppTxns" to create a
unique third even if the fields have different data types?
That is, can a text value such as "As34m" be combined with
an autonumber field such as "457" to give "457As34m" in a
new text field?

Please Help.

 

[RonaldoOneNil]

Yes,

NewFieldName: [Field1] & [Field2]

 

[John W. Vinson]

This works OK but I also need a unique field from the
"QrySuppTxns" to avoid any possible duplication in the
append step.

No, as a matter of fact, you *don't* need a redundant composite field.

Just open the target table and create a unique Index on the pair of fields.
Use the Index tool to create an index (it looks like lightning hitting a
datasheet); put some distinctive index name in the left column of the little
grid; put the first fieldname next to it in the right column, and the second
fieldname below it (leave the left column blank in that row).

Check the Unique Index checkbox, and now you will not be allowed to create a
record which would duplicate the combination of fields (either field alone can
have duplicates).

 

[Frank Martin]

in message

No, as a matter of fact, you *don't* need a redundant
composite field.

Just open the target table and create a unique Index on
the pair of fields.
Use the Index tool to create an index (it looks like
lightning hitting a
datasheet); put some distinctive index name in the left
column of the little
grid; put the first fieldname next to it in the right
column, and the second
fieldname below it (leave the left column blank in that
row).

Check the Unique Index checkbox, and now you will not be
allowed to create a
record which would duplicate the combination of fields
(either field alone can
have duplicates).

Thank you for this, it has helped a lot.

A problem has now occurred that is very mysterious.

The same append query "QrySuppTxns" is now throwing up a
single "key violation" error for just one transaction in
"QrySuppTxns".

I have gone back to this append query and deleted, in the
grid, every field in turn to see which is giving the problem
and it turned out to be "TxnDate", which should not happen
because there's no restriction in the destination table for
this. i.e.. duplicates are allowed for this field and it is
not a primary key.

I will keep checking though.

Regards, Frank

 

[John W. Vinson]

A problem has now occurred that is very mysterious.

The same append query "QrySuppTxns" is now throwing up a
single "key violation" error for just one transaction in
"QrySuppTxns".

I have gone back to this append query and deleted, in the
grid, every field in turn to see which is giving the problem
and it turned out to be "TxnDate", which should not happen
because there's no restriction in the destination table for
this. i.e.. duplicates are allowed for this field and it is
not a primary key.

Is it one of the fields in your unique index?

And are you sure that you're not appending a record which would cause a
duplicate on that index? The TxnDate might be a false clue.