Prev: Criteria for an email address fied
Next: Finding a function call in queries
From: Steve_A on 6 May 2010 15:40
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;


From: Petr Danes on 6 May 2010 16:17
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
news:65B99D74-B9A8-47AF-87FB-8AD4AC677AE5(a)microsoft.com...
>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;
>
>

From: Steve_A on 6 May 2010 17:18
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
> news:65B99D74-B9A8-47AF-87FB-8AD4AC677AE5(a)microsoft.com...
> >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;
> >
> >
>
> .
>
 | 
Pages: 1
Prev: Criteria for an email address fied
Next: Finding a function call in queries