Roger Bell said:
Thanks, Have done as you suffested with the following code:
UPDATE [MAIN TABLE], [MAIN TABLE1] SET [MAIN TABLE1].[Street Name] = [MAIN
TABLE].[STREET NAME]
WHERE ((([MAIN TABLE].[Envelope Number])=1));
This works, except it updates all Street Names in the data Base, not just
the one relating to the Envelope 1.
Where have I gone wrong again?
Many thanks
Roger Bell,
I left off a line of SQL in my example because I had written directly
before the example:
"I also assumed you would be adding another table in the query in the
source database and joining it to the table in the other database to
provide the data for the update."
I assumed you would use my example, in general, replacing my example
column and table names with yours, and that you would switch to the
query grid, and drag and drop some column names between the displayed
table-boxes in the top of the query grid in order to establish the
appropriate connection.
Doing that would have added the missing line (or lines) of code.
My SQL with the missing line (example version of the missing line):
UPDATE YourLinkedDestinationDatabaseTable AS T1
INNER JOIN
YourSourceDatabaseTable AS T2
ON T1.PrimaryKey = T2.PrimaryKey
SET T1.[Street Name] = T2.[street name];
Where you change "ON T1.PrimaryKey = T2.PrimaryKey" so that my example
column names (PrimaryKey) on each side of the = sign are replaced by
the correct column names in your two tables. If there are multiple
primary key columns, start a new line below ON beginning with AND.
ON T1.PrimaryKey1 = T2.PrimaryKey1
AND T1.PrimaryKey2 = T2.PrimaryKey2
You can also use the query grid to make or change these conditions.
Whenever you run a Query (SELECT, UPDATE, INSERT, DELETE,
SELECT...INTO, TRANSFORM...PIVOT) with more than two tables, you must
instruct the database on what columns are used to "join" the tables
together.*
INNER JOIN means that only rows in both tables where values in both
sets of columns match are "joined".
LEFT JOIN means that all rows in both table are returned, and when a
row in the right-hand table has no match for the specified ON
conditions, NULLS are returned in any output columns for that table.
RIGHT JOIN means that all rows in both table are returned, and when a
row in the left-hand table has no match for the specified ON
conditions, NULLS are returned in any output columns for that table.
"Left" and "right" mean to the left and right of the = sign after ON.
(Operators other than = also apply, so it also means to the left and
right of =>, >=, and <>)
*If you leave off the instructions, the database will join every row
in the first table with every row in the second table. The number of
output rows equals the number of rows in the first table multiplied by
the number of rows in the second table. This is called a Cartesian
Product, and is not used except for very specific queries.
Two example tables:
Items:
ItemID -- Primary Key
ItemName
ItemID, ItemName
1, Popcorn
2, Chips
3, Soda
4, Fillet Mignon
Prices:
PriceID -- Primary Key
ItemID
ItemPrice
StartDate
EndDate
PriceID, ItemID, ItemPrice, StartDate, EndDate
1, 1, 1.00, 06/01/2007, 06/08/2007
2, 2, 3.50, 06/01/2007, 06/08/2007
3, 3, 1.00, 06/01/2007, 06/08/2007
INNER JOIN Example:
SELECT I1.ItemName
,P1.ItemPrice
FROM Items AS I1
INNER JOIN
Prices AS P1
ON I1.ItemID = P1.ItemID
WHERE P1.StartDate >= #06/09/2007#
Returns
ItemName, Price
Popcorn, 1.00
Chips, 3.50
Soda, 1.00
Notice that Fillet Mignon does not appear. There is no = match (after
the ON clause) for ItemID between the two tables.
LEFT JOIN Example:
SELECT I1.ItemName
,P1.ItemPrice
FROM Items AS I1
LEFT JOIN
Prices AS P1
ON I1.ItemID = P1.ItemID
WHERE P1.StartDate >= #06/09/2007#
Returns
ItemName, Price
Popcorn, 1.00
Chips, 3.50
Soda, 1.00
Fillet Mignon, Null
Now Fillet Mignon does appear, even though there is no = match on
ItemID between the two tables. The output column (ItemPrice) from the
"right hand" table (Prices) leaves a Null behind.
DDL SQL (Usable to create the two tables above so you can run these
queries on your own. You will need to manually type in the sample
data noted above.)
CREATE TABLE Items
(ItemID AUTOINCREMENT
,ItemName TEXT(36)
,CONSTRAINT pk_Items
PRIMARY KEY (ItemID)
)
CREATE TABLE Prices
(PriceID AUTOINCREMENT
,ItemID INTEGER NOT NULL
,ItemPrice CURRENCY
,StartDate DATETIME NOT NULL
,EndDate DATETIME
,CONSTRAINT pk_Prices
PRIMARY KEY (PriceID)
,CONSTRAINT fk_Prices_Items
FOREIGN KEY (ItemID)
REFERENCES Items (ItemID)
)
Sincerely,
Chris O.
