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

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

HTH

On Nov 3, 9:10 pm, AJCB <A...@discussions.microsoft.com> wrote:
> 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

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?

"(E-Mail Removed)" wrote:

> in the table under format for the field "PalletID" type "PL#" or "PL"#
>
> HTH
>
> On Nov 3, 9:10 pm, AJCB <A...@discussions.microsoft.com> wrote:
> > 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
>
>
>

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
http://mvp.support.microsoft.com/

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


"AJCB" <(E-Mail Removed)> wrote in message
news:0502C53A-5380-4CA4-977F-(E-Mail Removed)...
> 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

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
http://mvp.support.microsoft.com/

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

"AJCB" <(E-Mail Removed)> wrote in message
news:2B61038F-EE13-409D-B3E8-(E-Mail Removed)...
> 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?
>
> "(E-Mail Removed)" wrote:
>
> > in the table under format for the field "PalletID" type "PL#" or "PL"#
> >
> > HTH
> >
> > On Nov 3, 9:10 pm, AJCB <A...@discussions.microsoft.com> wrote:
> > > 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
> >
> >
> >