Compound Keys, Update

[Guest]

I have a table like this:

System (Text)
SeqNbr (Nbr)
KillFlag (Boolean)
..
..
..

It may also have other fields and an autonumber primary key...

I need to select certain records where the System is, say, "XYZ" and
SeqNbr is, say, 5 (I have another table with the list of systems and sequence
numbers....), and then use the KillFlag from SeqNbr 5 to update the KillFlag
for SeqNbr 19.

My mind isn't getting around this problem. It seems like it should be a
simple SQL solution....Can anyone offer ideas?

 

[Guest]

How do you get from SeqNbr 5 to the KillFlag for SeqNbr 19?

 

[Van T. Dinh]

If I understand your description correctly, you need 2 copies of the Tables
and the SQL should be something like (untested):

UPDATE [YourTable] AS Main INNER JOIN [YouTable] AS SUB
ON Main.[System] = Sub.[System]
SET Main.KillFlag = Sub.KillFlag
WHERE (Main.[System] = "XYZ")
AND (Main.SeqNbr = 19)
AND (Sub.SeqNbr = 5)

 

[Guest]

2 physical copies, or 2 references to the same table?

The map rule is fairly simple, assume a 3rd table...
--
Jim

If I understand your description correctly, you need 2 copies of the Tables
and the SQL should be something like (untested):

UPDATE [YourTable] AS Main INNER JOIN [YouTable] AS SUB
ON Main.[System] = Sub.[System]
SET Main.KillFlag = Sub.KillFlag
WHERE (Main.[System] = "XYZ")
AND (Main.SeqNbr = 19)
AND (Sub.SeqNbr = 5)

--
HTH
Van T. Dinh
MVP (Access)

I have a table like this:

System (Text)
SeqNbr (Nbr)
KillFlag (Boolean)
.
.
.

It may also have other fields and an autonumber primary key...

I need to select certain records where the System is, say, "XYZ" and
SeqNbr is, say, 5 (I have another table with the list of systems and
sequence
numbers....), and then use the KillFlag from SeqNbr 5 to update the
KillFlag
for SeqNbr 19.

My mind isn't getting around this problem. It seems like it should be a
simple SQL solution....Can anyone offer ideas?

 

[Van T. Dinh]

Only 1 Table in your datanse but it is used twice in the query ...

3rd Table ??? no ideas what you are referring to.

 

[Guest]

Sorry...you asked how I decided what secondary table element to update...It's
a set of rules I can put into a table...not complex. Thanx for your answer. I
really appreciate your help!
--
Jim

Only 1 Table in your datanse but it is used twice in the query ...

3rd Table ??? no ideas what you are referring to.

 

[Van T. Dinh]

Actually, Karl asked that question and not I and therefore I didn't know
what you meant..

Hope you worked it out ...