UPDATE query within a query possible? 1

[everest]

Hi,

I have query that join two tables together.

Table 1
Site Element
A-1 Iron
A-1 Copper
A-1 Zinc
B-2 Iron
B-2 Lead

Table 2
Element Percent
Iron 60%
Copper 20%

Joins into Query 3
Query 3
Site Element Percent
A-1 Iron 60%
A-1 Copper 20%
A-1 Zinc
B-2 Iron 60%
B-2 Lead

I need to update the Percent field to set any blanks to 100%. I know an UPDATE query would easily do this, but only if it were a table.

I'd like to be able to run something like an UPDATE query on Query 3. Reason for this is that I have other queries that are run on Query 3 afterwards and I don't want to have to insert it into a table and manually run an UPDATE query each time.

So repeating my question, is it possible to run something like an UPDATE query on Query 3? Any help would be much appreciated. Thanks.

Everest

 

[mpastore]

Everest,

You don't really need an update query, what you need is a query that shows a calculated value. In query designer, join tables 1 and 2 based on element, but in join properties include all table1 values even if there is not a match in table2. Now you have a calculated field expression for percentage:

mypercent: iif(isnull(table2.percent),100,table2.percent)

Mike Pastore

Hats off to (Roy) Harper

 

[everest]

Thanks for your reply Mike,

I tried to simply my example, but I guess it backfired on me. Totally my fault for leaving out important information. I actually calculate the percent field in the current table. I'm not sure how to use the IIF function with a calculation.

Here's a little more detailed example.
Table 1
Site Element Total
A-1 Iron 10
A-1 Copper 10
A-1 Zinc 10
B-2 Iron 10
B-2 Lead 10

Table 2
Element Total2
Iron 6
Copper 2

Calculate Percent field using LEFT JOIN and(Total2/Total)*100 AS Percent ON Table1.Element=Table2.Element
Joins into Query 3
Query 3
Site Element Percent
A-1 Iron 60%
A-1 Copper 20%
A-1 Zinc
B-2 Iron 60%
B-2 Lead

Is there anyway of using the IIF function with this query? Thanks again for your help.

Everest

 

[mpastore]

... ((iif(isnull([Total2]),[Total],[Total2])/Total)*100) as percent

remember to set join properties to allow all values from table1 whether or not there is a corres. value from second table

Mike Pastore

Hats off to (Roy) Harper

 

[everest]

Thanks for your help Mike.

That did the trick.