Update query on the basis of three variables

[Eric]

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,

 

[Jeff Boyce]

Eric

By changing your data structure. From your description, you have "repeating
fields" (e.g., "Equip1, Equip2, Equip3"). While this is what you need to do
.... in a spreadsheet, you need to have a well-normalized data structure to
make the best use of Access' features and functions.

If you can have zero to many pieces of Equipment, create a "child" table
that holds as many ROWS (not columns) as you have pieces of Equipment for
each of your "parent" records (?you didn't describe the "parent").

Then, your query merely looks for Equipment (a single column), rather than
in E1, E2, E3, ..., En.

By the way, if you ever had to add an Equipment4 to your current design,
you'd have to redo all your related queries, reports, forms, macros, and
procedures, not to mention having to modify your table structure. With the
alternate approach above, you'd only have to add one more row to an
Equipment table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Arno R]

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