Update query based on previous record

[Steve_A]

I have tried to search on this but not sure what to look for.

I have the following, I have to add the AssyPN field so it is blank

Tbl: 213
Fields: ID autonum
AssyPN text
INDN text
DetPN text
QTY text but I can change if need to do a calc

I need to populate the AssyPN filed, To do this I need to read the INDN
number then look up to find the next lower number above it then populate that
DetPN in the AssyPN field. It would look something like this

ID AssPN INDN DetPN QTY
6110 00 145T8901-802-001 1
6152 145T8901-802-001 01 100T1490-009 1
6153 100T1490-009 02 100T1490-010 1
6154 100T1490-009 02 100T1490-011 1
6156 145T8901-802-001 01 145T8750-882 1
6157 145T8750-882 02 100T1490-011 1
6158 145T8750-882 02 100T1490-013 1
6193 145T8901-802-001 01 145T8750-882 1
6194 145T8750-882 02 145T8750-053 1
6197 145T8750-882 02 145T8750-082 1
6201 145T8901-802-001 01 145T8755-032-001 1
6205 145T8901-802-001 01 145T8756-8004-001 1
6206 145T8756-8004-00 02 BACF3T01A22N7 2
6208 145T8756-8004-00 02 145T8750-027 1
6209 145T8756-8004-00 02 145T8750-028 1
6210 145T8756-8004-00 02 145T8756-004 1
6211 145T8756-8004-00 02 145T8879-001 1

I hope this makes some sense, btw this is coming off a mainframe.

Thanks for looking

 

[S.Clark]

I don't believe you provided enough info to get a step by step answer, but
this is not something that you would solve with just one query either.

Using a custom VBA procedure, you could walk through your steps and execute
several queries to get to the desired end result.

 

[Steve_A]

I know this is not a easy one, I am used to multiple step queries and some
coding.let see if this sheds some light on my needs.

Look at the line that has 145T8750-027 in the detPn col. in front of it is
a 02 in the INDN col. to find its next assembly I have to go to the previous
01 above the 02 which is 145T8756-8004-001 in the DetPN col above it, then
copy the 145T8756-8004-001 and put it in the AssPN field in front of the
145T8750-027.

another way to look at the data is that it starts with a assembly part
number and then the detail parts it takes to make it under it. What I need
is to put all the next assemblies in the same row as the detail part. The ID
field is what keeps everything in the correct order, there will be on input
to this it is for doing lookups and reports only.

I hope this helps some

Thanks

 

[Bob Barrows [MVP]]

I know this is not a easy one, I am used to multiple step queries and
some coding.let see if this sheds some light on my needs.

Look at the line that has 145T8750-027 in the detPn col. in front of
it is
a 02 in the INDN col. to find its next assembly I have to go to the
previous 01 above the 02 which is 145T8756-8004-001 in the DetPN col
above it, then copy the 145T8756-8004-001 and put it in the AssPN
field in front of the 145T8750-027.

another way to look at the data is that it starts with a assembly part
number and then the detail parts it takes to make it under it. What
I need is to put all the next assemblies in the same row as the
detail part. The ID field is what keeps everything in the correct
order, there will be on input to this it is for doing lookups and
reports only.

This query should provide the value you want in the NewAssPN column.

SELECT a.ID, a.AssPN, a.INDN, a.DetPN, a.QTY,
(SELECT TOP 1 DetPN As AssPN FROM Assy As q
WHERE q.ID < a.ID AND q.INDN < a.INDN Order By q.ID DESC)
As NewAssPN
FROM Assy AS a

Give it a try and see if it suits. If so, you can link it to the
original table to do the update ... like this:
Hmmm ... I can't seem to use the above subquery in an UPDATE query: I
get the "must use updateable cursor" error.

You will likely have to insert the above into a work table and join to
the work table to perform the update. oh well.

SELECT a.ID,
(SELECT TOP 1 DetPN As AssPN FROM Assy As q
WHERE q.ID < a.ID AND q.INDN < a.INDN Order By q.ID DESC)
As NewAssPN
INTO WorkTable
FROM Assy AS a

UPDATE assy a join WorkTable w
on a.ID = w.ID
SET AssPN = NewAssPN

DROP Table WorkTable

 

[Steve_A]

This query should provide the value you want in the NewAssPN column.

SELECT a.ID, a.AssPN, a.INDN, a.DetPN, a.QTY,
(SELECT TOP 1 DetPN As AssPN FROM Assy As q
WHERE q.ID < a.ID AND q.INDN < a.INDN Order By q.ID DESC)
As NewAssPN
FROM Assy AS a

Give it a try and see if it suits. If so, you can link it to the
original table to do the update ... like this:
Hmmm ... I can't seem to use the above subquery in an UPDATE query: I
get the "must use updateable cursor" error.

You will likely have to insert the above into a work table and join to
the work table to perform the update. oh well.

SELECT a.ID,
(SELECT TOP 1 DetPN As AssPN FROM Assy As q
WHERE q.ID < a.ID AND q.INDN < a.INDN Order By q.ID DESC)
As NewAssPN
INTO WorkTable
FROM Assy AS a

UPDATE assy a join WorkTable w
on a.ID = w.ID
SET AssPN = NewAssPN

DROP Table WorkTable

well I need some clarification,

in the first part do I put that in my query as a sub query?

is a work table like creating a temp table, I looked for definition and
could only find reference to problems with it not what it is "LOL"

Next I create a new query and join my table and the worktable??

Thanks for your patience

 

[Bob Barrows [MVP]]

well I need some clarification,

in the first part do I put that in my query as a sub query?

First part? You mean where I said to give it a try? No, I meant you to
try that sql as written (substituting your table's name for "Assy" of
course).
Open your database,
go to the Queries tab,
click the New button and tell it you want to use Design,
close the Tables dialog without selecting any,
switch the query to SQL View (View menu or toolbar button)
and paste in this sql:

SELECT a.ID, a.AssPN, a.INDN, a.DetPN, a.QTY,
(SELECT TOP 1 DetPN As AssPN FROM Assy As q
WHERE q.ID < a.ID AND q.INDN < a.INDN Order By q.ID DESC)
As NewAssPN
FROM Assy AS a

Run it. Do the results look correct? I.E. is the value for NewAssPN the
correct one to be using for each row of data?
If so, move on.
If not, show me some sample data with which the above query gives the
wrong results and explain why the results are wrong.

is a work table like creating a temp table, I looked for definition
and could only find reference to problems with it not what it is "LOL"

It's just a generic term for a table you are going to be "working" with
temporarily. SQL Server has temp tables which get deleted automatically
at the end of the session in which they were created. Access does not
have this, which is why I explicitly showed the DROP TABLE sql. You have
to explicitly drop (delete) the table when you are finished.

This query (which Access calls a "make-table" query) creates the work
table. Note the "INTO WorkTable" piece.
SELECT a.ID,
(SELECT TOP 1 DetPN As AssPN FROM Assy As q
WHERE q.ID < a.ID AND q.INDN < a.INDN Order By q.ID DESC)
As NewAssPN
INTO WorkTable
FROM Assy AS a

Next I create a new query and join my table and the worktable??

I'm not sure what you are asking ... I showed the update query:

UPDATE assy a join WorkTable w
on a.ID = w.ID
SET AssPN = NewAssPN

Again: create a query in Design view, switch to SQL View and paste that
in.

I would probably use VBA to automate this process, unless it was only
going to be a one-time update.

 

[Steve_A]

That looks like it did the trick, I did it on a small one and the make table
took about 30 minutes to do 15,000 numbers but that is ok, still better than
having to do it by hand.

My next one will be over 180,000 records. guess I will start it and go to
bed "LOL"

thanks a bunch this will be a life saver

 

[Bob Barrows [MVP]]

Ooh! if you have the possibility of using a passthrough query to create that
table in SQL Server, you should definitely do that! In fact, a stored
procedure in SQL Server should make much faster work of this.

 

[Steve_A]

Well darn I am now getting "At most one record can be returned by this sub
query"

It is only happening on the make table. Does the local computer cach
someting to cause this?

Thanks

 

[Steve_A]

I wish I could use SQL server for my stuff but they wont let me. This may be
slow but it is better than having to go through and looking everything up by
my lonsome.

 

[Bob Barrows [MVP]]

Well darn I am now getting "At most one record can be returned by
this sub query"

It is only happening on the make table. Does the local computer cach
someting to cause this?

No, it's probably related to the extra data you are including in the
process. Let's look at the query again:

SELECT a.ID,
(SELECT TOP 1 DetPN As AssPN FROM Assy As q
WHERE q.ID < a.ID AND q.INDN < a.INDN Order By q.ID DESC)
As NewAssPN
INTO WorkTable
FROM Assy AS a

The error means that the subquery, SELECT TOP 1 DetPN As AssPN FROM Assy As
q
WHERE q.ID < a.ID AND q.INDN < a.INDN Order By q.ID DESC, is retrieving
more than one record for a particular ID, INDN combination. The problem is
that "TOP 1" does not guarantee a single record will be returned. In the
case of a tie, the records that are tied will be retrieved. So the question
is: are ties allowed in your business rules? From what you had previously
described, I would think the answer would be "no" since we are ordering by
ID and you said the ID would uniquely identify a row.

Now it seems that ID is not really unique. Is that a problem with the data?
Or is it something that is allowed to happen within your business rules?

If it is a problem that needs to be fixed, you can identify the problem
records by using a grouping query:

select ID, count(*) as IDCount
from Assy
Group By ID
Having count(*) > 1

If it is allowable in your business, you need to describe how to break the
ties.

 

[Steve_A]

Sorry it took so long to get back to you, Yep that was my porblem, when I
went to my production database I made my small table using the data after
several queries and some created multipla lines of ID. so I just went to my
original table and made it and it runs like a charm

Thanks Bob