mscertified said:
I'm sorry but I have no idea how to use the query designer to do what I want
to do. I always code in raw SQL. I still have not been bale to get multiple
left joings to work after 2 days iof trying. No-one seems to know how to do
it, Access help as usual is no help and none of my Access books even mention
it, none of my SQL books either.
Hi Rupert,
I believe one of the reasons "updating outer joins"
is not usually addressed is because it can have the
(possibly) unintended effect of *appending records*
or, depending on the situation, give update errors
which are hard to understand.
Let's start with a "simple example"
3 simple tables where fA,fB,fC are type Long w/ no Default Value
and AID, BID, CID are autonumber pk's:
table A table B table C
------------- -------------- --------------
AID fA txtA BID fB txtB CID fC txtC
1 1 red 1 1 brown 1 2 orange
2 2 blue 2 2 tan 2 3 yellow
3 3 green
------------qryfAeqfB_fAeqfC ------------------
SELECT
A.AID,
A.fA,
A.txtA,
B.BID,
B.fB,
B.txtB,
C.CID,
C.fC,
C.txtC
FROM
(A LEFT JOIN B ON A.fA = B.fB)
LEFT JOIN C ON A.fA = C.fC;
results:
AID fA txtA BID fB txtB CID fC txtC
1 1 red 1 1 brown
2 2 blue 2 2 tan 1 2 orange
3 3 green 2 3 yellow
one possible update scenario:
UPDATE
(A LEFT JOIN B ON A.fA = B.fB)
LEFT JOIN C ON A.fA = C.fC
SET
B.txtB = [A].[txtA],
C.txtC = [A].[txtA];
if run our SELECT query again after update qry:
AID fA txtA BID fB txtB CID fC txtC
1 1 red 1 1 red
2 2 blue 2 2 blue 1 2 blue
3 3 green 2 3 green
But look at the tables
(we've actually *APPENDED* records also):
original tables:
table A table B table C
------------- -------------- --------------
AID fA txtA BID fB txtB CID fC txtC
1 1 red 1 1 brown 1 2 orange
2 2 blue 2 2 tan 2 3 yellow
3 3 green
new table B
----------------
BID fB txtB
1 1 red
2 2 blue
3 green
new table C
-------------
CID fC txtC
1 2 blue
2 3 green
3 red
this effect can be used to your advantage
in some situations if you understand your
data structure thoroughly, but is filled with
pitfalls that would need to be addressed
at length in a general discussion (like in a book).
Is this simple example close to what you
were attempting to do? If not, can you
change it (while keeping it simple), to give
us sample data and structure? Any indexes and
default values would be important things
to provide.
I'm with Andreas though. Make a copy
of your db. Then (w/copy) play with the Select
query in designer, then change it an update
query and see if the results are what you want.
Click on "Create Query in Design View"
In the Show Table dialog box,
click on your table A,
click Add,
click on your table B,
click on Add again,
click on your table C,
click on Add again,
and then click Close.
You should now show your 3 tables
in the query designer.
-- join A to B ----
Click and hold down on table A's
"join field" (fA in above)
and "drag and drop"
over on table B's "join field" (fB in above)
You should now have a (join) line
connecting the 2 tables going
from A.fA to B.fB.
Right-mouse click on this join line
and choose "Join Properties." Select the
option (probably 2) that "includes all
records from table A and only those
records from table B where the join
fields are equal."
The join line should now have an arrow
at the end pointing to fB
-- join A to C ----
Click and hold down on table A's
"join field" (fA in above)
and "drag and drop"
over on table C's "join field" (fC in above)
You should now have a (join) line
connecting the 2 tables going
from A.fA to C.fC.
Right-mouse click on this join line
and choose "Join Properties." Select the
option (probably 2) that "includes all
records from table A and only those
records from table C where the join
fields are equal."
The join line should now have an arrow
at the end pointing to fC.
--- bring all fields down into grid ---
Double-click on top of table A to
select all the fields in table A.
Drag-and-drop the selection down
into a field row of the grid.
Do the same for the other 2 tables.
Save your SELECT query.
Look at the results to verify this is
what you expect.
--- change to update query ---
In the top menu, click on "Query"
and in the drop-down click on
"Update Query."
This will add a new row in the grid
"Update To:"
Find the columns in the grid of the
fields you want to update, and in
this new "Update To:" row, type
in the table.field to which you want
to update them.
In our simple example above,
here is what the grid would look like:
Field: txtB txtC
Table: B C
Update To: A.txtA A.txtA
Save the query.
Then run by clicking on the red exclamation
mark in the top menu.
Go back and look at your tables
and verify that it is doing what you
expected.
Since you are doing this on a *copy*
of your db, you can experiment further
by deleting tables A, B, and C in your copy,
and then reimporting them from the original db.
Please respond back if I have misunderstood
or was not clear about something.
Good luck,
Gary Walter
