Adding Autonumber with a prefix

[Guest]

Hi.

Hope someone can help me?
I have a database with the following fields.

PalletID
Location
Trailer No
Trailer Sheet No. (Currently Blank)
Update

We have a number of lorries, all with different Trailer Nos which come and
go each day.
When they are being loaded, the pallets get scanned and then placed on the
trailer.
What I am trying to do is run a query that says all lines with the update
field of "0"
and a location of "PL" I want to enter an autonumber eg. PL000001.
Then when the next trailer has been scanned, I want to run the query again
which will give all the pallets on that trailer PL000002 and so on.

I have not had any training on SQL query, so if this is possible in design
view, I would appreciate it. I have tried SQL, but without success.

Cheers
AJ

 

[fredloh]

in the table under format for the field "PalletID" type "PL#" or "PL"#

HTH

 

[Guest]

Where I am trying to enter this is the Trailer Sheet No. This does't not
necessarily have to roll, so long as it is there, or there abouts.

So in the update query, do I have to put "PL#" in the Trailer Sheet update
to section?

 

[Jeff Boyce]

One of the basic database design recommendations is that you don't store
more than one fact in one field. Storing "PL" plus a number in a field
would violate this recommendation.

As you've already seen in another response, you could simply add formatting
(controls what gets displayed, not what is stored) to get what you're after.

Another approach is to use a query to add ("concatenate") "PL" plus the
value of a field (?your "autonumber").

By the way, and just in case you weren't aware of it, the Access Autonumber
data type is NOT guaranteed to be sequential and is generally unfit for
human consumption. It is designed to provide a unique row identifier ...
that's it.

If you might need sequential numbers, you will need to "roll your own".
There are techniques for this shown at
mvps.org/access

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Jeff Boyce]

We're not there. We don't know what your "trailer sheet" is.

In a query you can create a new field using something like:

NewField: "PL#" & [YourSequenceNumberField]

(using your own field name(s).)


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/