Transfering AutoNumbers from one table to another table

[Guest]

I have two tables with the AutoNumber as the primary key. The AutoNumbers
are each formatted to identify the person inputing the data. Each has a
letter followed by four digits. For example: K0025 and L0019. I have
separate append queries which appends the data from each of these tables to a
third table. The primary key in the third table is supposed to be the
appended AutoNumbers from the two input tables. However, when the append
queries are run, the AutoNumber format does not append correctly. For
example, the K0025 appends as simply 25 and the L0019 appends as 19.

How can I get the whole AutoNumber to append to the new table?

Thanks!

 

[Jeff Boyce]

Pete

Access Autonumbers (that is, using the table designer and setting the data
type to Autonumber) do NOT have letters in them. If your tables are
displaying "K0025" in a field you can confirm is an autonumber data type,
then the FORMAT of that field (how it is displayed) must be "K" & "0000".
How a field is formatted and what is actually stored are not (necessarily)
the same.

If you are using two separate tables to store data entry by two separate
persons, your data structure could benefit from a bit more normalization.
Instead of using tables (or fields) to handle multiple data entry persons,
use a single table for the data, and add one additional field, to hold the
name/ID/whatever of the person doing the data entry.

Regards

Jeff Boyce
<OfficeAccess MVP>

 

[Douglas J. Steele]

Autonumbers cannot have letters in them. If you're seeing letters in your
AutoNumber fields, it has to be because of formatting, not because the
letters are in the field.

 

[John Spencer]

Autonumbers are just that - numbers. You can change the way they display by
using a format, but that does not change the way they are stored.

If your third table needs to store K0025 or L0019 then you need a text field
to contain the data. Or you need a text field to hold the Letter and a
number field to hold the numeric value.

You can use the format function in the append query to force the value to be
formatted as a string. Post your append query(s) and perhaps someone can
suggest the modification.

Perhaps something like:
Format([TheAutonumberField],"K0000") to replace [TheAutoNumberField] in
the query.

 

[Guest]

K0025 is text and not a number. Make sure the field it is being appended to
is text datatype.
If you set up a relationship with referential integrity and cascade update
you will not need to append.