UPDATE tbl1 "," tbl2 What is the comma for?

[Noob]

Hi,



Ok, so I have an UPDATE Query with 2 tbl(s) being referanced, but seperated with a comma. So my BEST guess is that we are going to UPDATE the 2 tbl(s) with what ever data is generated in the logic.
My question is what is this type of UPDATE Query is this commonly called, so that I can learn more about it? Is it a CrossTab? Maybe my assumtion is wrong if so please correct me.

TIA

-Noob

[JoeM]

Can you post the code so we can look at it?

[Noob]

Yeah I can give you a small taste. lol...

UPDATE     [REF Dino],
        [REF Form Info] 
SET    [REF Dino.Bones =  
       IIF(......

[JoeM]

I have never seen that before, an Update Query that updates two tables simultaneously.
Does it actually work?
Do all the correct records from both tables get updated?
How is the relationship between the two tables defined in the query?

It is definitely not called a CrossTab query, as CrossTab queries are not updateable.

[Noob]

Yeah I assume it works (I can VIEW the results). They want me to tell them what it does. lol... Its outside my wheel-house. I told them I know VBA. Not Access.They feel that its VBA & well yeah it can be done in VBA but it’s not nessicarilly. I'm not arguing with them. Just gonna try to give them what they want, as BEST I can. Thanks for giving it a shot @JoeM
Funny thing. I was just watching Lynda.Com Queries inDepth. I think they covered it in the lectures. I am reviewing it right now to see if I can stumble on it again.

[JoeM]

Test it out and see what happens.
The values in the SET part are the values being set.
The WHERE clause and JOIN will determine which records are being updated.

If you could post the whole query, we could help you analyze it.

[Noob]

Gonna be a bit of sanatizing. Gonna need a few minutes.

[Noob]

If you could post the whole query, we could help you analyze it.

Here it is in all it's glory.

UPDATE     [REF Dino],
        [REF Form Info] 
SET    [REF Dino].Bones = 
       IIF( 
              [REF Dino]![Category]="Region" 
       AND    [REF Dino]![Dug Type]<>"Gen",[REF Form Info]![Region BagOf Bones],
               IIF( 
                     [REF Dino]![Category] LIKE "*Spec*", 
                     IIF( 
                            ISNULL([REF Form Info]![WWF]),0.115,[REF Form Info]![WWF]),
                      IIF( 
                            [REF Dino]![Category]="Ret-30" 
                     AND    [REF Dino]![Dug Type]<>"Gen", 
                            IIF( 
                                   [REF Form Info]![Includes SSGs as BagOf?]="Yes",[REF Form Info]![Ret 30 BagOf Bones]-0.01,[REF Form Info]![Ret 30 BagOf Bones]),
                             IIF( 
                                   [REF Dino]![Category]="Ret-90" 
                            AND    [REF Dino]![Dug Type]<>"Gen", 
                                   IIF( 
                                          [REF Form Info]![Includes SSGs as BagOf?]="Yes",[REF Form Info]![Ret 90 BagOf Bones]-0.01,[REF Form Info]![Ret 90 BagOf Bones]),[REF Dino]![Bones])))),
        [REF Dino].[Discovery] = 
       IIF( 
              [REF Dino]![Category]="Region",[REF Form Info]![Region Discovery],
               IIF( 
                     [REF Dino]![Dug Type]="Gen", 
                     IIF( 
                            [REF Dino]![Category] LIKE "Ret*30",[REF Form Info]![Ret 30 Gen Discovery],[REF Form Info]![Ret 90 Gen Discovery]),
                      IIF( 
                            [REF Dino]![Category] LIKE "Ret*30",[REF Form Info]![Ret 30 BagOf Discovery],[REF Form Info]![Ret 90 BagOf Discovery])))
WHERE  ((( 
                            [REF Dino].Category)<>"Inj-E")));

[CJ_London]

an Update Query that updates two tables simultaneously.

it can in principle, however not sure what the effect is if potentially there are more than one records in one table being updated into the other table - it is the last 'repetition' but that would be random without some sense of order (not set in your query). My guess is that [REF Form Info] is a single record table.

a query with two or more tables without joins is called a Cartesian query. Ignoring any criteria, a table with 100 records 'Cartesian joined' to a table with 200 records will produce a 20,000 record recordset - every record in one table is 'mapped' to every table in the other record.

Your query is updating fields called Bones and Discovery in table [REF Dino] where category <>"Inj-E"

They are updated by a value generated by your complex nested iif statements.

I don't have time to interpret all of it but to start you off, For Bones if category="Region" and [dug type]<>"Gen", then it assigns the value in the [Region BagOf Bones] field of the [REF Form Info] table. Best way to map this is get a piece of paper and pay it out - each 'step' is a true or false result e.g.

                             category="Region" and [dug type]<>"Gen"
                      /                                    \
                     /                                        \
                  yes                                        no
assign [REF Form Info].[Region BagOf Bones]            
                                                       [REF Dino]![Category] LIKE "*Spec*"
                                             /                                    \
                                            /                                        \
                                         yes                                        no

[Noob]

WOW!!! Thanks for the detailed explanation & the sample tree @Ajax. Looking into Cartesian queries right now.

[CJ_London]

because they can generate very large datasets, Cartesian queries should be used with caution, but certainly have their uses.

you can mix Cartesian joins with inner joins within the same query but not left or right joins

so

'''
from (tablea inner join table on ....) . tablec
....

is OK

to use left or right joins you need to use subqueries

...
from (select * from tablea left join tableb on....) X, tablec
...

[Noob]

Yup good call. That is exactly what it is "Cartesian Query". Pretty cool. Thanks for the added insights much appreciated.