update one table based off of another 2

[jadams0173]

I'm trying to update a table based off criteria from another table in one update query. My tables look like this:

TempTable:
WorkOrder Date Print Item area
1234 7/7/2006 -1 abc I
1234 7/8/2006 -1 abc P
1234 NULL 0 abc E
5678 null 0 def I
5678 NULL 0 DEF P
5678 NULL 0 DEF E

The table that I want to update has this structure:
UpdateTable
Item Area Used(checkbox)
abc I 0
abc P 0
ABC E 0
DEF I 0
DEF P 0
DEF E 0

What I would like to happen is the query update the used column to -1(true) for the item if the print column =-1. If the print column is 0 then don't make any updates to the records in update table.

The big picture is to populate a form so that it shows only the Item and the area it is used. Now the user can see all the areas for each item. But if the user doesn't print the label for a particular area for a given item, next time I won't show them the area for that item.

 

[rjoubert]

Try this...

UPDATE TempTable INNER JOIN UpdateTable ON (TempTable.item = UpdateTable.item) AND (TempTable.area = UpdateTable.area) SET UpdateTable.used = True
WHERE (((TempTable.print)=True));

Good Luck!

 

[PHV]

Something like this ?
UPDATE UpdateTable AS U INNER JOIN TempTable AS T ON U.Item = T.Item AND U.Area = T.Area
SET U.Used = T.Print
WHERE T.Print = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jadams0173]

Thanks to the both of you for providing a solution!

 

[jadams0173]

After some trial I have discovered that my first approach will not work for me. Using the same sample data above, with a slight modification to the update table:

TempTable:
WorkOrder Date Print Item area
1234 7/7/2006 -1 abc I
1234 7/8/2006 -1 abc P
1234 NULL 0 abc E
5678 null 0 def I
5678 [red]7/10/2006 -1[/red] DEF P
5678 NULL 0 DEF E

The table that I want to update has this structure:
UpdateTable
Item Area [red]NOT[/red]Used(checkbox)
abc I 0
abc P 0
ABC E 0
DEF I 0
DEF P 0
DEF E 0

The desired results would be in the update table
Item Area [red]NOT[/red]Used(checkbox)
abc I 0
abc P 0
ABC E -1
DEF I -1
DEF P 0
DEF E -1


I need to check the boxes for the UNSED areas. I'm sorry for the confusion. Both tables have PK of autonumbers

 

[PHV]

Perhaps this ?
UPDATE UpdateTable AS U INNER JOIN TempTable AS T ON U.Item = T.Item AND U.Area = T.Area
SET U.NOTUsed = (Not T.Print)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jadams0173]

PHV,

I can't seem to get the desired results. What's happening is it looks like the results are reversed. The update is checking the notused box to true and leaving the areas really not used as false. Sometimes if multiple items are selected the result is the are not accurate such as:

P -1
I -1
E 0

and the result of the query will be in the update table as:

P 0
I -1
e -1

 

[jadams0173]

I have resolved this issue. Thanks PHV for your help. It got me on the right track.