Help with a Query

T

the_grove_man

How would I do this in Access: I keep getting errors:

update target
set Assemrec = source.Recno
from @Components target Join @Components source on '<$-' +
target.RefDes + '>' = source.RefDes
 
J

John W. Vinson

How would I do this in Access: I keep getting errors:

update target
set Assemrec = source.Recno
from @Components target Join @Components source on '<$-' +
target.RefDes + '>' = source.RefDes
Click to expand...

This isn't a dialect of SQL with which I am familiar. I take it @Components is
a parameter? What's '<$-'?

What tables do you have; what are you trying to update, from what source?
 
T

the_grove_man

Components table"
1. RecNo (Primary key)
2. FileRec (Foreign Key to Files table's RecNo)
3. RefDes (String)
4. AssemRec (Self join to some RecNo in the same table)


Components is the table I want to query and update. The '<$-' is a
nomenclature I am trying to match in the same table. In other words,
in the column under 'Refdes':

Refdes = "<$-1A2J> will match another Refdes, however, with the <$->
stripped away.
Refdes = 1A2J

If <$-refdes> with its <$-> stripped away = refdes then
update the record (The AssemRec field) that has the refdes (The
one that didn't have the <$->)
with the RecNo of the record that contained the <$->

trust me I know it sounds confusing, but it isn't actually. It is just
hard to explain.
 
J

John W. Vinson

Components table"
1. RecNo (Primary key)
2. FileRec (Foreign Key to Files table's RecNo)
3. RefDes (String)
4. AssemRec (Self join to some RecNo in the same table)


Components is the table I want to query and update. The '<$-' is a
nomenclature I am trying to match in the same table. In other words,
in the column under 'Refdes':

Refdes = "<$-1A2J> will match another Refdes, however, with the <$->
stripped away.
Refdes = 1A2J

If <$-refdes> with its <$-> stripped away = refdes then
update the record (The AssemRec field) that has the refdes (The
one that didn't have the <$->)
with the RecNo of the record that contained the <$->

trust me I know it sounds confusing, but it isn't actually. It is just
hard to explain.
Click to expand...

I'm GUESSING that this should work:

SELECT A.*, B.*
FROM Components AS A
INNER JOIN Components AS B
ON A.Refdes = "<$-" & B.AssemRec & ">"
WHERE <any desired criteria>

What I don't understand is what fields you want updated, and what you want
them updated to.
 
T

the_grove_man

Let me give you an example, maybe that will help.

Example:
RecNo = 5
FileRec = 8
Refdes = <$-FG71>
AssemRec = 0

This will match another record:
RecNo = 58
FileRec = 8 (Same FileRec, this must match)
RefDes = FG71 (This also matches because it matches the above example
after the above is stripped of <$->)
AssemRec = 5 (Since it matches the FileRec and RefDes of the above, I
will paste the above RecNo in this field)

Hope that helps
 
J

John W. Vinson

Let me give you an example, maybe that will help.

Example:
RecNo = 5
FileRec = 8
Refdes = <$-FG71>
AssemRec = 0

This will match another record:
RecNo = 58
FileRec = 8 (Same FileRec, this must match)
RefDes = FG71 (This also matches because it matches the above example
after the above is stripped of <$->)
AssemRec = 5 (Since it matches the FileRec and RefDes of the above, I
will paste the above RecNo in this field)

Hope that helps
Click to expand...

OK. This may or may not be an updateable query. Is there a unique index on
RefDes? If so that will help a lot:

UPDATE tablename AS A
INNER JOIN tablename AS B
ON A.Refdes = "<$-" & B.RefDes & ">"
AND A.FileRec = B.FileRec
SET A.AssemRec = B.RecNo;

If the query isn't updateable try

UPDATE tablename
SET AssemRec =
DLookUp("RecNo", "tablename", "[RefDes] = '" & Mid([RefDes], 4, 4) & "' AND
[FileRec] = " & [FileRec])
WHERE AssemRec IS NULL;
 
T

the_grove_man

Thanks I will try this when i get to work. Yes, there is a unique
index, it is RecNo.

Recall the tablename is Components. The table IS an updateable table,
so I am not inserting new records just modifying existing records. I
think your first query will work just by looking at it. I will test it
later today and let you know.

UPDATE tablename AS A
INNER JOIN tablename AS B
ON A.Refdes = "<$-" & B.RefDes & ">"
AND A.FileRec = B.FileRec
SET A.AssemRec = B.RecNo;

Thank you for all your help, you guys are the best!

John G
 
T

the_grove_man

The problem I have is I trying to do this (Following your code)

UPDATE Components AS A
INNER JOIN Components AS B
ON mid(A.RefDes, 4, len(A.RefDes)-4) = B.RefDes
AND A.FileRec = B.FileRec
SET B.AssemRec = A.RecNo;

And I am getting an 'Invalid Procedure Call'

The problem with this:
UPDATE tablename AS A
INNER JOIN tablename AS B
ON A.Refdes = "<$-" & B.RefDes & ">"
AND A.FileRec = B.FileRec
SET A.AssemRec = B.RecNo;

Is the Components table has both matches like this:
<$-RF8> = RF8

So your join would just match <$-RF8> to <$-RF8>.
 
T

the_grove_man

Thanks all, I finally got it.

UPDATE Components AS A
INNER JOIN Components AS B
ON (A.FileRec = B.FileRec) AND
(mid(A.RefDes,4, len(A.refdes)-4) = B.RefDes)
SET B.AssemRec = A.RecNo
WHERE A.StandardRec=17;
 
Top