Inner join from two databases

[BasicBoy]

Is there a way to inner join two tables from two different databases and to update the two tables from one query ?
I have a situation where I have item (with a Name and ID) in one database, but the ordered items for that item is kept in another database (the names of these items are only in the first database) -- like in

Select DB1.Table1.Item1.Name, DB2.Table2.Item1.ID, DB2.Table2.Item1.Quantity) from DB1.Table1 inner join DB2.Table2 on Table1.Item1.ID=Table2.Item2.ID where ...

Thanks

 

[fneily]

File - Get External Data - Link tables. Browse to database with other table. Select the table. In the first table under Table Objects, you'll see that table with an arrow pointing at it. That means it's linked. Now you can use it in a query. And if your query is updatable, it will update the linked table.

But the question is, why is it designed this way?

 

[BasicBoy]

Hi fneily

Thanks
The problem we have is that our stores are not online/networked with the office and I need to split up the data in three databases and move them around.

 

[fneily]

Oh. Have you looked at Tools - Replication/Syncro.? Just curious.

 

[BasicBoy]

Hi fneily

I am not conversant with database synchronisation and will appreciate some help.
My basic setup is an office where an order system (which I have written in VB6) is run. At the office new inventory items are made and others are changed. Here also items are ordered for delivery to the stores.
The stores is not online and they need to know what was ordered so that they can receive the items. The office also needs to know which items were issued to update the inventory.
The stores also needs to know which inventory items are available so they can ask the office to order it.

I will appreciate a broad solution to this problem.

Thanks

 

[Remou]

From my experience of repliction, I would say you should avoid it. What happens is that users get mixed up between copies that are sent to the replication site, or the master is moved and ceases to be a master.

It seems to me that it would be possible to issue an update disk with an update database.

Firstly, the new inventory is added to the update database, which is sent to stores where it is linked to the stores database, and new items added and missing items marked deleted. This can be done with TransferDatabase and queries.

Stores then follow their order processing routines and exports (Transferdatabase) the orders (where the order date > update database check date/time). Stores also exports the current stock.

The office imports the order table and stock table for processing. The imported order table becomes orders for processing and the difference between the imported stock and the previous stock becomes sales and so forth.

It is important that a lot of dates are recorded and that all the databases are frequently compacted.

 

[BasicBoy]

fneily

Can you please elaborate somewhat on the structure of the query - take the table names I mentioned on my first post

Thanks

 

[fneily]

Open the database with the table that shows the items quantity. Do the File - Get External Date, etc and link the other table with the name. Under the Table Objects listing, you'll see that table's name. It is now like that table is actually in the database. Just create a regular query in the QBE (query design view). The SQL will look something like:
SELECT Table1.ItemID, Table1.Name, Table2.Quantity
FROM Table1 INNER JOIN Table2 ON Table1.ItemID = Table2.ItemID;