Compound Keys, Update

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JimS - 06 Dec 2005 21:20 GMT

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?

Signature

Jim

Reply to this Message

KARL DEWEY - 06 Dec 2005 22:19 GMT

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

> I have a table like this:
>
[quoted text clipped - 14 lines]
> My mind isn't getting around this problem. It seems like it should be a
> simple SQL solution....Can anyone offer ideas?

Reply to this Message

Van T. Dinh - 07 Dec 2005 01:27 GMT

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)

Signature

HTH
Van T. Dinh
MVP (Access)

>I have a table like this:
>
[quoted text clipped - 16 lines]
> My mind isn't getting around this problem. It seems like it should be a
> simple SQL solution....Can anyone offer ideas?

Reply to this Message

JimS - 07 Dec 2005 01:39 GMT

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

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

Signature

Jim

> If I understand your description correctly, you need 2 copies of the Tables
> and the SQL should be something like (untested):
[quoted text clipped - 26 lines]
> > My mind isn't getting around this problem. It seems like it should be a
> > simple SQL solution....Can anyone offer ideas?

Reply to this Message

Van T. Dinh - 07 Dec 2005 02:10 GMT

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

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

Signature

HTH
Van T. Dinh
MVP (Access)

>2 physical copies, or 2 references to the same table?
>
> The map rule is fairly simple, assume a 3rd table...

Reply to this Message

JimS - 07 Dec 2005 02:53 GMT

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!

Signature

Jim

> Only 1 Table in your datanse but it is used twice in the query ...
>
[quoted text clipped - 3 lines]
> >
> > The map rule is fairly simple, assume a 3rd table...

Reply to this Message

Van T. Dinh - 07 Dec 2005 05:08 GMT

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

Hope you worked it out ...

Signature

HTH
Van T. Dinh
MVP (Access)

> 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!

Reply to this Message