Help with a Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

the_grove_man@yahoo.com - 29 Feb 2008 21:42 GMT

Let me paraphrase a query I want to do.

First, I have a table called 'Components'
This table has these fields:

RecNo (Primary key)
FileRec (Foreign key to Files Table)
StandardRec (Foreign key to StandardTypes table)
RefDes (String)
AssemRec (Self-join to a RecNo)

Here is my paraphrase:
UPDATE Components SET AssemRec = the primary key in same table where in the
refdes concatenation '<$-' + Refdes + '>' = RefDes AND the FileRec's are the
same.

So as an example, here is sample data.
RecNo = 5
FileRec = 10
StandardRec = 17
RefDes = <$-156AF>
AssemRec = 0

Since the refdes matches another Refdes in the same table if the brackets,
dollar sign and - are stripped away, I would paste the RecNo into the
AssemRec of the match.

The above data would match this:
RecNo = 10
FileRec = 10 (Same File)
StandardRec = 20
RefDes = 156AF (Notice it matches when stripped of <$->)
AssemRec = 5 (Since the Refdes matches and the filerec matches, I will paste
in the top RecNo into the AssemRec)

Thanks for any help.

Reply to this Message

Ken Snell (MVP) - 02 Mar 2008 18:16 GMT

Try this:

UPDATE Components SET AssemRec =
(SELECT TOP 1 T.RecNo
FROM Components AS T
WHERE Replace(Replace(T.Refdes,">",""),'<$-',"")
= Replace(Replace(Components.Refdes,">",""),"<$-","")
AND T.FileRec = Components.FileRec AND
T.RecNo Is Not Null
ORDER BY T.RecNo);

Note that you're violating normalization rules when you store multiple data
items in one field -- your Refdes field. It contains the desired value
surrounded by various "delimiters". It would be better, and easier to query
your data, if you stored such information in three separate fields -- one
for the ">", one for the middle value, and one for the ">".

Signature

Ken Snell
<MS ACCESS MVP>

> Let me paraphrase a query I want to do.
>
[quoted text clipped - 35 lines]
>
> Thanks for any help.

Reply to this Message

Ken Snell (MVP) - 02 Mar 2008 18:24 GMT

This modified version will not overwrite any records that already have a
value in AssemRec, if that would be your desire:

UPDATE Components SET AssemRec =
(SELECT TOP 1 T.RecNo
FROM Components AS T
WHERE Replace(Replace(T.Refdes,">",""),'<$-',"")
= Replace(Replace(Components.Refdes,">",""),"<$-","")
AND T.FileRec = Components.FileRec AND
T.RecNo Is Not Null
ORDER BY T.RecNo)
WHERE Components.AssemRec Is Null;

And this modified version will not overwrite any records that already have a
nonzero value in AssemRec, if that would be your desire:

UPDATE Components SET AssemRec =
(SELECT TOP 1 T.RecNo
FROM Components AS T
WHERE Replace(Replace(T.Refdes,">",""),'<$-',"")
= Replace(Replace(Components.Refdes,">",""),"<$-","")
AND T.FileRec = Components.FileRec AND
T.RecNo Is Not Null
ORDER BY T.RecNo)
WHERE Components.AssemRec Is Null
OR Components.AssemRec = 0;

Signature

Ken Snell
<MS ACCESS MVP>

> Try this:
>
[quoted text clipped - 52 lines]
>>
>> Thanks for any help.

Reply to this Message

the_grove_man@yahoo.com - 04 Mar 2008 00:39 GMT

Thanks,
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;

Reply to this Message