A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Sort Order of Update Query



 
 
Thread Tools Display Modes
  #1  
Old May 6th, 2010, 08:40 PM posted to microsoft.public.access.queries
Steve_A
external usenet poster
 
Posts: 34
Default Sort Order of Update Query

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;


  #2  
Old May 6th, 2010, 09:17 PM posted to microsoft.public.access.queries
Petr Danes[_3_]
external usenet poster
 
Posts: 52
Default Sort Order of Update Query

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" allen.stATverizon.net.huh pÃ*Å¡e v diskusnÃ*m pÅ™Ã*spÄ›vku
...
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;



  #3  
Old May 6th, 2010, 10:18 PM posted to microsoft.public.access.queries
Steve_A
external usenet poster
 
Posts: 34
Default Sort Order of Update Query

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

thanks again for your time.


"Petr Danes" wrote:

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" allen.stATverizon.net.huh pÃ*Å¡e v diskusnÃ*m pÅ™Ã*spÄ›vku
...
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;



.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:11 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.