Sort Order of Update Query

[Steve_A]

I have two tables and I am updating columns in one from data in another one.

PnList table has a columns named as follows

Pn 09950 10140 10240
020-989-014
020-989-015
020-989-016
020-989-017

step02 table as columns named

Pn Shpef Socd
sorted ascend ascend dedend
020-989-017 09950 ZD
020-989-017 09950 PD
020-989-017 10140 ZD
020-989-017 10140 PD
020-989-017 10240 ZD
020-989-017 10240 PD
I am joining by the PN colum and selecting a value in Shpef and putting the
Socd value in the columns 0995 10140 and so on one column at a time. to look
like this

Pn 09950 10140 10240
020-989-014 PD PD PD
020-989-015 PD PD ZD
020-989-016 PD PD ZD
020-989-017 ZD ZD PD

now my delima, I am having problems when there are multible Socd's getting
it to populate the same way everytime. one time it will use the first Socd
value and other times it will use the last one.

how can i get it to use either the first or last every time??
here is my update query

UPDATE PnList INNER JOIN step02 ON PnList.Pn = step02.Pn SET PnList.[09950]
= [socd]
WHERE (((step02.Shpef) Like "09950"))
WITH OWNERACCESS OPTION;

 

[Petr Danes]

Short version is, you can't. Access doesn't recognize the concept of first
or last in any reliable way. If you want it to use a specific record, you
MUST give it some way to uniquely identify that record, otherwise your
results will be as they are now, where Access uses whatever it feels like at
the moment. Sorts come into play when displaying a recordset or feeding a
query to a report, but when you're doing a JOIN, there is no such thing as a
first record in order.

Pete

 

[Steve_A]

Thanks Pete, that is what I was afraid of as I tried inserting order by and
nothing would work.

thanks again for your time.

Short version is, you can't. Access doesn't recognize the concept of first
or last in any reliable way. If you want it to use a specific record, you
MUST give it some way to uniquely identify that record, otherwise your
results will be as they are now, where Access uses whatever it feels like at
the moment. Sorts come into play when displaying a recordset or feeding a
query to a report, but when you're doing a JOIN, there is no such thing as a
first record in order.

Pete

I have two tables and I am updating columns in one from data in another
one.

PnList table has a columns named as follows

Pn 09950 10140 10240
020-989-014
020-989-015
020-989-016
020-989-017

step02 table as columns named

Pn Shpef Socd
sorted ascend ascend dedend
020-989-017 09950 ZD
020-989-017 09950 PD
020-989-017 10140 ZD
020-989-017 10140 PD
020-989-017 10240 ZD
020-989-017 10240 PD
I am joining by the PN colum and selecting a value in Shpef and putting
the
Socd value in the columns 0995 10140 and so on one column at a time. to
look
like this

Pn 09950 10140 10240
020-989-014 PD PD PD
020-989-015 PD PD ZD
020-989-016 PD PD ZD
020-989-017 ZD ZD PD

now my delima, I am having problems when there are multible Socd's getting
it to populate the same way everytime. one time it will use the first Socd
value and other times it will use the last one.

how can i get it to use either the first or last every time??
here is my update query

UPDATE PnList INNER JOIN step02 ON PnList.Pn = step02.Pn SET
PnList.[09950]
= [socd]
WHERE (((step02.Shpef) Like "09950"))
WITH OWNERACCESS OPTION;

.