Update query on the basis of three variables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Eric - 23 Aug 2006 16:38 GMT

In tbl_EquipmentChronology there are three variables Equipment1,
Equipment2, Equipment3. My query worsk fine but on the basis of
[tbl_EquipmentChronology].[Equipment1] as i have to check if Equipment2
has a value then instead of Equipment1 i have to grab Equipment2 value
or if Equipment3 has a value i have to grab from Equipment3. How to i
change my query.

UPDATE tbl_Events INNER JOIN tbl_EquipmentChronology ON
tbl_Events.TicketNum = tbl_EquipmentChronology.TicketNum SET

tbl_Events.txt = [tbl_EquipmentChronology].[Equipment1]
WHERE (((tbl_Events.PPVVOD_Outlet)=[Tbl_EquipmentChronology].[Outlet]
And (tbl_Events.PPVVOD_Outlet)<>0));

Thanks,

Reply to this Message

pietlinden@hotmail.com - 23 Aug 2006 20:22 GMT

Yuck. Normalize and your problems will go away. The update will be
simple.

Reply to this Message

pietlinden@hotmail.com - 23 Aug 2006 20:23 GMT

Yuck. Normalize and your problems will go away. The update will be
simple.
Any time you have FieldName1, FieldName2, FieldName3,... you should
recognize the problem. These should all be values in a child table.
Then querying etc is easy.

If you want really fun, try doing filters and updates for tables
structured like this.

Glad it's you using that DB and not me.

Reply to this Message

Jeff L - 23 Aug 2006 20:25 GMT

UPDATE tbl_Events a INNER JOIN tbl_EquipmentChronology b ON
aTicketNum = b.TicketNum
SET a.txt = IIF(Not IsNull(b.Equipment3), b.Equipment3, iif(Not
IsNull(b.Equipment2), b.Equipment2,b.Equipment1))
WHERE (((tbl_Events.PPVVOD_Outlet)=[Tbl_EquipmentChronology].[Outlet]
And (tbl_Events.PPVVOD_Outlet)<>0));

> In tbl_EquipmentChronology there are three variables Equipment1,
> Equipment2, Equipment3. My query worsk fine but on the basis of
[quoted text clipped - 5 lines]
> UPDATE tbl_Events INNER JOIN tbl_EquipmentChronology ON
> tbl_Events.TicketNum = tbl_EquipmentChronology.TicketNum SET

> tbl_Events.txt = [tbl_EquipmentChronology].[Equipment1]
> WHERE (((tbl_Events.PPVVOD_Outlet)=[Tbl_EquipmentChronology].[Outlet]
> And (tbl_Events.PPVVOD_Outlet)<>0));
>
> Thanks,

Reply to this Message

Arno R - 24 Aug 2006 13:35 GMT

Why are you not listening ??
Please do NOT multipost !!!!

If you *have to* then crosspost.
Multi-posting really is wasting people's time.

Arno R

> In tbl_EquipmentChronology there are three variables Equipment1,
> Equipment2, Equipment3. My query worsk fine but on the basis of
[quoted text clipped - 11 lines]
>
> Thanks,

Reply to this Message